Skip site navigation (1) Skip section navigation (2)

Re: Slow IN query

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: "Eric Jain" <Eric(dot)Jain(at)isb-sib(dot)ch>
Cc: "pgsql-general" <pgsql-general(at)postgresql(dot)org>
Subject: Re: Slow IN query
Date: 2004-03-31 15:17:25
Message-ID: 4788.1080746245@sss.pgh.pa.us (view raw or flat)
Thread:
Lists: pgsql-general
"Eric Jain" <Eric(dot)Jain(at)isb-sib(dot)ch> writes:
> explain select * from statements
> where model_ns='4' and model in ('P42655', 'Q9XFM4')
> order by id;

This works reasonably well in CVS tip, but in 7.4 and earlier the
planner will not figure out that a multi-column index can be used unless
the OR condition is on the *first* column of the index.  Try flipping
the order of your index columns.

Stupid example in 7.4.2:

regression=# create table statements (model_ns smallint, model text,
regression(# unique(model_ns,model));
NOTICE:  CREATE TABLE / UNIQUE will create implicit index "statements_model_ns_key" for table "statements"
CREATE TABLE
regression=# explain select * from statements where model_ns='4' and model in ('P42655', 'Q9XFM4');
                                         QUERY PLAN
--------------------------------------------------------------------------------------------
 Index Scan using statements_model_ns_key on statements  (cost=0.00..17.09 rows=1 width=34)
   Index Cond: (model_ns = 4::smallint)
   Filter: ((model = 'P42655'::text) OR (model = 'Q9XFM4'::text))
(3 rows)

regression=# drop table statements;
DROP TABLE
regression=# create table statements (model_ns smallint, model text,
regression(# unique(model,model_ns));
NOTICE:  CREATE TABLE / UNIQUE will create implicit index "statements_model_key" for table "statements"
CREATE TABLE
regression=# explain select * from statements where model_ns='4' and model in ('P42655', 'Q9XFM4');
                                                             QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------
 Index Scan using statements_model_key, statements_model_key on statements  (cost=0.00..9.66 rows=1 width=34)
   Index Cond: (((model = 'P42655'::text) AND (model_ns = 4::smallint)) OR ((model = 'Q9XFM4'::text) AND (model_ns = 4::smallint)))
   Filter: ((model_ns = 4::smallint) AND ((model = 'P42655'::text) OR (model = 'Q9XFM4'::text)))
(3 rows)

Development tip, however, is able to produce the latter plan in both
cases.

In your test case, I would imagine that the condition on model_ns alone
is being judged too unselective to make an index scan worthwhile.

			regards, tom lane

In response to

pgsql-general by date

Next:From: Tom LaneDate: 2004-03-31 15:21:48
Subject: Re: Warings in Log: could not resolve "localhost": host nor service provided, or not known
Previous:From: Tom LaneDate: 2004-03-31 15:05:32
Subject: Re: Question about rtrees (overleft replacing left in nodes)

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group