Re: [GENERAL] Planner picking topsey turvey plan?

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