From: | "Sriram Dandapani" <sdandapani(at)counterpane(dot)com> |
---|---|
To: | "Pgsql-Performance \(E-mail\)" <pgsql-performance(at)postgresql(dot)org> |
Subject: | creating of temporary table takes very long |
Date: | 2006-04-17 18:36:32 |
Message-ID: | 6992E470F12A444BB787B5C937B9D4DF0406A629@ca-mail1.cis.local |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
create temporary table c_chkpfw_hr_tr_updates as
select * from c_chkpfw_hr_tr a
where exists(select 1 from
chkpfw_tr_hr_dimension b
WHERE a.firstoccurrence =
b.firstoccurrence
AND a.sentryid_id = b.sentryid_id
AND a.node_id = b.node_id
AND a.customerid_id =
b.customerid_id
AND coalesce(a.interface_id,0) =
coalesce(b.interface_id,0)
AND coalesce(a.source_id,0) =
coalesce(b.source_id,0)
AND coalesce(a.destination_id,0) =
coalesce(b.destination_id,0)
AND coalesce(a.sourceport_id,0) =
coalesce(b.sourceport_id,0)
AND
coalesce(a.destinationport_id,0) = coalesce(b.destinationport_id,0)
AND coalesce(a.inoutbound_id,0) =
coalesce(b.inoutbound_id,0)
AND coalesce(a.action_id,0) =
coalesce(b.action_id,0)
AND coalesce(a.protocol_id,0) =
coalesce(b.protocol_id,0)
AND coalesce(a.service_id,0) =
coalesce(b.service_id,0)
AND coalesce(a.sourcezone_id,0) =
coalesce(b.sourcezone_id,0)
AND
coalesce(a.destinationzone_id,0) = coalesce(b.destinationzone_id,0));
This takes forever (I have to cancel the statement each time)
c_chkpfw_hr_tr has about 20000 rows
chkpfw_tr_hr_dimension has 150K rows
c_chkpfw_hr_tr has same indexes as chkpfw_tr_hr_dimension
For such a small data set, this seems like a mystery. The only other
alternative I have is to use cursors which are also very slow for row
sets of 10- 15K or more.
From | Date | Subject | |
---|---|---|---|
Next Message | Sriram Dandapani | 2006-04-17 18:52:15 | Re: creating of temporary table takes very long |
Previous Message | Sriram Dandapani | 2006-04-17 14:34:38 | slow cursor |