Skip site navigation (1) Skip section navigation (2)

=?iso-8859-2?B?T2Rwb3bs7zog?=Re: backend don't use index when querying by indexed column

From: <zhabala(at)telecom(dot)cz>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>, pgsql-bugs(at)postgresql(dot)org
Subject: =?iso-8859-2?B?T2Rwb3bs7zog?=Re: backend don't use index when querying by indexed column
Date: 2001-08-07 07:09:08
Message-ID: 20010807070416.LNNX26833.smtpmail1@[10.2.3.20] (view raw or whole thread)
Thread:
Lists: pgsql-bugs
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(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 
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
> 


Responses

pgsql-bugs by date

Next:From: Win BauschDate: 2001-08-07 09:47:38
Subject: ecpg 2.8.0: at <connection name> problem
Previous:From: Tom LaneDate: 2001-08-06 23:55:56
Subject: Re: backend don't use index when querying by indexed column

Privacy Policy | About PostgreSQL
Copyright © 1996-2017 The PostgreSQL Global Development Group