pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedexplain analyze results are different for each iteration
3+ messages / 3 participants
[nested] [flat]
* explain analyze results are different for each iteration
@ 2004-09-15 01:21 Robert Davis <robert@faithwestinc.com>
2004-09-15 02:07 ` Re: explain analyze results are different for each iteration Stephan Szabo <sszabo@megazone.bigpanda.com>
0 siblings, 1 reply; 3+ messages in thread
From: Robert Davis @ 2004-09-15 01:21 UTC (permalink / raw)
To: pgsql-sql
I'm trying to benchmark some complex sql queries. One query, in
particular, is causing problems -- its cost values can vary from 228
to 907, its Total Runtimes from 60 ms to 5176 ms. The query plans
show that the optimizer is choosing different plans for different
iterations of the same query. Does anyone have any idea what's
going on here or maybe how I can force Postgres to prefer the faster
plan?
The query plans are very long (11 tables joined) but I can include
them if it might help. Thanks for any advice,
Roberto
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: explain analyze results are different for each iteration
2004-09-15 01:21 explain analyze results are different for each iteration Robert Davis <robert@faithwestinc.com>
@ 2004-09-15 02:07 ` Stephan Szabo <sszabo@megazone.bigpanda.com>
0 siblings, 0 replies; 3+ messages in thread
From: Stephan Szabo @ 2004-09-15 02:07 UTC (permalink / raw)
To: Robert Davis <robert@faithwestinc.com>; +Cc: pgsql-sql
On Tue, 14 Sep 2004, Robert Davis wrote:
> I'm trying to benchmark some complex sql queries. One query, in
> particular, is causing problems -- its cost values can vary from 228
> to 907, its Total Runtimes from 60 ms to 5176 ms. The query plans
> show that the optimizer is choosing different plans for different
> iterations of the same query. Does anyone have any idea what's
> going on here or maybe how I can force Postgres to prefer the faster
> plan?
>
> The query plans are very long (11 tables joined) but I can include
> them if it might help. Thanks for any advice,
11 tables. You might want to see if raising geqo_threshold stabilizes the
plans since IIRC that's the threshold for using the genetic optimizer.
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: explain analyze results are different for each iteration
@ 2004-09-15 01:33 Gregory S. Williamson <gsw@globexplorer.com>
0 siblings, 0 replies; 3+ messages in thread
From: Gregory S. Williamson @ 2004-09-15 01:33 UTC (permalink / raw)
To: Robert Davis <robert@faithwestinc.com>; pgsql-sql
Are you forcing stuff out of cache (both postgres and OS) ? I've found that this can make a huge difference with some queries ... not sure that this would make it have different plans, though.
My ill-informied $0.02 worth ... fwiw
Greg Williamson
DBA
GlobeXplorer LLC
-----Original Message-----
From: Robert Davis [mailto:robert@faithwestinc.com]
Sent: Tue 9/14/2004 6:21 PM
To: pgsql-sql@postgresql.org
Cc:
Subject: [SQL] explain analyze results are different for each iteration
I'm trying to benchmark some complex sql queries. One query, in
particular, is causing problems -- its cost values can vary from 228
to 907, its Total Runtimes from 60 ms to 5176 ms. The query plans
show that the optimizer is choosing different plans for different
iterations of the same query. Does anyone have any idea what's
going on here or maybe how I can force Postgres to prefer the faster
plan?
The query plans are very long (11 tables joined) but I can include
them if it might help. Thanks for any advice,
Roberto
---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faqs/FAQ.html
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2004-09-15 02:07 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2004-09-15 01:21 explain analyze results are different for each iteration Robert Davis <robert@faithwestinc.com>
2004-09-15 02:07 ` Stephan Szabo <sszabo@megazone.bigpanda.com>
2004-09-15 01:33 Re: explain analyze results are different for each iteration Gregory S. Williamson <gsw@globexplorer.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