On Thu, 10 Jul 2008 10:46:53 -0400 Tom Lane <tgl@xxxxxxxxxxxxx> wrote: > 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); 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
