Re: [GENERAL] Planner picking topsey turvey plan?

what does explain analyze yourqueryhere say?

On Sat, Dec 6, 2008 at 1:33 PM, Glyn Astill <glynastill(at)yahoo(dot)co(dot)uk> wrote:
> Anyone?
>
>
> --- On Fri, 5/12/08, Glyn Astill <glynastill(at)yahoo(dot)co(dot)uk> wrote:
>
>> From: Glyn Astill <glynastill(at)yahoo(dot)co(dot)uk>
>> Subject: [GENERAL] Planner picking topsey turvey plan?
>> To: pgsql-general(at)postgresql(dot)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(at)postgresql(dot)org)
>> To make changes to your subscription:
>> http://www.postgresql.org/mailpref/pgsql-general
>
>
>
>
> --
> Sent via pgsql-admin mailing list (pgsql-admin(at)postgresql(dot)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