From: Peter Koczan <pjkoczan@gmail.com>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Richard Huxton <dev@archonet.com>
Cc: pgsql-performance <pgsql-performance@postgresql.org>
Subject: Re: sequence query performance issues
Date: Mon, 1 Oct 2007 14:23:02 -0500
Message-ID: <4544e0330710011223q7676fd34mca7e82393510b2ec@mail.gmail.com> (raw)
In-Reply-To: <4544e0330709281033s5a8c72ccj979f1c02a43188a0@mail.gmail.com>
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)
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-performance@postgresql.org
Cc: pjkoczan@gmail.com, tgl@sss.pgh.pa.us, dev@archonet.com
Subject: Re: sequence query performance issues
In-Reply-To: <4544e0330710011223q7676fd34mca7e82393510b2ec@mail.gmail.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