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:In-Reply-To:MIME-Version:Content-Type:Content-Transfer-Encoding:Message-ID;
	b=iPp6bh6oFGc9SXgth1w8vWuuTPE5leZgTy0PiW2sh9xIuxRHY7zC7k/62tWN1v5w228+s65BVnOtRDBZOihA7Q2DLHp2ISzj+HaOfQaM/PqxUSqdh+xVGbkOKJ0VBcoC39oZEElFs8aDP1zxCIlWQAtX2YfJReH5bIj4bR6DKu4=;
Date: Sat, 6 Dec 2008 20:33:14 +0000 (GMT)
From: Glyn Astill <glynastill@yahoo.co.uk>
Reply-To: glynastill@yahoo.co.uk
Subject: Re: [GENERAL] Planner picking topsey turvey plan?
To: pgsql-general@postgresql.org, pgsql-admin@postgresql.org
In-Reply-To: <167634.59714.qm@web23608.mail.ird.yahoo.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=utf-8
Content-Transfer-Encoding: quoted-printable
Message-ID: <19513.71606.qm@web23602.mail.ird.yahoo.com>

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,
>=20
> Does anyone know how I can change what I'm doing to get
> pgsql to pick a better plan?
>=20
> 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..
>=20
>=20
> 1) I've created a view "orders" that joins
> two tables "credit" and "mult_ord"
> together as below:
>=20
> 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 =3D
> b.transno)=20
>   GROUP BY b.mult_ref, a.show;
>=20
>=20
>=20
> 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:
>=20
> 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=20
> (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 width=3D39)
>                ->  Index Scan using credit_index04 on
> credit a  (cost=3D0.00..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)
>=20
>=20
>=20
> 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.
>=20
> 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=20
> (cost=3D0.00..9.56 rows=3D153 width=3D0)
>          Index Cond: ((artist)::text =3D 'ALKALINE
> TRIO'::text)
> (4 rows)
>=20
>=20
>=20
> 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.
>=20
> However it seems the planner has other ideas, it just looks
> backwards to me:
>=20
> 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=20
> (cost=3D321406.05..792886.22 rows=3D4083620 width=3D39)
>                      Hash Cond: (a.transno =3D b.transno)
>                      ->  Seq Scan on credit a=20
> (cost=3D0.00..267337.20 rows=3D4083620 width=3D31)
>                      ->  Hash=20
> (cost=3D160588.80..160588.80 rows=3D8759380 width=3D17)
>                            ->  Seq Scan on mult_ord b=20
> (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=20
> (cost=3D9.59..582.41 rows=3D153 width=3D26)
>                Recheck Cond: ((artist)::text =3D
> 'ALKALINE TRIO'::text)
>                ->  Bitmap Index Scan on show_index07=20
> (cost=3D0.00..9.56 rows=3D153 width=3D0)
>                      Index Cond: ((artist)::text =3D
> 'ALKALINE TRIO'::text)
> (15 rows)
>=20
> Any idea if I can get around this?
>=20
>=20
>=20
>=20
>=20
>=20
> --=20
> Sent via pgsql-general mailing list
> (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general=0A=0A=0A