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@sss.pgh.pa.us]
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@counterpane.com> 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