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

Slow IN query

From: "Eric Jain" <Eric(dot)Jain(at)isb-sib(dot)ch>
To: "pgsql-general" <pgsql-general(at)postgresql(dot)org>
Subject: Slow IN query
Date: 2004-03-31 10:47:40
Message-ID: 008f01c4170d$96e3c620$c300000a@caliente (view raw or flat)
Thread:
Lists: pgsql-general
Using an OR or IN query seems to be orders of magnitudes slower than
running a query twice. There is an unique index on 'id' and an index on
'model_ns, model'. The number of row returned is less than 800.
Everything is vacuumed and analyzed. Running on 7.4.1. Perhaps this
situation is something the optimizer could be cleverer about; if not,
never mind.

explain select * from statements
where model_ns='4' and model in ('P42655', 'Q9XFM4')
order by id;

 Index Scan using statements_pkey on statements  (cost=0.00..2166475.49
rows=107 width=113)
   Filter: ((model_ns = 4::smallint) AND ((model = 'P42655'::text) OR
(model = 'Q9XFM4'::text)))


explain select * from statements
where model_ns='4' and model = 'P42655'
union
select * from statements
where model_ns='4' and model = 'Q9XFM4'
order by id;

 Sort  (cost=425.84..426.11 rows=108 width=113)
   Sort Key: id
   ->  Unique  (cost=418.14..422.19 rows=108 width=113)
         ->  Sort  (cost=418.14..418.41 rows=108 width=113)
               Sort Key: id, model_ns, model, "statement", subject_ns,
subject, predicate_ns, predicate, object_ns, object, object_string,
object_number, object_boolean, generated
               ->  Append  (cost=0.00..414.49 rows=108 width=113)
                     ->  Subquery Scan "*SELECT* 1"  (cost=0.00..207.25
rows=54 width=113)
                           ->  Index Scan using statements_uniprot_idx
on statements  (cost=0.00..206.71 rows=54 width=113)
                                 Index Cond: ((model_ns = 4::smallint)
AND (model = 'P42655'::text))
                     ->  Subquery Scan "*SELECT* 2"  (cost=0.00..207.25
rows=54 width=113)
                           ->  Index Scan using statements_uniprot_idx
on statements  (cost=0.00..206.71 rows=54 width=113)
                                 Index Cond: ((model_ns = 4::smallint)
AND (model = 'Q9XFM4'::text))


Responses

pgsql-general by date

Next:From: Oleg BartunovDate: 2004-03-31 11:18:58
Subject: Re: Wich hardware suits best for large full-text indexed
Previous:From: Durai Date: 2004-03-31 10:44:26
Subject: Warings in Log: could not resolve "localhost": host nor service provided, or not known

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