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

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
      subscribe-nomail command to [EMAIL PROTECTED] so that your
      message can get through to the mailing list cleanly

Reply via email to