CODEWITHSUNDEEP
sqlpostgresql
vladon
vladon

Reputation: 8401

Postgres: Create index on that text is null or not

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?

Upvotes: 1

Views: 2850

Answers (2)

Chris Travers
Chris Travers

Reputation: 26464

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.

Upvotes: 1

Laurenz Albe
Laurenz Albe

Reputation: 247300

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;

Upvotes: 2

Related Questions