From: Glyn Astill <glynastill@yahoo.co.uk>
To: pgsql-general@postgresql.org
Subject: Planner picking topsey turvey plan?
Date: Fri, 5 Dec 2008 14:23:30 +0000 (GMT)
Message-ID: <167634.59714.qm@web23608.mail.ird.yahoo.com> (raw)
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?
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: glynastill@yahoo.co.uk, pgsql-general@postgresql.org
Subject: Re: Planner picking topsey turvey plan?
In-Reply-To: <167634.59714.qm@web23608.mail.ird.yahoo.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox