Re: 8.x index insert performance

> select * from sometable where somefield IS NULL won't work because IS
is
> not a nomally indexible operator.

Ah, I didn't know that. So there is no real reason not to exclude null
values from all your indexes :). Reading Tom's recent comments
everything is clear now.

Instead of using your two index approach I prefer to:
create function nullidx(anyelement) returns boolean as $$ select $1 is
null; $$ language
sql immutable;

create index on t(nullidx(f)); -- etc

Merlin

"Merlin Moncure" <merlin(dot)moncure(at)rcsonline(dot)com> writes:

> > select * from sometable where somefield IS NULL won't work because IS
> is
> > not a nomally indexible operator.
>
> Ah, I didn't know that. So there is no real reason not to exclude null
> values from all your indexes :). Reading Tom's recent comments
> everything is clear now.

There are other reasons. If you want a query like

SELECT * FROM tab ORDER BY col LIMIT 10

to use an index on col then it can't exclude NULLs or else it wouldn't be
useful. (Oracle actually has this problem, you frequently have to add WHERE
col IS NOT NULL" in order to let it use an index.)

--
greg