| 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: | Whole Thread | Raw Message | 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
| 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 |