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