Re: why my query is not using index??

Am Montag, 11. Oktober 2004 22:49 schrieb Francisco Reyes:
> On Mon, 11 Oct 2004, Janning Vygen wrote:
> > postgres uses a seq scan if its faster. In your case postgres seems to
> > know that most of your rows have a date < 2004-01-01 and so doesn't need
> > to consult the index if it has to read every page anyway. seq scan can be
> > faster on small tables. try (in psql) "SET enable_seqscan TO off;"
> > before running your query and see how postgres plans it without using seq
> > scan.
>
> I was about to post and saw this message.
> I have a query that was using sequential scans. Upon turning seqscan to
> off it changed to using the index. What does that mean?

enable_seqscan off means that postgres is not allowed to use seqscan.
default is on and postgres decides for each table lookup which method is
faster: seq scan or index scan. thats what the planner does: deciding which
access method might be the fastest.

> The tables are under 5k records so I wonder if that is why the optimizer
> is option, on it's default state, to do sequential scans.

if you have small tables, postgres is using seqscan to reduce disk lookups.
postgresql reads disk pages in 8k blocks. if your whole table is under 8k
there is no reason for postgres to load an index from another disk page
because it has to load the whole disk anyway.

not sure, but i think postgres also analyzes the table to see which values are
in there. if you have a huge table with a column of integers and postgres
knows that 99% are of value 1 and you are looking for a row with a value of
1, why should it use an index just to see that it has to load the whole table
to find a matching row.

And that's why you can't make performance tests with small tables. you need
test data which is as close as possible to real data.

> I was also wondering if there is a relation between the sequential scans
> and the fact that my entire query is a series of left joins:

no.

janning