DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=beta;
	h=received:message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:content-transfer-encoding:content-disposition:references;
	b=QfQ0s5F8kENohBeztC0uDwpLIhjR1S/aNcM9a2LbGLFPCtr1ETcdZt/Tjqo+xni2cnjkVF0w+ef18Qv4KqnLb2tVco371aJc0AAAMhlA0Spouq2l1EHtYa1OyZl9WDQXUU+nrHQo3MbdcQSTyPpPU507PxWqJQ4LCqBnHqA+L1I=
Message-ID: <4544e0330710011223q7676fd34mca7e82393510b2ec@mail.gmail.com>
Date: Mon, 1 Oct 2007 14:23:02 -0500
From: "Peter Koczan" <pjkoczan@gmail.com>
To: "Tom Lane" <tgl@sss.pgh.pa.us>
Subject: Re: sequence query performance issues
Cc: "Richard Huxton" <dev@archonet.com>,
	pgsql-performance <pgsql-performance@postgresql.org>
In-Reply-To: <4544e0330709281033s5a8c72ccj979f1c02a43188a0@mail.gmail.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
Content-Disposition: inline
References: <4544e0330709271504g364fd042u161966fa448934f6@mail.gmail.com>
	<46FCB5F3.4080600@archonet.com> <886.1190988979@sss.pgh.pa.us>
	<4544e0330709281033s5a8c72ccj979f1c02a43188a0@mail.gmail.com>

> *light bulb* Ahhhhhhh, that's it. So, I guess the solution is either
> to cast the column or wait for 8.3 (which isn't a problem since the
> port won't be done until 8.3 is released anyway).

Just a quick bit of follow-up:

This query works and is equivalent to what I was trying to do (minus
the randomization and limiting):
=> select a.uid from generate_series(1000, 32000) as a(uid) where
a.uid::smallint not in (select uid from people where uid is not null);

It turns out that this and using coalesce are a wash in terms of
performance, usually coming within 10 ms of each other no matter what
limit and ordering constraints you put on the queries.

Peter

=> explain analyze select a.uid from generate_series(1000, 32767) as
a(uid) where a.uid not in (select coalesce(uid, 0) from people);
                                                         QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------
 Function Scan on generate_series a  (cost=718.41..733.41 rows=500
width=4) (actual time=68.742..186.340 rows=26808 loops=1)
   Filter: (NOT (hashed subplan))
   SubPlan
     ->  Seq Scan on people  (cost=0.00..702.68 rows=6294 width=2)
(actual time=0.025..28.368 rows=6294 loops=1)
 Total runtime: 286.311 ms
(5 rows)

=> explain analyze select a.uid from generate_series(1000, 32767) as
a(uid) where a.uid::smallint not in (select uid from people where uid
is not null);
                                                         QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------
 Function Scan on generate_series a  (cost=699.34..716.84 rows=500
width=4) (actual time=58.508..177.683 rows=26808 loops=1)
   Filter: (NOT (hashed subplan))
   SubPlan
     ->  Seq Scan on people  (cost=0.00..686.94 rows=4958 width=2)
(actual time=0.017..23.123 rows=4971 loops=1)
           Filter: (uid IS NOT NULL)
 Total runtime: 277.699 ms
(6 rows)