DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=s1024; d=yahoo.co.uk;
	h=X-YMail-OSG:Received:X-Mailer:Date:From:Reply-To:Subject:To:MIME-Version:Content-Type:Content-Transfer-Encoding:Message-ID;
	b=JrACfRJKuRSpeklFkHkGpz+8tQMkjU66XgYutk+jQcUR2NSgoj7+0t/Djrz5bQJGblfv7vAawSGlKCDidPH9L3CtJ75WTCrMmWxixrLM9PmxAXuebgMG8uqrdCutJ+fVEFH5BFryLWVb+49cmoOkpiLLRHjNv3QnmuvErPeFfkQ=;
Date: Fri, 5 Dec 2008 14:23:30 +0000 (GMT)
From: Glyn Astill <glynastill@yahoo.co.uk>
Reply-To: glynastill@yahoo.co.uk
Subject: Planner picking topsey turvey plan?
To: pgsql-general@postgresql.org
MIME-Version: 1.0
Content-Type: text/plain; charset=utf-8
Content-Transfer-Encoding: quoted-printable
Message-ID: <167634.59714.qm@web23608.mail.ird.yahoo.com>

Hi people,

Does anyone know how I can change what I'm doing to get pgsql to pick a bet=
ter 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 ea=
se the mess of pasting in the plans..


1) I've created a view "orders" that joins two tables "credit" and "mult_or=
d" together as below:

CREATE VIEW orders AS
  SELECT b.mult_ref, a.show, MIN(a.transno) AS "lead_transno", COUNT(a.tran=
sno) AS "parts", SUM(a.tickets) AS "items", SUM(a.value) AS "value"
  FROM (credit a LEFT OUTER JOIN mult_ord b ON a.transno =3D b.transno)=20
  GROUP BY b.mult_ref, a.show;


2) And an explain on that view comes out as below, it's using the correct i=
ndex for the field show on "credit" which doesn't look too bad to me:

DB=3D# explain select a.artist, a.date, b.mult_ref, b.items, b.parts from (=
show a inner join orders b on a.code =3D b.show)
where b.show =3D 357600;
                                               QUERY PLAN
---------------------------------------------------------------------------=
-----------------------------
 Nested Loop  (cost=3D15050.79..15099.68 rows=3D1013 width=3D70)
   ->  Index Scan using show_index01 on show a  (cost=3D0.00..8.37 rows=3D1=
 width=3D26)
         Index Cond: (code =3D 357600::numeric)
   ->  HashAggregate  (cost=3D15050.79..15071.05 rows=3D1013 width=3D39)
         ->  Nested Loop Left Join  (cost=3D0.00..15035.60 rows=3D1013 widt=
h=3D39)
               ->  Index Scan using credit_index04 on credit a  (cost=3D0.0=
0..4027.30 rows=3D1013 width=3D31)
                     Index Cond: (show =3D 357600::numeric)
               ->  Index Scan using mult_ord_index02 on mult_ord b  (cost=
=3D0.00..10.85 rows=3D1 width=3D17)
                     Index Cond: (a.transno =3D b.transno)
(9 rows)


3) Then I have a table called "show" that is indexed on the artist field, a=
nd 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=3D# explain select * from show where artist =3D 'ALKALINE TRIO';
                                 QUERY PLAN
---------------------------------------------------------------------------=
--
 Bitmap Heap Scan on show  (cost=3D9.59..582.41 rows=3D153 width=3D348)
   Recheck Cond: ((artist)::text =3D 'ALKALINE TRIO'::text)
   ->  Bitmap Index Scan on show_index07  (cost=3D0.00..9.56 rows=3D153 wid=
th=3D0)
         Index Cond: ((artist)::text =3D '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=3D# explain select a.artist, a.date, b.mult_ref, b.items, b.parts from (=
show a inner join orders b on a.code =3D b.show)
where artist =3D 'ALKALINE TRIO';
                                             QUERY PLAN
---------------------------------------------------------------------------=
-------------------------
 Hash Join  (cost=3D1576872.96..1786175.37 rows=3D1689 width=3D70)
   Hash Cond: (a.show =3D a.code)
   ->  GroupAggregate  (cost=3D1576288.64..1729424.39 rows=3D4083620 width=
=3D39)
         ->  Sort  (cost=3D1576288.64..1586497.69 rows=3D4083620 width=3D39=
)
               Sort Key: b.mult_ref, a.show
               ->  Hash Left Join  (cost=3D321406.05..792886.22 rows=3D4083=
620 width=3D39)
                     Hash Cond: (a.transno =3D b.transno)
                     ->  Seq Scan on credit a  (cost=3D0.00..267337.20 rows=
=3D4083620 width=3D31)
                     ->  Hash  (cost=3D160588.80..160588.80 rows=3D8759380 =
width=3D17)
                           ->  Seq Scan on mult_ord b  (cost=3D0.00..160588=
.80 rows=3D8759380 width=3D17)
   ->  Hash  (cost=3D582.41..582.41 rows=3D153 width=3D26)
         ->  Bitmap Heap Scan on show a  (cost=3D9.59..582.41 rows=3D153 wi=
dth=3D26)
               Recheck Cond: ((artist)::text =3D 'ALKALINE TRIO'::text)
               ->  Bitmap Index Scan on show_index07  (cost=3D0.00..9.56 ro=
ws=3D153 width=3D0)
                     Index Cond: ((artist)::text =3D 'ALKALINE TRIO'::text)
(15 rows)

Any idea if I can get around this?

=0A=0A=0A