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.
