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