Обсуждение: 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))
 





--
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