Re: [ADMIN] 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=2856379loops=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=4104964loops=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.901rows=4104954 loops=1) 
                     ->  Hash  (cost=160885.28..160885.28 rows=8775528 width=18) (actual time=14831.810..14831.810
rows=8775528loops=1) 
                           ->  Seq Scan on mult_ord b  (cost=0.00..160885.28 rows=8775528 width=18) (actual
time=4.716..4952.254rows=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=54loops=1) 
                     Index Cond: ((artist)::text = 'ALKALINE TRIO'::text)
 Total runtime: 243367.640 ms



--- On Sat, 6/12/08, Scott Marlowe <scott.marlowe@gmail.com> wrote:

> From: Scott Marlowe <scott.marlowe@gmail.com>
> Subject: Re: [ADMIN] [GENERAL] Planner picking topsey turvey plan?
> To: glynastill@yahoo.co.uk
> Cc: pgsql-general@postgresql.org, pgsql-admin@postgresql.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@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
>
> --
> Sent via pgsql-general mailing list
> (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general