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.
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 |