Date: Tue, 18 Apr 2006 17:34:11 -0500
From: "Jim C. Nasby" <jnasby@pervasive.com>
To: Sriram Dandapani <sdandapani@counterpane.com>
Cc: Tom Lane <tgl@sss.pgh.pa.us>,
	"Pgsql-Performance (E-mail)" <pgsql-performance@postgresql.org>
Subject: Re: creating of temporary table takes very long
Message-ID: <20060418223410.GL49405@pervasive.com>
References: <6992E470F12A444BB787B5C937B9D4DF0406A79C@ca-mail1.cis.local>
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: <6992E470F12A444BB787B5C937B9D4DF0406A79C@ca-mail1.cis.local>
User-Agent: Mutt/1.5.11

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@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
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 2: Don't 'kill -9' the postmaster
> 

-- 
Jim C. Nasby, Sr. Engineering Consultant      jnasby@pervasive.com
Pervasive Software      http://pervasive.com    work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf       cell: 512-569-9461