You might try rewriting the coalesces into a row comparison... WHERE row($4, $5, ...) IS NOT DISTINCT FROM row(interface_id, source_id, ...) See http://www.postgresql.org/docs/8.1/interactive/functions-comparisons.html#AEN13408 Note that the docs only show IS DISTINCT FROM, so you might have to do WHERE NOT row(...) IS DISTINCT FROM row(...) On Tue, Apr 18, 2006 at 09:13:04AM -0700, Sriram Dandapani wrote: > 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 > > ---------------------------(end of broadcast)--------------------------- > TIP 2: Don't 'kill -9' the postmaster > -- Jim C. Nasby, Sr. Engineering Consultant jnasby@xxxxxxxxxxxxx Pervasive Software http://pervasive.com work: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461
