Re: [PERFORM] Query with no result set, really really slow addingORBDER BY / LIMIT clause

Поиск
Список
Период
Сортировка
От Marco Renzi
Тема Re: [PERFORM] Query with no result set, really really slow addingORBDER BY / LIMIT clause
Дата
Msg-id CAHmtSMYgiJbGGkphC+DVrmUCFyMOEwjGO9=NAsjJAToPbfLqQg@mail.gmail.com
обсуждение исходный текст
Ответ на Re: [PERFORM] Query with no result set, really really slow addingORBDER BY / LIMIT clause  (Pavel Stehule <pavel.stehule@gmail.com>)
Ответы Re: [PERFORM] Query with no result set, really really slow addingORBDER BY / LIMIT clause
Список pgsql-performance
Дерево обсуждения
This could look strange, but is fast as hell!
The main problem is:
Is everytime ok doing query like this with order by and limit? Is ok using an upperlimit to 1.000.000.000 records?

SELECT * FROM (
SELECT  fase.id
FROM            tipofase
JOIN       fase
ON         (fase.tipofase = tipofase.id)
WHERE agendafrontoffice = true
ORDER BY        fase.id DESC   limit 1000000000 offset 0
) A
ORDER BY        A.id DESC   limit 10 offset 0

2017-04-20 18:05 GMT+02:00 Pavel Stehule <pavel.stehule@gmail.com>:



I am afraid so is not possible to solve this issue by one query. In this case the planner expects early stop due finding few values. But because there are not any value, the LIMIT clause has not any benefit in executor time, but the planner is messed. Maybe try to increase LIMIT to some higher value .. 1000, 10000 so planner don't fall to this trap. PostgreSQL statistics are about most common values, but the values without any occurrence are not well registered by statistics.

Regards

It can looks strange, but it can work

SELECT *
   FROM (your query ORDER BY .. OFFSET 0 LIMIT 10000) s
  ORDER BY ...
  LIMIT 10;

Regards

Pavel

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

Предыдущее
От: Pavel Stehule
Дата:
Сообщение: Re: [PERFORM] Query with no result set, really really slow addingORBDER BY / LIMIT clause
Следующее
От: Pavel Stehule
Дата:
Сообщение: Re: [PERFORM] Query with no result set, really really slow addingORBDER BY / LIMIT clause