INDEX problem

From: "Vladimir S(dot) Tikhonjuk" <vst(at)vst(dot)donetsk(dot)ua>
To: pgsql-sql(at)postgresql(dot)org
Subject: INDEX problem
Date: 2004-09-24 14:21:48
Message-ID: 41542D7C.6070402@vst.donetsk.ua
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-sql

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

Browse pgsql-sql by date

  From Date Subject
Next Message Oliver Nolden 2004-09-24 15:39:53 How to insert values in bytea?
Previous Message Tom Lane 2004-09-24 14:20:13 Re: need ``row number``