Thx Tom I guess I have to abandon the bulk update. The columns in the where clause comprise 80% of the table columns..So indexing all may not help. The target table will have on average 60-180 million rows. I will attempt the in instead of exist and let you know the result -----Original Message----- From: Tom Lane [mailto:tgl@xxxxxxxxxxxxx] Sent: Tuesday, April 18, 2006 9:10 AM To: Sriram Dandapani Cc: Pgsql-Performance (E-mail) Subject: Re: [PERFORM] creating of temporary table takes very long "Sriram Dandapani" <sdandapani@xxxxxxxxxxxxxxx> writes: > Got an explain analyze output..Here it is > "Seq Scan on c_chkpfw_hr_tr a (cost=0.00..225975659.89 rows=11000 > width=136) (actual time=2.345..648070.474 rows=22001 loops=1)" > " Filter: (subplan)" > " SubPlan" > " -> Bitmap Heap Scan on chkpfw_tr_hr_dimension b > (cost=1474.64..10271.13 rows=1 width=0) (actual time=29.439..29.439 > rows=1 loops=22001)" > " 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 (..)" > " -> Bitmap Index Scan on chkpfw_tr_hr_idx1 > (cost=0.00..1474.64 rows=38663 width=0) (actual time=12.144..12.144 > rows=33026 loops=22001)" > " Index Cond: (($0 = firstoccurrence) AND ($1 = > sentryid_id) AND ($2 = node_id))" > "Total runtime: 648097.800 ms" That's probably about as good a query plan as you can hope for given the way the query is written. Those COALESCE comparisons are all unindexable (unless you make functional indexes on the COALESCE expressions). You might get somewhere by converting the EXISTS to an IN, though. regards, tom lane
