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

Re: tricky query

From: Sam Mason <sam(at)samason(dot)me(dot)uk>
To: pgsql-performance(at)postgresql(dot)org
Subject: Re: tricky query
Date: 2005-06-28 17:42:05
Message-ID: (view raw, whole thread or download thread mbox)
Lists: pgsql-performance
John A Meinel wrote:
>SELECT as id_new FROM id_test t1
>       (SELECT FROM id_test t2 WHERE =

This works well on sparse data, as it only requires as many index
access as it takes to find the first gap.   The simpler "NOT IN"
version that everybody seems to have posted the first time round
has a reasonably constant (based on the number of rows, not gap
position) startup time but the actual time spent searching for the
gap is much lower.

I guess the version you use depends on how sparse you expect the
data to be.  If you expect your query to have to search through
more than half the table before finding the gap then you're better
off using the "NOT IN" version, otherwise the "NOT EXISTS" version
is faster -- on my system anyway.

Hope that's interesting!


In response to

pgsql-performance by date

Next:From: Tom LaneDate: 2005-06-28 18:14:42
Subject: Re: Too slow querying a table of 15 million records
Previous:From: Michael StoneDate: 2005-06-28 17:27:01
Subject: Re: read block size

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