Anyone? --- On Fri, 5/12/08, Glyn Astill <glynastill@xxxxxxxxxxx> wrote: > From: Glyn Astill <glynastill@xxxxxxxxxxx> > Subject: Planner picking topsey turvey plan? > To: pgsql-general@xxxxxxxxxxxxxx > 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@xxxxxxxxxxxxxx) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
