Thread: ISNULL performance tweaks

I have some existing code running in a production environment with
embedded SELECTs whose WHERE's use ISNULL tests on indexed foreign key
fields.  This is obviously very slow.

My ISNULL *queries *take anywhere from 6 to 40 seconds.  These queries
are used to generate reports to a device which times out at 20 seconds,
so half the time these devices don't get their reports, which makes my
customers VERY angry.

I recall seeing an email (I believe on this list) about how to improve
performance of ISNULL's with some sort of tweak or trick.  However, I
can't find that email anywhere, and couldn't find it searching the
maillist archives.

So, until I have the time to code the fixes I need to prevent the use of
ISNULL, does anybody know how I can speed up this existing system?

Man, I wish PG indexed nulls!  Is there any plan on adding these in the
future?

Thanks for any help you can give!

--
Matt Mello

Matt,

> I recall seeing an email (I believe on this list) about how to improve
> performance of ISNULL's with some sort of tweak or trick.  However, I
> can't find that email anywhere, and couldn't find it searching the
> maillist archives.

Easy.  Create a partial index on NULLs:

CREATE INDEX idx_tablename_nulls ON tablename(columname)
WHERE columname IS NULL;

--
-Josh Berkus
 Aglio Database Solutions
 San Francisco

Matt,

> Man, I wish PG indexed nulls!  Is there any plan on adding these in the
> future?

BTW, this is a design argument.  As far as a lot of SQL-geeks are concerned
(and I'm one of them) use of NULLs should be minimized or eliminiated
entirely from well-normalized database designs.   In such designs, IS NULL
queries are used only for outer joins (where indexes don't matter) or for
data integrity maintainence (where query speed doesn't matter).

As a result, the existing core team doesn't see this issue as a priority.
What fixing it requires is a new programmer who cares enough about it to hack
it.  What would be really nice is the ability to create an index WITH NULLS,
as follows:

CREATE INDEX idx_tablename_one ON tablename(column_one) WITH NULLS;


--
-Josh Berkus
 Aglio Database Solutions
 San Francisco