From: Vladimir S. Tikhonjuk <vst@vst.donetsk.ua>
To: pgsql-sql@postgresql.org
Subject: INDEX problem
Date: Fri, 24 Sep 2004 14:21:48 +0000
Message-ID: <41542D7C.6070402@vst.donetsk.ua> (raw)
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
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-sql@postgresql.org
Cc: vst@vst.donetsk.ua
Subject: Re: INDEX problem
In-Reply-To: <41542D7C.6070402@vst.donetsk.ua>
* 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