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

On Thu, 10 Jul 2008 10:46:53 -0400
Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> 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);

It's not doing what was doing the previous.

I know the concept of the previous one was correct since once I
placed stuff in a temp I finally got results in a reasonable time.
Yours is returning 3 records and not 3 records for each brands and I
know there are more than 3 record that satisfy the query.

the inner query doesn't have any relationship with the outer... so
it returns 3 records and the outer just pick up the same returned
record.

Were you trying to write something different?

thanks

--
Ivan Sergio Borgonovo
http://www.webthatworks.it