Re: Searching for the cause of a bad plan - Mailing list pgsql-performance

From Csaba Nagy
Subject Re: Searching for the cause of a bad plan
Date
Msg-id 1190376744.4661.208.camel@PCD12478
Whole thread Raw
In response to Re: Searching for the cause of a bad plan  (Simon Riggs <simon@2ndquadrant.com>)
Responses Re: Searching for the cause of a bad plan
List pgsql-performance
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.





pgsql-performance by date:

Previous
From: Simon Riggs
Date:
Subject: Re: Searching for the cause of a bad plan
Next
From: smiley2211
Date:
Subject: Re: Upgraded from 7.4 to 8.1.4 QUERIES NOW SLOW!!!