Odpověď: Re: backend don't use index when querying by indexed column
Dear Tom,
first of all thank You for Your response. But here is the
reality. Table formula have about 450000 rows at this time.
The id_formula column is int4 defined as not null primary key. And the index
have clause unique, but backend still don't use it. I have another column in
this table named id_loan. It's int4 too, but it's not unique, but there are 18
to 50 rows with the same value in this column. I have tried to create index
using btree or hash on this column, the index was created, but backend don't
use it. Even i add id_loan = id_loan to SELECT, then backend use the index. I
can't understand to that. If you will need other informations, please ask me.
Thank You very much
Zdenek Habala
Od : Tom Lane <tgl@sss.pgh.pa.us>
Datum : 2001/08/07 �t dop. 01:55:56 GMT+02:00
Komu : zhabala@telecom.cz, pgsql-bugs@postgresql.org
P�edm�t : Re: [BUGS] backend don't use index when querying by indexed columnpgsql-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
Show quoted text
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
<zhabala@telecom.cz> writes:
first of all thank You for Your response. But here is the
reality. Table formula have about 450000 rows at this time.
The id_formula column is int4 defined as not null primary key.
It is eh? Have you done a VACUUM ANALYZE on this table lately?
4500 rows (1%) would be the default match estimate in the absence of
any statistics.
And the index have clause unique, but backend still don't use it.
7.2 will know enough to realize that presence of a unique index means
the column is unique, but existing releases only look at the VACUUM
ANALYZE statistics. No statistics = bad plan.
regards, tom lane