Skip site navigation (1) Skip section navigation (2)

Re: creating of temporary table takes very long

From: "Sriram Dandapani" <sdandapani(at)counterpane(dot)com>
To: "Sriram Dandapani" <sdandapani(at)counterpane(dot)com>,"Pgsql-Performance \(E-mail\)" <pgsql-performance(at)postgresql(dot)org>
Subject: Re: creating of temporary table takes very long
Date: 2006-04-17 18:52:15
Message-ID: 6992E470F12A444BB787B5C937B9D4DF0406A633@ca-mail1.cis.local (view raw or flat)
Thread:
Lists: pgsql-performance
Explain analyze on the select statement that is the basis for temp table
data takes forever. I turned off enable_seqscan but it did not have an
effect

 

________________________________

From: pgsql-performance-owner(at)postgresql(dot)org
[mailto:pgsql-performance-owner(at)postgresql(dot)org] On Behalf Of Sriram
Dandapani
Sent: Monday, April 17, 2006 11:37 AM
To: Pgsql-Performance (E-mail)
Subject: [PERFORM] creating of temporary table takes very long

 

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.

pgsql-performance by date

Next:From: Tom LaneDate: 2006-04-17 19:28:38
Subject: Re: creating of temporary table takes very long
Previous:From: Sriram DandapaniDate: 2006-04-17 18:36:32
Subject: creating of temporary table takes very long

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group