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

Re: [PERFORM] temporary indexes

From: "Kevin Grittner" <Kevin(dot)Grittner(at)wicourts(dot)gov>
To: "Jim C(dot) Nasby" <jnasby(at)pervasive(dot)com>,"Tom Lane" <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: <pgsql-hackers(at)postgresql(dot)org>,<pgsql-performance(at)postgresql(dot)org>
Subject: Re: [PERFORM] temporary indexes
Date: 2006-02-28 21:15:31
Message-ID: 44046913.EE98.0025.0@wicourts.gov (view raw or flat)
Thread:
Lists: pgsql-hackerspgsql-performance
>>> On Tue, Feb 28, 2006 at 11:05 am, in message
<16076(dot)1141146348(at)sss(dot)pgh(dot)pa(dot)us>,
Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote: 
> The limiting factor is that EXISTS subplans
> aren't flattened ... and once that's fixed, I doubt the example
would
> need any new kind of join support.

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.

-Kevin


Attachment: datafix-in.qry
Description: application/octet-stream (2.0 KB)
Attachment: datafix-in-plan2.txt
Description: application/octet-stream (5.3 KB)
Attachment: datafix-in-plan1.txt
Description: application/octet-stream (4.2 KB)

In response to

Responses

pgsql-performance by date

Next:From: Lukas SmithDate: 2006-02-28 23:02:55
Subject: Re: [PERFORM] temporary indexes
Previous:From: Jim C. NasbyDate: 2006-02-28 21:09:25
Subject: Re: fsync and battery-backed caches

pgsql-hackers by date

Next:From: Tom LaneDate: 2006-02-28 21:36:44
Subject: bug in PG_VERSION_NUM patch
Previous:From: Jim C. NasbyDate: 2006-02-28 21:02:32
Subject: Re: [PERFORM] temporary indexes

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