Re: limit clause produces wrong query plan

От: Andrus
Тема: Re: limit clause produces wrong query plan
Дата: ,
Msg-id: 372C245439584A6CBF49F996503BF162@andrusnotebook
(см: обсуждение, исходный текст)
Ответ на: Re: limit clause produces wrong query plan  ("Scott Marlowe")
Ответы: Re: limit clause produces wrong query plan  (Chris)
Re: limit clause produces wrong query plan  (PFC)
Список: pgsql-performance

Скрыть дерево обсуждения

limit clause produces wrong query plan  ("Andrus", )
 Re: limit clause produces wrong query plan  ("Andrus", )
  Re: limit clause produces wrong query plan  ("Scott Marlowe", )
   Re: limit clause produces wrong query plan  ("Andrus", )
    Re: limit clause produces wrong query plan  (Chris, )
    Re: limit clause produces wrong query plan  (PFC, )
   Re: limit clause produces wrong query plan  (Scott Carey, )

Scott,

>And how exactly should it be optimized?  If a query is even moderately
>interesting, with a few joins and a where clause, postgresql HAS to
>create the rows that come before your offset in order to assure that
>it's giving you the right rows.

SELECT ... FROM bigtable ORDER BY intprimarykey OFFSET 100 LIMIT 100

It should scan primary key in index order for 200 first keys and skipping
first 100 keys.

>> SELECT ... FROM bigtable ORDER BY intprimarykey OFFSET 0 LIMIT 100
>
> That should be plenty fast.

The example which I  posted shows that

SELECT ... FROM bigtable ORDER BY intprimarykey LIMIT 100

this is extremely *slow*: seq scan is  performed over whole bigtable.

> A standard workaround is to use some kind of sequential, or nearly so,
> id field, and then use between on that field.
>
> select * from table where idfield between x and x+100;

Users can delete and insert any rows in table.
This appoarch requires updating x in every row in big table after each
insert, delete or order column change and is thus extremely slow.
So I do'nt understand how this can be used for large tables.

Andrus.



В списке pgsql-performance по дате сообщения:

От: Scott Carey
Дата:
Сообщение: Re: limit clause produces wrong query plan
От: Greg Smith
Дата:
Сообщение: Re: Monitoring buffercache...