Re: [PERFORM] tricky query

[John A Meinel]

Merlin Moncure wrote:

Not so bad. Try something like this:

SELECT min(id+1) as id_new FROM table
   WHERE (id+1) NOT IN (SELECT id FROM table);

Now, this requires probably a sequential scan, but I'm not sure how

you

can get around that.
Maybe if you got trickier and did some ordering and limits. The above
seems to give the right answer, though.

it does, but it is still faster than generate_series(), which requires
both a seqscan and a materialization of the function.

I don't know how big you want to scale to.

big. :)

merlin

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

signature.asc
Description: OpenPGP digital signature