pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedStrange behaviour
2+ messages / 2 participants
[nested] [flat]
* Strange behaviour
@ 2006-07-27 21:37 Richard Rowell <richard@bowmansystems.com>
2006-08-03 16:39 ` Re: Strange behaviour Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 2+ messages in thread
From: Richard Rowell @ 2006-07-27 21:37 UTC (permalink / raw)
To: pgsql-performance
We are using a BI tool that generates some rather ugly queries. One of
the ugly queries is taking much longer to return thin I think it
should.
The select expression when run alone returns in 2 seconds with 35k rows
(http://www.bowmansystems.com/~richard/explain_select.analyze)
The "where clause" when run alone returns 5200 rows in 10 seconds
(http://www.bowmansystems.com/~richard/explain_where.analyze)
However when I put to two together it takes much, much longer to run.
(http://www.bowmansystems.com/~richard/full.analyze)
Can anyone shed any light on what is going on here? Why does the
optimizer choose such a slow plan in the combined query when the only
real difference between the full query and the "where only" query is the
number of rows in the result set on the "outside" of the "IN" clause?
A few pertinent observations/facts below
1. The query is generated by a BI tool, I know it is ugly and stupid in
many cases. However, please try to see the larger issue, that if the
select and where portions are run separately they are both fast but
together it is insanely slow.
2. The database has vacuumdb -f -z run on it nightly.
3. Modifications to the stock postgresql.conf:
shared_buffers = 15000
work_mem = 131072
default_statistics_target = 100
4. Dual Dual core Opterons, 4 gigs of ram, 6 disk Serial ATA hardware
RAID 10 running Postgres 8.03 compiled from source running on Debian
stable.
5. The tables being queried are only 200 megs or so combined on disk,
the whole DB is ~ 4 gigs
SELECT sum(relpages*8/1024) AS size_M FROM pg_class;
size_m
--------
4178
Thanks!
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: Strange behaviour
2006-07-27 21:37 Strange behaviour Richard Rowell <richard@bowmansystems.com>
@ 2006-08-03 16:39 ` Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 2+ messages in thread
From: Tom Lane @ 2006-08-03 16:39 UTC (permalink / raw)
To: Richard Rowell <richard@bowmansystems.com>; +Cc: pgsql-performance
Richard Rowell <richard@bowmansystems.com> writes:
> We are using a BI tool that generates some rather ugly queries. One of
> the ugly queries is taking much longer to return thin I think it
> should.
> (http://www.bowmansystems.com/~richard/full.analyze)
> Can anyone shed any light on what is going on here?
Seems like you have some bad rowcount estimates leading to poor plan
selection. Most of the problem looks to be coming from the FunctionScan
nodes, wherein the planner doesn't have any real way to estimate how
many rows come out. You might look into whether you can replace those
functions with views, so that the planner isn't dealing with "black boxes".
regards, tom lane
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2006-08-03 16:39 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2006-07-27 21:37 Strange behaviour Richard Rowell <richard@bowmansystems.com>
2006-08-03 16:39 ` Tom Lane <tgl@sss.pgh.pa.us>
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