Re: functional index not used, looping simpler query just faster

Ivan Sergio Borgonovo <mail(at)webthatworks(dot)it> writes:
> Now I try this:

> explain select i1.brands, i1.name, i1.dataPub, i1.datainserimento
> from catalog_items i1
> inner join catalog_brands b1 on upper(i1.brands)=upper(b1.name)
> where i1.ItemID in (
> select i2.ItemID from catalog_items i2
> inner join catalog_brands b2 on upper(i2.brands)=upper(b2.name)
> where i1.brands=i2.brands
> and i2.dataPub>(now() - interval '8 month') and
> i2.datainserimento>(now() - interval '6 month') order by
> i2.datainserimento desc limit 3);

This sub-select is non optimizable because you've got an outer reference
in it, which compels re-evaluating it at every row of the outer query.
Try recasting as

explain select i1.brands, i1.name, i1.dataPub, i1.datainserimento
from catalog_items i1
inner join catalog_brands b1 on upper(i1.brands)=upper(b1.name)
where (i1.ItemID, i1.brands) in (
select i2.ItemID, i2.brands from catalog_items i2
inner join catalog_brands b2 on upper(i2.brands)=upper(b2.name)
where
i2.dataPub>(now() - interval '8 month') and
i2.datainserimento>(now() - interval '6 month') order by
i2.datainserimento desc limit 3);

regards, tom lane