Re: increasing effective_cache_size slows down join queries by a factor of 4000x

On 2/4/22 05:21, A Shaposhnikov wrote:
> Tomas,
>
> thank you! The query:
>
> select 1 from data as d, data_class as dc
> where dc.data_id = d.id and d.id > 205284974
> and dc.data_id > 205284974 -- new condition
> order by d.id
> limit 1000;
>
> totally solved it - it is now fast under all conditions! I thought
> that the optimizer would be able to infer it itself.
>

Unfortunately, the optimizer is not that smart - we can do that for
equality conditions, but not for other operators. There was actually a
thread [1] exploring a possibility to extend this to inequalities, but
it went nowhere so far. It also explains why it's done only for equality
operators. In short, it's fairly expensive, makes costing of joins more
difficult, and most queries can't benefit from it (because conditions on
join keys are not that common).

BTW how does the final query plan look like? Is it using the merge sort
of nested loop? I wonder if this might be formulated as a costing issue,
pushing the planner to use the nested loop.

[1]
https://www.postgresql.org/message-id/flat/CAFQUnFhqkWuPCwQ1NmHYrisHJhYx4DoJak-dV%2BFcjyY6scooYA%40mail.gmail.com

regards

--
Tomas Vondra
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company