Re: tricky query

Поиск
Список
Период
Сортировка
От Sam Mason
Тема Re: tricky query
Дата
Msg-id 20050628174205.GT62747@colo.samason.me.uk
обсуждение исходный текст
Ответ на Re: tricky query  (John A Meinel <john@arbash-meinel.com>)
Список pgsql-performance
John A Meinel wrote:
>SELECT t1.id+1 as id_new FROM id_test t1
>   WHERE NOT EXISTS
>       (SELECT t2.id FROM id_test t2 WHERE t2.id = t1.id+1)
>   ORDER BY t1.id LIMIT 1;

This works well on sparse data, as it only requires as many index
access as it takes to find the first gap.   The simpler "NOT IN"
version that everybody seems to have posted the first time round
has a reasonably constant (based on the number of rows, not gap
position) startup time but the actual time spent searching for the
gap is much lower.

I guess the version you use depends on how sparse you expect the
data to be.  If you expect your query to have to search through
more than half the table before finding the gap then you're better
off using the "NOT IN" version, otherwise the "NOT EXISTS" version
is faster -- on my system anyway.

Hope that's interesting!


  Sam

В списке pgsql-performance по дате отправления:

Предыдущее
От: Tom Lane
Дата:
Сообщение: Re: Too slow querying a table of 15 million records
Следующее
От: Jean-Max Reymond
Дата:
Сообщение: Re: perl garbage collector