Re: [GENERAL] Planner picking topsey turvey plan?

Explain analyze below,

DB=# explain analyze 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=1583955.94..1794350.36 rows=1552 width=70) (actual time=231496.678..243243.711 rows=892 loops=1)
Hash Cond: (a.show = a.code)
-> GroupAggregate (cost=1583418.91..1737354.68 rows=4104954 width=40) (actual time=223204.620..241813.067 rows=2856379 loops=1)
-> Sort (cost=1583418.91..1593681.29 rows=4104954 width=40) (actual time=223204.567..231296.896 rows=4104964 loops=1)
Sort Key: b.mult_ref, a.show
Sort Method: external merge Disk: 224328kB
-> Hash Left Join (cost=321999.38..795776.58 rows=4104954 width=40) (actual time=14850.320..165804.778 rows=4104964 loops=1)
Hash Cond: (a.transno = b.transno)
-> Seq Scan on credit a (cost=0.00..268740.54 rows=4104954 width=31) (actual time=7.563..76901.901 rows=4104954 loops=1)
-> Hash (cost=160885.28..160885.28 rows=8775528 width=18) (actual time=14831.810..14831.810 rows=8775528 loops=1)
-> Seq Scan on mult_ord b (cost=0.00..160885.28 rows=8775528 width=18) (actual time=4.716..4952.254 rows=8775528 loops=1)
-> Hash (cost=535.28..535.28 rows=140 width=26) (actual time=228.599..228.599 rows=54 loops=1)
-> Bitmap Heap Scan on show a (cost=9.49..535.28 rows=140 width=26) (actual time=77.723..228.488 rows=54 loops=1)
Recheck Cond: ((artist)::text = 'ALKALINE TRIO'::text)
-> Bitmap Index Scan on show_index07 (cost=0.00..9.46 rows=140 width=0) (actual time=62.228..62.228 rows=54 loops=1)
Index Cond: ((artist)::text = 'ALKALINE TRIO'::text)
Total runtime: 243367.640 ms

--- On Sat, 6/12/08, Scott Marlowe <scott(dot)marlowe(at)gmail(dot)com> wrote:

> From: Scott Marlowe <scott(dot)marlowe(at)gmail(dot)com>
> Subject: Re: [ADMIN] [GENERAL] Planner picking topsey turvey plan?
> To: glynastill(at)yahoo(dot)co(dot)uk
> Cc: pgsql-general(at)postgresql(dot)org, pgsql-admin(at)postgresql(dot)org
> Date: Saturday, 6 December, 2008, 8:35 PM
> 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
>
> --
> 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