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: 20050628174205.GT62747@colo.samason.me.uk (view raw or flat)
Thread:
Lists: pgsql-performance
John A Meinel wrote:
>SELECT t1.id+1 as id_new FROM id_test t1
>   WHERE NOT EXISTS
>       (SELECT t2.id FROM id_test t2 WHERE t2.id = t1.id+1)
>   ORDER BY t1.id LIMIT 1;

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!


  Sam

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-2014 The PostgreSQL Global Development Group