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

Ivan Sergio Borgonovo <mail@webthatworks.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