Skip site navigation (1) Skip section navigation (2)

Re: [GENERAL] Planner picking topsey turvey plan?

From: Glyn Astill <glynastill(at)yahoo(dot)co(dot)uk>
To: Scott Marlowe <scott(dot)marlowe(at)gmail(dot)com>
Cc: pgsql-general(at)postgresql(dot)org, pgsql-admin(at)postgresql(dot)org
Subject: Re: [GENERAL] Planner picking topsey turvey plan?
Date: 2008-12-06 22:26:02
Message-ID: 800555.58693.qm@web23601.mail.ird.yahoo.com (view raw or flat)
Thread:
Lists: pgsql-adminpgsql-general
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


      

In response to

pgsql-admin by date

Next:From: Gerd KoenigDate: 2008-12-07 20:23:23
Subject: upgrade via rpm
Previous:From: Scott MarloweDate: 2008-12-06 20:35:50
Subject: Re: [GENERAL] Planner picking topsey turvey plan?

pgsql-general by date

Next:From: Scott RibeDate: 2008-12-07 17:00:38
Subject: RAM is good!
Previous:From: Scott MarloweDate: 2008-12-06 20:35:50
Subject: Re: [GENERAL] Planner picking topsey turvey plan?

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group