pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Strange Postgresql Indexing Behavior
2+ messages / 2 participants
[nested] [flat]

* Strange Postgresql Indexing Behavior
@ 2002-03-13 21:28 Brian Knox <laotse@aol.net>
  2002-03-14 02:16 ` Re: Strange Postgresql Indexing Behavior Martijn van Oosterhout <kleptog@svana.org>
  0 siblings, 1 reply; 2+ messages in thread

From: Brian Knox @ 2002-03-13 21:28 UTC (permalink / raw)
  To: pgsql-general@postgresql.org


I'm having an interesting time trying to figure out some behavior with
postgresql indexes that I am trying to understand.

I have a table, historyticket. In that table, I have a column,
fk_opener_id, which is an integer column. I have an index (default btree
index) on the fk_opener_id column in that table.

When I select from this table with the following query:

select * from historyticket where fk_opener_id = ?

The query sometimes uses the index, and sometimes does a sequential scan.
I experimented for a little bit and found out that if the number of rows
that match the query is greater than a certain number (somewhere around
1000 rows from what I can tell) then the index is not used.

For example:

=============
testing=# explain select * from historyticket where fk_opener_id = 67;
NOTICE:  QUERY PLAN:

Seq Scan on historyticket  (cost=0.00..768.62 rows=1246 width=419)

testing=# select count(*) from historyticket where fk_opener_id = 67;
 count
-------
  1158
(1 row)
---------
testing=# explain select * from historyticket where fk_opener_id = 4;
NOTICE:  QUERY PLAN:

Index Scan using fk_opener_id on historyticket  (cost=0.00..179.47 rows=47
width=419)

testing=# select count(*) from historyticket where fk_opener_id = 4;
 count
-------
    79
(1 row)
===============

I did more queries and confirmed that when the number of rows returned is
below a certain number (I don't have enough data to determine the exact
number) the index is used, and when it is above a certain number, it is
not used.

Can anyone explain to me what is happening / why it is happening / how to
make the indexes work correctly?

Thanks.

Brian







^ permalink  raw  reply  [nested|flat] 2+ messages in thread

* Re: Strange Postgresql Indexing Behavior
  2002-03-13 21:28 Strange Postgresql Indexing Behavior Brian Knox <laotse@aol.net>
@ 2002-03-14 02:16 ` Martijn van Oosterhout <kleptog@svana.org>
  0 siblings, 0 replies; 2+ messages in thread

From: Martijn van Oosterhout @ 2002-03-14 02:16 UTC (permalink / raw)
  To: Brian Knox <laotse@aol.net>; +Cc: pgsql-general@postgresql.org

On Wed, Mar 13, 2002 at 04:28:00PM -0500, Brian Knox wrote:
> 
> I'm having an interesting time trying to figure out some behavior with
> postgresql indexes that I am trying to understand.

[snip]

> I did more queries and confirmed that when the number of rows returned is
> below a certain number (I don't have enough data to determine the exact
> number) the index is used, and when it is above a certain number, it is
> not used.
> 
> Can anyone explain to me what is happening / why it is happening / how to
> make the indexes work correctly?

Well, checking a tuple from an index is more expensive than checking a tuple
from a sequential scan. So, if you want to select 50% of the table, it's
faster to read the whole table than it is to use the index.

The planner tries to guess where the break-even point is. Above, seq scan,
below index scan.

This is a FAQ, IIRC.

HTH,
-- 
Martijn van Oosterhout <kleptog@svana.org>
http://svana.org/kleptog/
> 11/9/2001 - a new beginning or the beginning of the end?



^ permalink  raw  reply  [nested|flat] 2+ messages in thread


end of thread, other threads:[~2002-03-14 02:16 UTC | newest]

Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2002-03-13 21:28 Strange Postgresql Indexing Behavior Brian Knox <laotse@aol.net>
2002-03-14 02:16 ` Martijn van Oosterhout <kleptog@svana.org>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox