Postgres: Create index on that text is null or not

Question

Suppose we have a table:

CREATE TABLE public."Comments"
(
    id integer NOT NULL DEFAULT nextval('"Comments_id_seq"'::regclass),
    comment text COLLATE pg_catalog."default",
    CONSTRAINT "Comments_pkey" PRIMARY KEY (id)
)

We need to fast select rows where comment IS NULL or where comment IS NOT NULL, but we do not need full text search.

Is it possible somehow create an index for that, but not creating full-text index?

create index on "Comments" (id) where comment is not null? — user330315
– user330315, Commented Sep 20, 2017 at 8:13
Unrelated, but: you should really avoid the need for quoted identifiers. They are much more trouble in the long run than they are worth — user330315
– user330315, Commented Sep 20, 2017 at 8:13
@a_horse_with_no_name I know it, it's legacy code moved from MySQL. Anyway, it is unrelated to the question. — vladon
– vladon, Commented Sep 20, 2017 at 8:17
@BennjoeMordeno: that is not true for Postgres. You can easily index values that are much larger then 900 bytes. — user330315
– user330315, Commented Sep 20, 2017 at 8:20

Laurenz Albe · Accepted Answer · 2017-09-20 09:27:52Z

2

An index will only help you if the WHERE condition is selective, that is, it filters out a significant percentage of the rows. Indeed, PostgreSQL will prefer the more efficient sequential scan if the condition is not selective.

So you cannot have an index that helps with both cases, but you can have an index that may help with the less frequent of these cases.

If comments are usually NULL, you could use:

CREATE INDEX ON "Comments" (id) WHERE comment IS NOT NULL;

answered Sep 20, 2017 at 9:27

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Chris Travers · Accepted Answer · 2017-09-20 10:08:50Z

in addition to Laurenz's answer, if you have not so many cases where comment is null, you could just use a btree index since they can be used to look this up:

create index comments_comment_idx ON "Comments" (comment);

This gives you an ability to search on equality of comments as long as they are short enough not to be toasted. You can also search for ranges and prefixes. However if they go on for a few kb, you may start to run into problems, so recommend using this approach only if comments are expected to be reasonably short.

As a further note there are other ways of optimising such a query as well. As a further note, if you are doing a lot of ordering by id you may want to do a multi-column index that would allow an index-only scan:

create index comments_comment_id_idx ON "Comments" (id, comment);

That would be useful if you want the last five ids where comment is not null for example.

Collectives™ on Stack Overflow

Postgres: Create index on that text is null or not

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related