Re: [ADMIN] Planner picking topsey turvey plan?

what does explain analyze yourqueryhere say?

On Sat, Dec 6, 2008 at 1:33 PM, Glyn Astill <glynastill@yahoo.co.uk> wrote:
> Anyone?
>
>
> --- On Fri, 5/12/08, Glyn Astill <glynastill@yahoo.co.uk> wrote:
>
>> From: Glyn Astill <glynastill@yahoo.co.uk>
>> Subject: [GENERAL] Planner picking topsey turvey plan?
>> To: pgsql-general@postgresql.org
>> Date: Friday, 5 December, 2008, 2:23 PM
>> Hi people,
>>
>> Does anyone know how I can change what I'm doing to get
>> pgsql to pick a better plan?
>>
>> I'll explain what I've done below but please
>> forgive me if I interpret the plans wrong as I try to
>> describe, I've split it into 4 points to try and ease
>> the mess of pasting in the plans..
>>
>>
>> 1) I've created a view "orders" that joins
>> two tables "credit" and "mult_ord"
>> together as below:
>>
>> CREATE VIEW orders AS
>>   SELECT b.mult_ref, a.show, MIN(a.transno) AS
>> "lead_transno", COUNT(a.transno) AS
>> "parts", SUM(a.tickets) AS "items",
>> SUM(a.value) AS "value"
>>   FROM (credit a LEFT OUTER JOIN mult_ord b ON a.transno =
>> b.transno)
>>   GROUP BY b.mult_ref, a.show;
>>
>>
>>
>> 2) And an explain on that view comes out as below, it's
>> using the correct index for the field show on
>> "credit" which doesn't look too bad to me:
>>
>> DB=# explain select a.artist, a.date, b.mult_ref, b.items,
>> b.parts from (show a inner join orders b on a.code = b.show)
>> where b.show = 357600;
>>                                                QUERY PLAN
>> --------------------------------------------------------------------------------------------------------
>>  Nested Loop  (cost=15050.79..15099.68 rows=1013 width=70)
>>    ->  Index Scan using show_index01 on show a
>> (cost=0.00..8.37 rows=1 width=26)
>>          Index Cond: (code = 357600::numeric)
>>    ->  HashAggregate  (cost=15050.79..15071.05 rows=1013
>> width=39)
>>          ->  Nested Loop Left Join  (cost=0.00..15035.60
>> rows=1013 width=39)
>>                ->  Index Scan using credit_index04 on
>> credit a  (cost=0.00..4027.30 rows=1013 width=31)
>>                      Index Cond: (show = 357600::numeric)
>>                ->  Index Scan using mult_ord_index02 on
>> mult_ord b  (cost=0.00..10.85 rows=1 width=17)
>>                      Index Cond: (a.transno = b.transno)
>> (9 rows)
>>
>>
>>
>> 3) Then I have a table called "show" that is
>> indexed on the artist field, and a plan for listing the
>> shows for an artist is as below, again this doesn't look
>> too bad to me, as it's using the index on artist.
>>
>> DB=# explain select * from show where artist =
>> 'ALKALINE TRIO';
>>                                  QUERY PLAN
>> -----------------------------------------------------------------------------
>>  Bitmap Heap Scan on show  (cost=9.59..582.41 rows=153
>> width=348)
>>    Recheck Cond: ((artist)::text = 'ALKALINE
>> TRIO'::text)
>>    ->  Bitmap Index Scan on show_index07
>> (cost=0.00..9.56 rows=153 width=0)
>>          Index Cond: ((artist)::text = 'ALKALINE
>> TRIO'::text)
>> (4 rows)
>>
>>
>>
>> 4) So.. I guess I can join "show" ->
>> "orders", expecting an index scan on
>> "show" for the artist, then an index scan on
>> "orders" for each show.
>>
>> However it seems the planner has other ideas, it just looks
>> backwards to me:
>>
>> DB=# explain select a.artist, a.date, b.mult_ref, b.items,
>> b.parts from (show a inner join orders b on a.code = b.show)
>> where artist = 'ALKALINE TRIO';
>>                                              QUERY PLAN
>> ----------------------------------------------------------------------------------------------------
>>  Hash Join  (cost=1576872.96..1786175.37 rows=1689
>> width=70)
>>    Hash Cond: (a.show = a.code)
>>    ->  GroupAggregate  (cost=1576288.64..1729424.39
>> rows=4083620 width=39)
>>          ->  Sort  (cost=1576288.64..1586497.69
>> rows=4083620 width=39)
>>                Sort Key: b.mult_ref, a.show
>>                ->  Hash Left Join
>> (cost=321406.05..792886.22 rows=4083620 width=39)
>>                      Hash Cond: (a.transno = b.transno)
>>                      ->  Seq Scan on credit a
>> (cost=0.00..267337.20 rows=4083620 width=31)
>>                      ->  Hash
>> (cost=160588.80..160588.80 rows=8759380 width=17)
>>                            ->  Seq Scan on mult_ord b
>> (cost=0.00..160588.80 rows=8759380 width=17)
>>    ->  Hash  (cost=582.41..582.41 rows=153 width=26)
>>          ->  Bitmap Heap Scan on show a
>> (cost=9.59..582.41 rows=153 width=26)
>>                Recheck Cond: ((artist)::text =
>> 'ALKALINE TRIO'::text)
>>                ->  Bitmap Index Scan on show_index07
>> (cost=0.00..9.56 rows=153 width=0)
>>                      Index Cond: ((artist)::text =
>> 'ALKALINE TRIO'::text)
>> (15 rows)
>>
>> Any idea if I can get around this?
>>
>>
>>
>>
>>
>>
>> --
>> Sent via pgsql-general mailing list
>> (pgsql-general@postgresql.org)
>> To make changes to your subscription:
>> http://www.postgresql.org/mailpref/pgsql-general
>
>
>
>
> --
> Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-admin
>



--
When fascism comes to America, it will be draped in a flag and
carrying a cross - Sinclair Lewis