Skip site navigation (1) Skip section navigation (2)

Re: [PERFORM] temporary indexes

From: Lukas Smith <smith(at)pooteeweet(dot)org>
To: Kevin Grittner <Kevin(dot)Grittner(at)wicourts(dot)gov>
Subject: Re: [PERFORM] temporary indexes
Date: 2006-02-28 23:02:55
Message-ID: 4404D69F.7000407@pooteeweet.org (view raw or flat)
Thread:
Lists: pgsql-hackerspgsql-performance
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

In response to

pgsql-performance by date

Next:From: PFCDate: 2006-02-28 23:31:33
Subject: Re: wal sync method
Previous:From: Kevin GrittnerDate: 2006-02-28 21:15:31
Subject: Re: [PERFORM] temporary indexes

pgsql-hackers by date

Next:From: Jim C. NasbyDate: 2006-02-28 23:06:18
Subject: Re: Dead Space Map
Previous:From: Alvaro HerreraDate: 2006-02-28 22:47:25
Subject: Re: Automatic free space map filling

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group