About index for temporay table in a plpgsql function

From: Emi Lu <emilu(at)encs(dot)concordia(dot)ca>
To: Postgres General <pgsql-general(at)postgresql(dot)org>
Subject: About index for temporay table in a plpgsql function
Date: 2006-03-16 22:14:58
Message-ID: 4419E362.4030303@encs.concordia.ca
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Hello,

A question about index for temporary table.

Postgresql 8.0.1, in a pl/pgsql function:
begin
...
-- Part I
create temporal table t1 as ((select ... from .. left join ..... where
... ) union (select .. from ... left join ... where ... ));
create index idx_t1_cols on t1(col1, col2) tablespace ts1;

...
-- Part II
insert into tableX
(
select ...
from tableY
left join t1 using (col1, col2)
where ... ...
)
...
end;

In the above example, the index for t1 can speed up the left join query
in part II (although, there is no analyze done after the creation of
index), right?

The temporary table will be dropped automatically at the end of the
function, right?
By the way, is there anythings need to pay especially attention about
temporary table under a function please?

Thanks a lot,
Ying

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Brandon Keepers 2006-03-16 22:37:42 Re: Relation 'pg_largeobject' does not exist
Previous Message Joshua D. Drake 2006-03-16 22:05:31 Re: PostgreSQL Knowledge Base