{SOLVED?] 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:

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

I came up with this. I'm still curious to know if this could be done
efficiently with just one query.

create table catalog_topbybrands (
ItemID bigint not null,
Code varchar(32) not null,
Name varchar(256) not null,
Brands varchar(1024),
Authors varchar(1024),
ISBN varchar(100),
dataInserimento timestamp,
dataPub timestamp
);
create table catalog_topbybrands_working (
ItemID bigint not null,
Brands varchar(1024),
dataInserimento timestamp,
dataPub timestamp
);

create or replace function TopByBrands()
returns void
as
$$
declare
_row catalog_brands%ROWTYPE;
begin
truncate table catalog_topbybrands;
truncate table catalog_topbybrands_working;
insert into catalog_topbybrands_working
(ItemID, Brands, dataInserimento, dataPub)
select i.ItemID, i.Brands, dataInserimento, dataPub from
catalog_items i
inner join catalog_brands b on upper(b.Name)=upper(i.Brands)
where
i.dataPub>(now() - interval '18 month')
and i.dataInserimento>(now() - interval '8 month')
and i.dataPub is not null and i.dataInserimento is not null
order by i.dataInserimento, i.dataPub;
for _row in (select * from catalog_brands) loop
insert into catalog_topbybrands
(ItemID, Code, Name, Brands, Authors, ISBN, dataInserimento, dataPub)
select i.ItemID, i.Code, i.Name, i.Brands, i.Authors, i.ISBN, i.dataInserimento, i.dataPub
from catalog_topbybrands_working w
join catalog_items i on i.ItemID=w.ItemID
where upper(w.Brands)=upper(_row.name)
order by dataInserimento desc, dataPub desc limit 3;
end loop;
return;
end;
$$ language plpgsql volatile;

just a working prototype. In fact considering that once filtered by
date etc... the temp table is very small it may perform better
avoiding last join in the last insert.

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