find empty holes in attribute (number) ?
От | Patrick |
---|---|
Тема | find empty holes in attribute (number) ? |
Дата | |
Msg-id | 20000803120120.D31554@nohope.patoche.org обсуждение исходный текст |
Список | 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.
В списке pgsql-novice по дате отправления: