Обсуждение: 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
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))
--
Verba volent, scripta manent
My ISP - http://www.netbynet.ru
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