pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
ISNULL performance tweaks
3+ messages / 2 participants
[nested] [flat]

* ISNULL performance tweaks
@ 2003-03-06 18:51  Matt Mello <alien@spaceship.com>
  0 siblings, 2 replies; 3+ messages in thread

From: Matt Mello @ 2003-03-06 18:51 UTC (permalink / raw)
  To: pgsql-performance

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




^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* Re: ISNULL performance tweaks
@ 2003-03-06 19:04  Josh Berkus <josh@agliodbs.com>
  parent: Matt Mello <alien@spaceship.com>
  1 sibling, 0 replies; 3+ messages in thread

From: Josh Berkus @ 2003-03-06 19:04 UTC (permalink / raw)
  To: Matt Mello <alien@spaceship.com>; pgsql-performance


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




^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* Re: ISNULL performance tweaks
@ 2003-03-06 19:12  Josh Berkus <josh@agliodbs.com>
  parent: Matt Mello <alien@spaceship.com>
  1 sibling, 0 replies; 3+ messages in thread

From: Josh Berkus @ 2003-03-06 19:12 UTC (permalink / raw)
  To: Matt Mello <alien@spaceship.com>; pgsql-performance


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




^ permalink  raw  reply  [nested|flat] 3+ messages in thread


end of thread, other threads:[~2003-03-06 19:12 UTC | newest]

Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-03-06 18:51 ISNULL performance tweaks Matt Mello <alien@spaceship.com>
2003-03-06 19:04 ` Josh Berkus <josh@agliodbs.com>
2003-03-06 19:12 ` Josh Berkus <josh@agliodbs.com>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox