Message-ID: <41542D7C.6070402@vst.donetsk.ua>
Date: Fri, 24 Sep 2004 14:21:48 +0000
From: "Vladimir S. Tikhonjuk" <vst@vst.donetsk.ua>
User-Agent: Mozilla/5.0 (X11; U; FreeBSD i386; en-US; rv:1.6) Gecko/20040324
MIME-Version: 1.0
To: pgsql-sql@postgresql.org
Subject: INDEX problem
Content-Type: text/plain; charset=KOI8-R; format=flowed
Content-Transfer-Encoding: 7bit

Hi all!

I have such table:

CREATE TABLE object (
  id   SERIAL,
  object_type_id   int8
);

This table has 4 000 000 rows.

There are 2 index:
    CREATE INDEX object_id_idx ON object(id);
    CREATE INDEX object_object_type_id_idx ON object(object_type_id);

So:
# EXPLAIN SELECT * FROM object WHERE id = 1::int8;
 Index Scan using object_id_idx on object  (cost=0.00..92323.66 
rows=23650 width=29)
 Index Cond: (id = 1::bigint)

Here everything is O.K.

# EXPLAIN SELECT * FROM object WHERE object_type_id = 1::int8;
 Index Scan using object_object_type_id_idx on object  
(cost=0.00..92323.66 rows=23650 width=29)
 Index Cond: (object_type_id = 1::bigint)

Here everything is O.K. too... but!

# EXPLAIN SELECT * FROM object WHERE object_type_id IN (1::int8, 21::int8);
 Seq Scan on object  (cost=0.00..105730.00 rows=47182 width=29)
 Filter: ((object_type_id = 1::bigint) OR (object_type_id = 21::bigint))

The same results after:
# EXPLAIN SELECT * FROM object WHERE object_type_id = 1::int8 OR 
object_type_id = 21::int8;

Why Postgres didn't use index here ???


# EXPLAIN ANALYZE SELECT * FROM object WHERE object_type_id IN (1::int8, 
21::int8);
 Seq Scan on object  (cost=0.00..105730.00 rows=47182 width=29) (actual 
time=20744.910..20744.910 rows=0 loops=1)
   Filter: ((object_type_id = 1::bigint) OR (object_type_id = 21::bigint))
 Total runtime: 20745.022 ms

Best regards,
Vladimir S. Tikhonjuk