find empty holes in attribute (number) ?

From: Patrick <pat(at)patoche(dot)org>
To: pgsql-novice(at)postgresql(dot)org
Subject: find empty holes in attribute (number) ?
Date: 2000-08-03 10:01:20
Message-ID: 20000803120120.D31554@nohope.patoche.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-novice

Hi,

I have a table with a number and then various attributes.
I'd like to find the 'holes' that is the numbers without any rows
related, at least one of them.

For example if i have :

num | other attributes
------+-----------------
1 | ....
2 | ....
4 | ....
6 | ....
7 | ....
8 | ....

etc...

I'd like to have a query returning 3 or a list of all holes (3,5).

I was doing :
select min(num +1) FROM table where (num + 1
not in (select num from table));

And it returns me the first free one (3), which is ok for what I do.
Except that today I noticed it's using nearly 1 minute for a table
with 2500 rows to give me a result, and I have to to something
better.

I do have an index on num but explain tells me :
explain select min(num +1) FROM table where
(num + 1 not in (select num from table));
NOTICE: QUERY PLAN:

Aggregate (cost=282.06 rows=2426 width=4)
-> Seq Scan on table (cost=282.06 rows=2426 width=4)
SubPlan
-> Seq Scan on table (cost=282.06 rows=2426
width=4)

So the two Seq Scan probably explain the slowness...

Can I improve the SQL query ?
Should I instead process this thing in my program in Perl (since I need only
4 seconds to have the results of select num from table, and i can
easily process that in Perl) ?

If that matters : postgresql 6.5.3 on debian gnu/linux potato.

TIA and regards from France.

--
Patrick.
Because if life has a meaning, we should already know it.

Browse pgsql-novice by date

  From Date Subject
Next Message luc00 2000-08-03 13:51:59 libpq++ app code examples ?
Previous Message Andrew McMillan 2000-08-02 22:13:30 Re: Upgrade in the wrong way