Обсуждение: backend don't use index when querying by indexed column

Zdenek Habala (zhabala@telecom.cz) reports a bug with a severity of 2
The lower the number the more severe it is.

Short Description
backend don't use index when querying by indexed column

Long Description
I have a table named formula with this structure:

--------------------
 id_formula | integer                  | not null default nextval('"formula_id_formula_seq"'::text)
 id_loan    | integer                  |
 cisfor     | integer                  |
 castpr     | integer                  |
 castuh     | integer                  |
 rt_datfor  | integer                  |
 rt_datspl  | integer                  |
 rt_datvlo  | integer                  |
 kdo        | character(8)             |
 plod       | timestamp with time zone |
 pldo       | timestamp with time zone |
Indices: formula_id_loan,
         predpis_id_formula_key

Index "predpis_id_formula_key"
 Attribute  |  Type
------------+---------
 id_formula | integer
btree

when i try to explain this select i will got this:

explain select * from formula where id_formula = 1;
NOTICE:  QUERY PLAN:
Seq Scan on formula  (cost=0.00..10919.89 rows=4576 width=72)

but when i try to explain modified select like this:
explain select * from formula where id_formula = 1 and id_formula=id_formula;
NOTICE:  QUERY PLAN:
 Index Scan using predpis_id_formula_key on formula  (cost=0.00..11952.57 rows=46
 width=72)

backend uses the index.

Is it a bug or isn't it ? ( I have hundreds of other tables and any one of them have not this problem, only that
formulaone. ) 

Thanx for explanation.

Sample Code


No file was uploaded with this report

pgsql-bugs@postgresql.org writes:
> backend don't use index when querying by indexed column

This is not necessarily a bug.

> when i try to explain this select i will got this:

> explain select * from formula where id_formula = 1;
> NOTICE:  QUERY PLAN:
> Seq Scan on formula  (cost=0.00..10919.89 rows=4576 width=72)

How many rows altogether in this table?  How many actually have
id_formula = 1?   It would appear from the rows=4576 estimate that there's
at least one value in the table that occurs thousands of times.

> but when i try to explain modified select like this:
> explain select * from formula where id_formula = 1 and id_formula=id_formula;
> NOTICE:  QUERY PLAN:
>  Index Scan using predpis_id_formula_key on formula  (cost=0.00..11952.57 rows=46
>  width=72)

> backend uses the index.

I think the extra time per-row to evaluate the extra WHERE clause pushes
up the estimated cost of the seq scan just enough to make it a little
more expensive than the estimated cost of the indexscan (note that
they're pretty close in the two examples).  The seqscan will have to
evaluate two operators for every row, whereas the indexscan only has to
do it at the rows found by the index, so its cost goes up less when you
add more WHERE conditions.

If these estimates have nothing to do with reality in your situation,
then that's a bug.  But you haven't told us anything about what reality
is.  If the planner's estimates are correct then it's doing the right
thing.

            regards, tom lane