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