pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Tom Lane <tgl@sss.pgh.pa.us>
To: Andrew Rawnsley <ronz@ravensfield.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: annoying query/planner choice
Date: Mon, 12 Jan 2004 00:40:13 -0500
Message-ID: <21520.1073886013@sss.pgh.pa.us> (raw)
In-Reply-To: <22837C75-44AC-11D8-8262-000393A47FCC@ravensfield.com>
References: <22837C75-44AC-11D8-8262-000393A47FCC@ravensfield.com>

Andrew Rawnsley <ronz@ravensfield.com> writes:
> I have a situation that is giving me small fits, and would like to see  
> if anyone can shed any light on it.

In general, pulling 10% of a table *should* be faster as a seqscan than
an indexscan, except under the most extreme assumptions about clustering
(is the table clustered on site_id, by any chance?).  What I suspect is
that the table is a bit larger than your available RAM, so that a
seqscan ends up flushing all of the kernel's cache and forcing a lot of
I/O, whereas an indexscan avoids the cache flush by not touching (quite)
all of the table.  The trouble with this is that the index only looks
that good under test conditions, ie, when you repeat it just after an
identical query that pulled all of the needed pages into RAM.  Under
realistic load conditions where different site_ids are being hit, the
indexscan is not going to be as good as you think, because it will incur
substantial I/O.

You should try setting up a realistic test load hitting different random
site_ids, and see whether it's really a win to force seqscan off for
this query or not.

			regards, tom lane



view thread (6+ messages)  latest in thread

Message-ID: <21520.1073886013@sss.pgh.pa.us>
Permalink:  ../21520.1073886013@sss.pgh.pa.us/
Also on:    postgresql.org/message-id/21520.1073886013@sss.pgh.pa.us

 · 

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: tgl@sss.pgh.pa.us, ronz@ravensfield.com
  Subject: Re: annoying query/planner choice
  In-Reply-To: <21520.1073886013@sss.pgh.pa.us>

* 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