pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedISNULL performance tweaks
3+ messages / 2 participants
[nested] [flat]
* ISNULL performance tweaks
@ 2003-03-06 18:51 Matt Mello <alien@spaceship.com>
2003-03-06 19:04 ` Re: ISNULL performance tweaks Josh Berkus <josh@agliodbs.com>
2003-03-06 19:12 ` Re: ISNULL performance tweaks Josh Berkus <josh@agliodbs.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 18:51 ISNULL performance tweaks Matt Mello <alien@spaceship.com>
@ 2003-03-06 19:04 ` Josh Berkus <josh@agliodbs.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 18:51 ISNULL performance tweaks Matt Mello <alien@spaceship.com>
@ 2003-03-06 19:12 ` Josh Berkus <josh@agliodbs.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