pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  

help / color / mirror / Atom feed

From: Lukas Smith <smith@pooteeweet.org>
To: Kevin Grittner <Kevin.Grittner@wicourts.gov>
Subject: Re: [PERFORM] temporary indexes
Date: Wed, 01 Mar 2006 00:02:55 +0100
Message-ID: <4404D69F.7000407@pooteeweet.org> (raw)
In-Reply-To: <44046913.EE98.0025.0@wicourts.gov>
References: <44041B68.EE98.0025.0@wicourts.gov>
	<20060228164515.GS82012@pervasive.com>
	<20060228164515.GS82012@pervasive.com>
	<16076.1141146348@sss.pgh.pa.us>
	<44046913.EE98.0025.0@wicourts.gov>

Kevin Grittner wrote:

> I rewrote the query to use IN predicates rather than EXISTS predicates,
> and the cost estimates look like this:
> 
> EXISTS, no index:  1.6 billion
> EXISTS, with index:  0.023 billion
> IN, no index:  13.7 billion
> IN, with index:  10.6 billion
> 
> At least for the two EXISTS cases, the estimates were roughly accurate.
>  These plans were run against the data after the fix, but analyze has
> not been run since then, so the estimates should be comparable with the
> earlier post.
> 
> I'm not used to using the IN construct this way, so maybe someone can
> spot something horribly stupid in how I tried to use it.

I will have a look at your queries tomorrow. Some general advice (rdbms 
agnostic) on when to use IN and when to use EXISTS taken from "SQL 
performance tuning":

- if the inner table has few rows and the outer has many then IN is 
preferred
- if however you have a restrictive expression on the outer query you 
should preferr EXISTS
- use NOT EXISTS instead of NOT IN (break out early)

regards,
Lukas

view thread (13+ messages)  latest in thread

Message-ID: <4404D69F.7000407@pooteeweet.org>
Permalink:  ../4404D69F.7000407@pooteeweet.org/
Also on:    postgresql.org/message-id/4404D69F.7000407@pooteeweet.org

copy link · copy postgr.es

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: smith@pooteeweet.org, Kevin.Grittner@wicourts.gov
  Subject: Re: [PERFORM] temporary indexes
  In-Reply-To: <4404D69F.7000407@pooteeweet.org>

* 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