pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Tom Lane <tgl@sss.pgh.pa.us>
To: Fabian Kreitner <fabian.kreitner@ainea-ag.de>
Cc: pgsql-performance@postgresql.org
Subject: Re: index / sequential scan problem
Date: Thu, 17 Jul 2003 14:12:55 -0400
Message-ID: <5586.1058465575@sss.pgh.pa.us> (raw)
In-Reply-To: <5.1.0.14.0.20030717131157.03957fa0@195.145.148.245>
References: <5.1.0.14.0.20030717105203.03d2b5c0@195.145.148.245>
	<5.1.0.14.0.20030717105203.03d2b5c0@195.145.148.245>
	<5.1.0.14.0.20030717131157.03957fa0@195.145.148.245>

Fabian Kreitner <fabian.kreitner@ainea-ag.de> writes:
> That is what I read too and is why Im confused that the index is indeed 
> executing faster. Can this be a problem with the hardware and/or postgress 
> installation?

I think the actual issue here is that you are executing the EXISTS
subplan over and over, once for each outer row.  The planner's cost
estimate for EXISTS is based on the assumption that you do it once
... in which scenario the seqscan very possibly is cheaper.  However,
when you do the EXISTS subplan over and over for many outer rows, you
get a savings from the fact that the index and table pages soon get
cached in memory.  The seqscan plan gets a savings too, since the table
is small enough to fit in memory, but once everything is in memory the
indexscan plan is faster.

There's been some discussion on pghackers about how to teach the planner
to account for repeated executions of subplans, but we have not come up
with a good solution yet.

For the moment, what people tend to do if they know their database is
small enough to mostly stay in memory is to reduce random_page_cost to
make the planner favor indexscans.  If you know the database is entirely
cached then the theoretically correct value of random_page_cost is 1.0
(since fetching any page will cost the same, if it's all in RAM).  I'd
recommend against adopting that as a default, but a lot of people find
that setting it to 2.0 or so seems to model their situation better than
the out-of-the-box 4.0.

			regards, tom lane



view thread (19+ messages)  latest in thread

Message-ID: <5586.1058465575@sss.pgh.pa.us>
Permalink:  ../5586.1058465575@sss.pgh.pa.us/
Also on:    postgresql.org/message-id/5586.1058465575@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 from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-performance@postgresql.org
  Cc: tgl@sss.pgh.pa.us, fabian.kreitner@ainea-ag.de
  Subject: Re: index / sequential scan problem
  In-Reply-To: <5586.1058465575@sss.pgh.pa.us>
  References: <5.1.0.14.0.20030717105203.03d2b5c0@195.145.148.245> <5.1.0.14.0.20030717131157.03957fa0@195.145.148.245> <5586.1058465575@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