Index does not working.
I created a table
CREATE TABLE my_table
(
id int4 NOT NULL,
name varchar(64) NOT NULL,
deleted bool NOT NULL,
active bool NOT NULL,
CONSTRAINT my_table_pkey PRIMARY KEY (id)
)
and index
CREATE INDEX my_table_index
ON my_table
USING btree
(deleted, active);
there is two identical queries...
1) select * from my_table where active and deleted
and
2) select * from my_table where active = true and deleted = true
First query does not use index in contrast to two query.
Why?
Thanks.
--
Verba volent, scripta manent
My ISP - http://www.netbynet.ru
Dan Black <fireworker@gmail.com> writes:
First query does not use index in contrast to two query.
Why?
You don't give enough information. Post EXPLAIN ANALYZE output for
both queries and perhaps someone can help.
-Doug
1)
select * from my_table where active and deleted
--------------
Seq Scan on my_table (cost=100000000.00..100000180.00 rows=2500 width=56)
Filter: (active AND deleted)
2)
select * from my_table where active = true and deleted = true
-------------
Index Scan using my_table_index on my_table (cost=0.00..369.00 rows=2500
width=56)
Index Cond: ((deleted = true) AND (active = true))
2005/6/13, Douglas McNaught <doug@mcnaught.org>:
Dan Black <fireworker@gmail.com> writes:
First query does not use index in contrast to two query.
Why?
You don't give enough information. Post EXPLAIN ANALYZE output for
both queries and perhaps someone can help.-Doug
--
Verba volent, scripta manent
My ISP - http://www.netbynet.ru
Dan Black <fireworker@gmail.com> writes:
1)
select * from my_table where active and deleted
--------------
Seq Scan on my_table (cost=100000000.00..100000180.00 rows=2500 width=56)
Filter: (active AND deleted)2)
select * from my_table where active = true and deleted = true
-------------
Index Scan using my_table_index on my_table (cost=0.00..369.00 rows=2500
width=56)
Index Cond: ((deleted = true) AND (active = true))
Looks like you haven't ever ANALYZED this table.
-Doug
Dan Black <fireworker@gmail.com> writes:
1)
select * from my_table where active and deleted
--------------
Seq Scan on my_table (cost=100000000.00..100000180.00 rows=2500 width=56)
Filter: (active AND deleted)
2)
select * from my_table where active = true and deleted = true
-------------
Index Scan using my_table_index on my_table (cost=0.00..369.00 rows=2500
width=56)
Index Cond: ((deleted = true) AND (active = true))
This is expected: Postgres' internal index API is based around
operators, therefore anything that doesn't look something like
"indexcol operator value" isn't a candidate for an index restriction.
There is a hack in CVS tip that special-cases indexes on boolean
columns so that the above case works, but it's not in any released
version.
regards, tom lane