Re: Optimizer Hint, to ignore limit and offset in optimizer plan

Поиск
Список
Период
Сортировка
От Pavel Stehule
Тема Re: Optimizer Hint, to ignore limit and offset in optimizer plan
Дата
Msg-id CAFj8pRBDDnSLQ3GW3CtqVwprrwHDiN7EhAuX2xSdgwxpL5srwg@mail.gmail.com
обсуждение исходный текст
Ответ на Optimizer Hint, to ignore limit and offset in optimizer plan  (Martin Handsteiner <martin.handsteiner@sibvisions.com>)
Список pgsql-sql
Hi

pá 26. 6. 2020 v 10:49 odesílatel Martin Handsteiner <martin.handsteiner@sibvisions.com> napsal:

Hello,

 

I’m aware, that taking limit and offset into account of optimizer plan is not a bug.

 

Nevertheless it is very often an unwanted feature.

 

As the postgres db has the issue with not supporting cursors over commit/ rollback, it is necessary to use the limit and offset mechanism.

 

The problem now is, with either

  • not always possible to ensure a proper sort (application with sort on header click),
  • and also on complex queries and a lot of data, that will be slow when sorting the result.

 

So if there would be an optimizer hint, that tells the optimizer to ignore limit and offset on generating a plan, it would be perfect.

 

Mainly having the same optimizer plan without looking on limit and offset, the possibitlity of having mixed data is drastically reduced.

 

Is there possibly already an optimizer hint, to trickout the optimizer, and tell him, that I want all the data, even if there is for eg a limit 10 clause in the select?


There is an old trick (workaround) based on usage OFFSET 0.

you can use

SELECT * FROM (SELECT * FROM xxx OFFSET 0) s LIMIT 10;

In this case, a inner query isn't planned with LIMIT number.

Regards

Pavel

 

Thanks

  Martin

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

Предыдущее
От: Martin Handsteiner
Дата:
Сообщение: Optimizer Hint, to ignore limit and offset in optimizer plan
Следующее
От: Bruce Momjian
Дата:
Сообщение: Re: Optimizer Hint, to ignore limit and offset in optimizer plan