Re: tricky query

From: "Merlin Moncure" <merlin(dot)moncure(at)rcsonline(dot)com>
To: "John A Meinel" <john(at)arbash-meinel(dot)com>
Cc: "Postgresql Performance" <pgsql-performance(at)postgresql(dot)org>, "elein" <elein(at)varlena(dot)com>
Subject: Re: tricky query
Date: 2005-06-28 16:02:09
Message-ID: 6EE64EF3AB31D5448D0007DD34EEB3415C2BFF@Herge.rcsinc.local
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance

John Meinel wrote:
> See my follow up post, which enables an index scan. On my system with
> 90k rows, it takes no apparent time.
> (0.000ms)
> John
> =:->

Confirmed. Hats off to you, the above some really wicked querying.
IIRC I posted the same question several months ago with no response and
had given up on it. I think your solution (smallest X1 not in X) is a
good candidate for general bits, so I'm passing this to varlena for
review :)

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;

Merlin

Responses

Browse pgsql-performance by date

  From Date Subject
Next Message John A Meinel 2005-06-28 16:25:56 Re: tricky query
Previous Message John A Meinel 2005-06-28 15:43:25 Re: tricky query