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: "Tom Lane" <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: "Pgsql-Performance \(E-mail\)" <pgsql-performance(at)postgresql(dot)org>
Subject: Re: creating of temporary table takes very long
Date: 2006-04-17 19:48:57
Message-ID: 6992E470F12A444BB787B5C937B9D4DF0406A650@ca-mail1.cis.local (view raw or flat)
Thread:
Lists: pgsql-performance
Explain output. I tried explain analyze but pgadmin froze after 10
minutes.


QUERY PLAN
"Seq Scan on c_chkpfw_hr_tr a  (cost=0.00..225975659.89 rows=11000
width=136)"
"  Filter: (subplan)"
"  SubPlan"
"    ->  Bitmap Heap Scan on chkpfw_tr_hr_dimension b
(cost=1474.64..10271.13 rows=1 width=0)"
"          Recheck Cond: (($0 = firstoccurrence) AND ($1 = sentryid_id)
AND ($2 = node_id))"
"          Filter: (($3 = customerid_id) AND (COALESCE($4, 0) =
COALESCE(interface_id, 0)) AND (COALESCE($5, 0) = COALESCE(source_id,
0)) AND (COALESCE($6, 0) = COALESCE(destination_id, 0)) AND
(COALESCE($7, 0) = COALESCE(sourceport_id, 0)) AND (COALESCE($8, 0) =
COALESCE(destinationport_id, 0)) AND (COALESCE($9, 0) =
COALESCE(inoutbound_id, 0)) AND (COALESCE($10, 0) = COALESCE(action_id,
0)) AND (COALESCE($11, 0) = COALESCE(protocol_id, 0)) AND (COALESCE($12,
0) = COALESCE(service_id, 0)) AND (COALESCE($13, 0) =
COALESCE(sourcezone_id, 0)) AND (COALESCE($14, 0) =
COALESCE(destinationzone_id, 0)))"
"          ->  Bitmap Index Scan on chkpfw_tr_hr_idx1
(cost=0.00..1474.64 rows=38663 width=0)"
"                Index Cond: (($0 = firstoccurrence) AND ($1 =
sentryid_id) AND ($2 = node_id))"

-----Original Message-----
From: Tom Lane [mailto:tgl(at)sss(dot)pgh(dot)pa(dot)us] 
Sent: Monday, April 17, 2006 12:29 PM
To: Sriram Dandapani
Cc: Pgsql-Performance (E-mail)
Subject: Re: [PERFORM] creating of temporary table takes very long 

"Sriram Dandapani" <sdandapani(at)counterpane(dot)com> writes:
> [ query snipped ]
> This takes forever (I have to cancel the statement each time)

How long did you wait?

> c_chkpfw_hr_tr has same indexes as chkpfw_tr_hr_dimension

Which would be what exactly?  What does EXPLAIN show for that SELECT?
(I won't make you post EXPLAIN ANALYZE, if you haven't got the patience
to let it finish, but you should at least provide EXPLAIN results.)

			regards, tom lane

pgsql-performance by date

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

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