Received: from localhost (unknown [200.46.204.183]) by mail.postgresql.org (Postfix) with ESMTP id 2564465020C for ; Sat, 6 Dec 2008 16:35:56 -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 20521-02 for ; Sat, 6 Dec 2008 16:35:52 -0400 (AST) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6 Received: from rv-out-0506.google.com (rv-out-0506.google.com [209.85.198.237]) by mail.postgresql.org (Postfix) with ESMTP id 728A16501A4 for ; Sat, 6 Dec 2008 16:35:51 -0400 (AST) Received: by rv-out-0506.google.com with SMTP id b25so534919rvf.43 for ; Sat, 06 Dec 2008 12:35:51 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma; h=domainkey-signature:received:received:message-id:date:from:to :subject:cc:in-reply-to:mime-version:content-type :content-transfer-encoding:content-disposition:references; bh=PP3Zt1l4Nm53eeiOajvAx/3rnHnlb8wmp9seTKnKZic=; b=SyuVz2ZlsoCXarWEx4iBtNRk6YJpYOw5wJ5LCBWvAbKnk5P+vnRGTKT8i6+9+3s4SN xJoT4YuB1UqDgyFL7R0+I6z92rb/FX6TnT4iohVPb08bBQMY21/TypvZs9SqdNnrSufy 0XUzRvzPDjcefDnyg10Xr9XSAmD5ot+ZSeRR4= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma; h=message-id:date:from:to:subject:cc:in-reply-to:mime-version :content-type:content-transfer-encoding:content-disposition :references; b=bBGhtt9niMnMxkFfG5iPMi8FnMuMuPFvL7Ts1RaTulYASOY0Y/S10O4B2RpM5Zz0zU eZKQ6YzAak3DqvqWQy9uQ/pwG1L/PTJR21I0b6fWFGGkNBD3u6cGil7iMx7CyJdKoQGz 4Gfg0qJH8YD7w/h03z9tYzjLCJffmiUSkqEWM= Received: by 10.114.25.19 with SMTP id 19mr1145710way.89.1228595750887; Sat, 06 Dec 2008 12:35:50 -0800 (PST) Received: by 10.114.133.4 with HTTP; Sat, 6 Dec 2008 12:35:50 -0800 (PST) Message-ID: Date: Sat, 6 Dec 2008 13:35:50 -0700 From: "Scott Marlowe" To: glynastill@yahoo.co.uk Subject: Re: [GENERAL] Planner picking topsey turvey plan? Cc: pgsql-general@postgresql.org, pgsql-admin@postgresql.org In-Reply-To: <19513.71606.qm@web23602.mail.ird.yahoo.com> MIME-Version: 1.0 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline References: <167634.59714.qm@web23608.mail.ird.yahoo.com> <19513.71606.qm@web23602.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/37 X-Sequence-Number: 30870 what does explain analyze yourqueryhere say? 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 = >> 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@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 > -- When fascism comes to America, it will be draped in a flag and carrying a cross - Sinclair Lewis