Received: from localhost (unknown [200.46.204.183]) by mail.postgresql.org (Postfix) with ESMTP id B4E856500E7 for ; Sat, 6 Dec 2008 18:26:09 -0400 (AST) Received: from mail.postgresql.org ([200.46.204.86]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 36759-06 for ; Sat, 6 Dec 2008 18:26:05 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from web23601.mail.ird.yahoo.com (web23601.mail.ird.yahoo.com [87.248.115.44]) by mail.postgresql.org (Postfix) with SMTP id 0369164FD81 for ; Sat, 6 Dec 2008 18:26:04 -0400 (AST) Received: (qmail 59824 invoked by uid 60001); 6 Dec 2008 22:26:02 -0000 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:Cc:In-Reply-To:MIME-Version:Content-Type:Content-Transfer-Encoding:Message-ID; b=0i7FQ3rrniIErf2DR7bFVAE39+pIFNkLxbNDwEFfNXWAoza/4Aodrm/jd6sYjOkm2AV+fDFMbUgcEQ1S+i4E/lYxGVHm7S3/ID11LveE92Rl7Gwsee/8UjZIyys5W4sDJ2lZNpLAG1lh5fs9jF5LRssmUNOrXF4kOpPEWWCgq9M=; X-YMail-OSG: uROvYU4VM1nf4wlYMQeaGNf5nN5dBMNMYFtaFsF0MK5WSGw9pGlsa6DIN9v9RF5hWMEMwg_k2RAgq3pmz9w29.968K9k1vIkM0zwvJd0zGSlBMgGJooyIo0mwaducM_0ynmPFsy.5X5Axx6A4ZrKQfXoeUrqyq7x0.IrtUx.E5pgLw-- Received: from [86.3.222.102] by web23601.mail.ird.yahoo.com via HTTP; Sat, 06 Dec 2008 22:26:02 GMT X-Mailer: YahooMailWebService/0.7.260.1 Date: Sat, 6 Dec 2008 22:26:02 +0000 (GMT) From: Glyn Astill Reply-To: glynastill@yahoo.co.uk Subject: Re: [GENERAL] Planner picking topsey turvey plan? To: Scott Marlowe Cc: pgsql-general@postgresql.org, pgsql-admin@postgresql.org In-Reply-To: MIME-Version: 1.0 Content-Type: text/plain; charset=utf-8 Content-Transfer-Encoding: quoted-printable Message-ID: <800555.58693.qm@web23601.mail.ird.yahoo.com> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0 tagged_above=0 required=5 tests=none X-Spam-Level: X-Archive-Number: 200812/38 X-Sequence-Number: 30871 Explain analyze below,=20 DB=3D# explain analyze select a.artist, a.date, b.mult_ref, b.items, b.part= s from (show a inner join orders b on a.code =3D b.show) where artist =3D '= ALKALINE TRIO'; = QUERY PLAN ---------------------------------------------------------------------------= ---------------------------------------------------------------------------= - Hash Join (cost=3D1583955.94..1794350.36 rows=3D1552 width=3D70) (actual = time=3D231496.678..243243.711 rows=3D892 loops=3D1) Hash Cond: (a.show =3D a.code) -> GroupAggregate (cost=3D1583418.91..1737354.68 rows=3D4104954 width= =3D40) (actual time=3D223204.620..241813.067 rows=3D2856379 loops=3D1) -> Sort (cost=3D1583418.91..1593681.29 rows=3D4104954 width=3D40= ) (actual time=3D223204.567..231296.896 rows=3D4104964 loops=3D1) Sort Key: b.mult_ref, a.show Sort Method: external merge Disk: 224328kB -> Hash Left Join (cost=3D321999.38..795776.58 rows=3D4104= 954 width=3D40) (actual time=3D14850.320..165804.778 rows=3D4104964 loops= =3D1) Hash Cond: (a.transno =3D b.transno) -> Seq Scan on credit a (cost=3D0.00..268740.54 rows= =3D4104954 width=3D31) (actual time=3D7.563..76901.901 rows=3D4104954 loops= =3D1) -> Hash (cost=3D160885.28..160885.28 rows=3D8775528 = width=3D18) (actual time=3D14831.810..14831.810 rows=3D8775528 loops=3D1) -> Seq Scan on mult_ord b (cost=3D0.00..160885= .28 rows=3D8775528 width=3D18) (actual time=3D4.716..4952.254 rows=3D877552= 8 loops=3D1) -> Hash (cost=3D535.28..535.28 rows=3D140 width=3D26) (actual time=3D2= 28.599..228.599 rows=3D54 loops=3D1) -> Bitmap Heap Scan on show a (cost=3D9.49..535.28 rows=3D140 wi= dth=3D26) (actual time=3D77.723..228.488 rows=3D54 loops=3D1) Recheck Cond: ((artist)::text =3D 'ALKALINE TRIO'::text) -> Bitmap Index Scan on show_index07 (cost=3D0.00..9.46 ro= ws=3D140 width=3D0) (actual time=3D62.228..62.228 rows=3D54 loops=3D1) Index Cond: ((artist)::text =3D 'ALKALINE TRIO'::text) Total runtime: 243367.640 ms --- On Sat, 6/12/08, Scott Marlowe wrote: > From: Scott Marlowe > 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? >=20 > On Sat, Dec 6, 2008 at 1:33 PM, Glyn Astill > wrote: > > Anyone? > > > > > > --- On Fri, 5/12/08, Glyn Astill > wrote: > > > >> From: Glyn Astill > >> 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 =3D > >> 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=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; > >> =20 > 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=20 > (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) > >> > >> > >> > >> 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=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 width=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=20 > (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=3D4083620 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 width=3D26) > >> Recheck Cond: ((artist)::text =3D > >> 'ALKALINE TRIO'::text) > >> -> Bitmap Index Scan on > show_index07 > >> (cost=3D0.00..9.56 rows=3D153 width=3D0) > >> Index Cond: ((artist)::text =3D > >> '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 > > >=20 >=20 >=20 > --=20 > When fascism comes to America, it will be draped in a flag > and > carrying a cross - Sinclair Lewis >=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