Re: Query with no result set, really really slow adding ORBDER BY / LIMIT clause

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(dot)stehule(at)gmail(dot)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
>