From: Csaba Nagy <nagy@ecircle-ag.com>
To: Simon Riggs <simon@2ndquadrant.com>
Cc: postgres performance list <pgsql-performance@postgresql.org>
Subject: Re: Searching for the cause of a bad plan
Date: Fri, 21 Sep 2007 14:12:24 +0200
Message-ID: <1190376744.4661.208.camel@PCD12478> (raw)
In-Reply-To: <1190374478.4202.20.camel@ebony.site>
References: <1190369024.4661.173.camel@PCD12478>
<1190372365.4202.14.camel@ebony.site>
<1190374166.4661.194.camel@PCD12478>
<1190374478.4202.20.camel@ebony.site>
On Fri, 2007-09-21 at 12:34 +0100, Simon Riggs wrote:
> On Fri, 2007-09-21 at 13:29 +0200, Csaba Nagy wrote:
>
> > > Can you plans with/without LIMIT and with/without cursor, for both b1
> > > and b2?
> >
> > The limit is unfortunately absolutely needed part of the query
>
> Understood, but not why I asked...
>
Well, the same query without limit goes:
dbdop=# explain execute test_001(31855344);
QUERY
PLAN
------------------------------------------------------------------------------------------------------------
Sort (cost=322831.85..322831.94 rows=36 width=1804)
Sort Key: ta.a, ta.b
-> Hash Join (cost=3365.60..322830.92 rows=36 width=1804)
Hash Cond: (ta.b = tb.b)
-> Index Scan using pk_table_a on table_a ta
(cost=0.00..314541.78 rows=389648 width=16)
Index Cond: (a = $1)
-> Hash (cost=524.71..524.71 rows=41671 width=1788)
-> Seq Scan on table_b2 tb (cost=0.00..524.71
rows=41671 width=1788)
I'm not sure what you mean without cursor, maybe not using prepare ?
Well we set up the JDBC driver to always prepare the queries, as this
gives us much better worst case plans than when letting postgres see the
parameter values, especially in queries with limit. So I simulate that
when explaining the behavior we see. All our limit queries are for
interactive display, so the worst case is of much higher importance for
us than the mean execution time... unfortunately postgres has a tendency
to take the best mean performance path than avoid worst case, and it is
not easy to convince it otherwise.
Cheers,
Csaba.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: nagy@ecircle-ag.com, simon@2ndquadrant.com
Subject: Re: Searching for the cause of a bad plan
In-Reply-To: <1190376744.4661.208.camel@PCD12478>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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