pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Janning Vygen <vygen@gmx.de>
To: Francisco Reyes <lists@natserv.com>
Cc: HyunSung Jang <siche@siche.net>
Cc: pgsql-performance@postgresql.org
Subject: Re: why my query is not using index??
Date: Mon, 11 Oct 2004 23:26:02 +0200
Message-ID: <200410112326.02486.vygen@gmx.de> (raw)
In-Reply-To: <20041011163756.M97379@zoraida.natserv.net>
References: <41639F3C.4050103@siche.net>
	<200410111425.02671.vygen@gmx.de>
	<20041011163756.M97379@zoraida.natserv.net>

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



view thread (9+ messages)  latest in thread

Message-ID: <200410112326.02486.vygen@gmx.de>
Permalink:  ../200410112326.02486.vygen@gmx.de/
Also on:    postgresql.org/message-id/200410112326.02486.vygen@gmx.de

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-performance@postgresql.org
  Cc: vygen@gmx.de, lists@natserv.com, siche@siche.net
  Subject: Re: why my query is not using index??
  In-Reply-To: <200410112326.02486.vygen@gmx.de>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

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