X-Original-To: pgsql-performance@postgresql.org Received: from spampd.localdomain (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id 1C7624762CF for ; Tue, 13 May 2003 10:46:15 -0400 (EDT) Received: from sss.pgh.pa.us (unknown [192.204.191.242]) by postgresql.org (Postfix) with ESMTP id 8A2FB474E42 for ; Tue, 13 May 2003 10:46:12 -0400 (EDT) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.12.9/8.12.9) with ESMTP id h4DEkCU6008276; Tue, 13 May 2003 10:46:12 -0400 (EDT) To: jim@nasby.net Cc: csajl , Stephan Szabo , josh@agliodbs.com, pgsql-performance@postgresql.org Subject: Re: [repost] partial index / funxtional idx or bad sql? In-reply-to: <20030513055817.N66185@flake.decibel.org> References: <20030512204533.I20042-100000@megazone23.bigpanda.com> <20030513040338.3925.qmail@web40305.mail.yahoo.com> <20030513055817.N66185@flake.decibel.org> Comments: In-reply-to "Jim C. Nasby" message dated "Tue, 13 May 2003 05:58:17 -0500" Date: Tue, 13 May 2003 10:46:12 -0400 Message-ID: <8275.1052837172@sss.pgh.pa.us> From: Tom Lane X-Spam-Status: No, hits=-32.5 required=5.0 tests=BAYES_01,EMAIL_ATTRIBUTION,IN_REP_TO,QUOTED_EMAIL_TEXT, REFERENCES,REPLY_WITH_QUOTES autolearn=ham version=2.50 X-Spam-Level: X-Spam-Checker-Version: SpamAssassin 2.50 (1.173-2003-02-20-exp) X-Archive-Number: 200305/147 X-Sequence-Number: 1926 "Jim C. Nasby" writes: > Wow, I'll have to keep that in mind. Shouldn't the optimizer be able to > handle that? Could this get added to the TODO? No, 'cause it's done (in CVS tip). I'm actually a bit hesitant now to recommend that people do such things, because the 7.4 optimizer is likely to produce a better plan from the unmodified IN query than it will from any explicitly "improved" version. The 7.4 code knows several ways to do IN efficiently, but when you hand-transform the query you are forcing the choice; perhaps wrongly. An example from CVS tip and the regression database in which hand transformation forces a less efficient plan choice: regression=# explain analyze select * from tenk1 a where unique1 in (select ten from tenk1); QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------------- Merge Join (cost=483.17..484.91 rows=10 width=248) (actual time=407.14..409.16 rows=10 loops=1) Merge Cond: ("outer".unique1 = "inner".ten) -> Index Scan using tenk1_unique1 on tenk1 a (cost=0.00..1571.97 rows=10000 width=244) (actual time=0.41..1.60 rows=11 loops=1) -> Sort (cost=483.17..483.19 rows=10 width=4) (actual time=406.57..406.65 rows=10 loops=1) Sort Key: tenk1.ten -> HashAggregate (cost=483.00..483.00 rows=10 width=4) (actual time=406.08..406.26 rows=10 loops=1) -> Seq Scan on tenk1 (cost=0.00..458.00 rows=10000 width=4) (actual time=0.19..261.84 rows=10000 loops=1) Total runtime: 410.74 msec (8 rows) regression=# explain analyze select * from tenk1 a, (select distinct ten from tenk1) b regression-# where a.unique1 = b.ten; QUERY PLAN --------------------------------------------------------------------------------------------------------------------------------- Nested Loop (cost=1122.39..1232.59 rows=10 width=248) (actual time=476.67..666.02 rows=10 loops=1) -> Subquery Scan b (cost=1122.39..1172.39 rows=10 width=4) (actual time=475.94..662.00 rows=10 loops=1) -> Unique (cost=1122.39..1172.39 rows=10 width=4) (actual time=475.89..661.65 rows=10 loops=1) -> Sort (cost=1122.39..1147.39 rows=10000 width=4) (actual time=475.85..559.27 rows=10000 loops=1) Sort Key: ten -> Seq Scan on tenk1 (cost=0.00..458.00 rows=10000 width=4) (actual time=0.37..274.87 rows=10000 loops=1) -> Index Scan using tenk1_unique1 on tenk1 a (cost=0.00..6.01 rows=1 width=244) (actual time=0.27..0.31 rows=1 loops=10) Index Cond: (a.unique1 = "outer".ten) Total runtime: 687.53 msec (9 rows) So, for now, make the transformation ... but keep a note about the IN version to try whenever you update to 7.4. regards, tom lane