From: Tom Lane <tgl@sss.pgh.pa.us>
To: Ryan Holmes <ryan@hyperstep.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: IN operator causes sequential scan (vs. multiple OR expressions)
Date: Sat, 27 Jan 2007 20:56:39 -0500
Message-ID: <26081.1169949399@sss.pgh.pa.us> (raw)
In-Reply-To: <4F003DBB-34E7-4E9D-A8B3-27AA0CBD66C3@hyperstep.com>
References: <AE23623B-65E0-4CC8-8F7E-3920E4B42FDC@hyperstep.com>
<24016.1169941986@sss.pgh.pa.us>
<4F003DBB-34E7-4E9D-A8B3-27AA0CBD66C3@hyperstep.com>
Ryan Holmes <ryan@hyperstep.com> writes:
> So, yes, disabling seqscan does force an index scan for the IN
> version. My question now is, how do I get PostgreSQL to make the
> "right" decision without disabling seqscan?
I pinged you before because in a trivial test case I got
indexscans out of both text and varchar cases:
regression=# create table foo (f1 text unique, f2 varchar(25) unique);
NOTICE: CREATE TABLE / UNIQUE will create implicit index "foo_f1_key" for table "foo"
NOTICE: CREATE TABLE / UNIQUE will create implicit index "foo_f2_key" for table "foo"
CREATE TABLE
regression=# explain select * from foo where f1 in ('foo', 'bar');
QUERY PLAN
-------------------------------------------------------------------------
Bitmap Heap Scan on foo (cost=4.52..9.86 rows=2 width=61)
Recheck Cond: (f1 = ANY ('{foo,bar}'::text[]))
-> Bitmap Index Scan on foo_f1_key (cost=0.00..4.52 rows=2 width=0)
Index Cond: (f1 = ANY ('{foo,bar}'::text[]))
(4 rows)
regression=# explain select * from foo where f2 in ('foo', 'bar');
QUERY PLAN
-------------------------------------------------------------------------------------
Bitmap Heap Scan on foo (cost=6.59..17.27 rows=10 width=61)
Recheck Cond: ((f2)::text = ANY (('{foo,bar}'::character varying[])::text[]))
-> Bitmap Index Scan on foo_f2_key (cost=0.00..6.59 rows=10 width=0)
Index Cond: ((f2)::text = ANY (('{foo,bar}'::character varying[])::text[]))
(4 rows)
But on closer inspection the second case is not doing the right thing:
notice the rowcount estimate is 10, whereas it should be only 2 because
of the unique index on f2. I poked into it and realized that in 8.2
scalararraysel() fails to deal with binary-compatible datatype cases,
instead falling back to a not-very-bright generic estimate.
I've committed a fix for 8.2.2, but in the meantime maybe you could
change your varchar column to text?
regards, tom lane
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: tgl@sss.pgh.pa.us, ryan@hyperstep.com
Subject: Re: IN operator causes sequential scan (vs. multiple OR expressions)
In-Reply-To: <26081.1169949399@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