pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedPoor performance with queries using clause: sth IN (...)
2+ messages / 2 participants
[nested] [flat]
* Poor performance with queries using clause: sth IN (...)
@ 2007-05-09 13:22 Andrzej Zawadzki <zawadaa@wp.pl>
0 siblings, 1 reply; 2+ messages in thread
From: Andrzej Zawadzki @ 2007-05-09 13:22 UTC (permalink / raw)
To: pgsql-performance
That's email from my friend.
Any hint?
-------- Original Message --------
Subject: bug
Date: Wed, 09 May 2007 15:03:00 +0200
From: Michal Postupalski
To: Andrzej Zawadzki
We've just changed our database from 8.1 to 8.2 and we are
grief-stricken about very poor performance with queries using clause:
"sth IN (...)". As we can see any query is translate to "sth = ANY
('{....}'::bpchar[]))" and it tooks much more time beacuse it doesn't
use index'es. Why ? How can we speed up these queries? I've just read
"Performance of IN (...) vs. = ANY array[...]" on pgsql-performance
mailing list and I didn't find any solutions. Can anybody tell me what
can I do with postgres to force him using indexes? If there isn't any
solution I'm afraid that we will have to do downgrade to previous
version 8.1.
example:
SELECT count(*)
FROM kredytob b, kredyty k
WHERE true
AND b.kredytid = k.id
AND '' IN ('', upper(b.nazwisko))
AND '' IN ('', upper(b.imie))
AND '78111104485' IN ('', b.pesel)
AND '' IN ('', upper(trim(b.dowseria))) AND '' IN ('', b.dowosnr) AND 0
IN (0, b.typkred) AND k.datazwrot IS NULL;
regards...
Michał Postupalski
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: Poor performance with queries using clause: sth IN (...)
@ 2007-05-09 14:29 Gregory Stark <stark@enterprisedb.com>
parent: Andrzej Zawadzki <zawadaa@wp.pl>
0 siblings, 0 replies; 2+ messages in thread
From: Gregory Stark @ 2007-05-09 14:29 UTC (permalink / raw)
To: Andrzej Zawadzki <zawadaa@wp.pl>; +Cc: pgsql-performance
> AND '' IN ('', upper(b.nazwisko))
> AND '' IN ('', upper(b.imie))
> AND '78111104485' IN ('', b.pesel)
> AND '' IN ('', upper(trim(b.dowseria)))
> AND '' IN ('', b.dowosnr)
> AND 0 IN (0, b.typkred)
> AND k.datazwrot IS NULL;
Hum, interesting. Most of the work Postgres does with IN clauses is on the
assumption that the column you're trying to restrict is on the left hand side
of the IN clause.
1) I think you'll be much better off expanding these into OR clauses.
2) I assume the left hand sides of the IN clauses are actually parameters? I
would recommend using bound parameters mostly for security but also for
performance reasons in that case.
3) having upper() and trim() around the columns makes it basically impossible
for the planner to use indexes even if it was capable of expanding the IN
clauses into OR expressions. Your options are either
a) use an expression index, for example
CREATE INDEX idx_nazwisko on kredytob (upper(nazwisko))
b) use a case-insensitive locale (which you may already be doing) in which
case the upper() is simply unnecessary.
c) use the citext data type (or a case insensitive indexable operator but we
don't seem to have a case insensitive equals, only LIKE and regexp
matches? That seems strange.)
4) You should consider using text or varchar instead of char(). char() has no
performance advantages in Postgres and is annoying to work with.
Something like this with expression indexes on upper(nazwisko), upper(imie),
upper(trim(downseria)) would actually be optimized using indexes:
AND (? = '' OR upper(b.nazwisko) = ?)
AND (? = '' OR upper(b.imie) = ?)
AND (? = '' OR b.pesel = ?)
AND (? = '' OR upper(trim(b.downseria)) = ?)
AND (? = '' OR b.dowosnr = ?)
AND (? = 0 OR b.typkred = ?)
AND k.datazwrot IS NULL
If this is the only query or a particularly important query you could consider
making all those indexes partial with "WHERE datazwrot IS NULL" as well.
--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2007-05-09 14:29 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2007-05-09 13:22 Poor performance with queries using clause: sth IN (...) Andrzej Zawadzki <zawadaa@wp.pl>
2007-05-09 14:29 ` Gregory Stark <stark@enterprisedb.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