Re: [PERFORM] index of only not null, use function index?

Merlin Moncure <mmoncure@gmail.com> writes:
> On Mon, May 22, 2017 at 10:17 AM, Ariel <aspostgresql@dsgml.com> wrote:
>> Should I do:
>>
>> CREATE INDEX ON table ((col IS NOT NULL)) WHERE col IS NOT NULL
>>
>> or:
>>
>> CREATE INDEX ON table (col) WHERE col IS NOT NULL
>>
>> I'm thinking the first index will make a smaller, simpler, index since I
>> don't actually need to index the value of the column. But are there any
>> drawbacks I may not be aware of? Or perhaps there are no actual benefits?

> You are correct.  I don't see any downside to converting to bool; this
> will be more efficient especially if 'col' is large at the small cost
> of some generality.

Depends on the datatype really.  Because of alignment considerations,
the index tuples will be the same size for any column value <= 4 bytes,
or <= 8 bytes on 64-bit hardware.  So if this is an integer column,
or even bigint on 64-bit, you won't save any space with the first
index definition.  If it's a text column with an average width larger
than what I just mentioned, you could save some space that way.

In general, indexes on expressions are a tad more expensive to maintain
than indexes on plain column values.  And the second index at least has
the potential to be useful for other queries than the one you're thinking
about.  So personally I'd go with the second definition unless you can
show that there's a really meaningful space savings with the first one.

> Having said that, what I typically do in such
> cases (this comes a lot in database driven work queues) something like
> this:
> CREATE INDEX ON table (OrderCol) WHERE col IS NOT NULL;

Right, you can frequently get a lot of mileage out of indexing something
that's unrelated to the predicate condition, but is also needed by the
query you want to optimize.

                        regards, tom lane

--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance