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