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 |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers pgsql-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
From | Date | Subject | |
---|---|---|---|
Next Message | Jim C. Nasby | 2006-02-28 23:06:18 | Re: Dead Space Map |
Previous Message | Alvaro Herrera | 2006-02-28 22:47:25 | Re: Automatic free space map filling |
From | Date | Subject | |
---|---|---|---|
Next Message | PFC | 2006-02-28 23:31:33 | Re: wal sync method |
Previous Message | Kevin Grittner | 2006-02-28 21:15:31 | Re: [PERFORM] temporary indexes |