{SOLVED?] Re: functional index not used, looping simpler query just faster

On Thu, 10 Jul 2008 10:46:53 -0400
Tom Lane <tgl@sss.pgh.pa.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