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
> Od : Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
> Datum : 2001/08/07 Út dop. 01:55:56 GMT+02:00
> Komu : zhabala(at)telecom(dot)cz, pgsql-bugs(at)postgresql(dot)org
> Předmět : Re: [BUGS] backend don't use index when querying by indexed column
> pgsql-bugs(at)postgresql(dot)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
> > NOTICE: QUERY PLAN:
> > Index Scan using predpis_id_formula_key on formula (cost=0.00..11952.57
> > 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
> regards, tom lane
pgsql-bugs by date
|Next:||From: Win Bausch||Date: 2001-08-07 09:47:38|
|Subject: ecpg 2.8.0: at <connection name> problem|
|Previous:||From: Tom Lane||Date: 2001-08-06 23:55:56|
|Subject: Re: backend don't use index when querying by indexed column |