pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Tom Lane <tgl@sss.pgh.pa.us>
To: jim@nasby.net
Cc: csajl <csajl@yahoo.com>
Cc: Stephan Szabo <sszabo@megazone23.bigpanda.com>
Cc: josh@agliodbs.com
Cc: pgsql-performance@postgresql.org
Subject: Re: [repost] partial index / funxtional idx or bad sql?
Date: Tue, 13 May 2003 10:46:12 -0400
Message-ID: <8275.1052837172@sss.pgh.pa.us> (raw)
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>

"Jim C. Nasby" <jim@nasby.net> 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




view thread (10+ messages)

Message-ID: <8275.1052837172@sss.pgh.pa.us>
Permalink:  ../8275.1052837172@sss.pgh.pa.us/
Also on:    postgresql.org/message-id/8275.1052837172@sss.pgh.pa.us

 · 

reply

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 from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-performance@postgresql.org
  Cc: tgl@sss.pgh.pa.us, jim@nasby.net, csajl@yahoo.com, sszabo@megazone23.bigpanda.com, josh@agliodbs.com
  Subject: Re: [repost] partial index / funxtional idx or bad sql?
  In-Reply-To: <8275.1052837172@sss.pgh.pa.us>
  References: <20030512204533.I20042-100000@megazone23.bigpanda.com> <20030513040338.3925.qmail@web40305.mail.yahoo.com> <20030513055817.N66185@flake.decibel.org> <8275.1052837172@sss.pgh.pa.us>

* 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