From: Tom Lane <tgl@sss.pgh.pa.us>
To: Sriram Dandapani <sdandapani@counterpane.com>
Cc: Pgsql-Performance \(E-mail\) <pgsql-performance@postgresql.org>
Subject: Re: creating of temporary table takes very long
Date: Tue, 18 Apr 2006 12:09:57 -0400
Message-ID: <13997.1145376597@sss.pgh.pa.us> (raw)
In-Reply-To: <6992E470F12A444BB787B5C937B9D4DF0406A654@ca-mail1.cis.local>
References: <6992E470F12A444BB787B5C937B9D4DF0406A654@ca-mail1.cis.local>
"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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: tgl@sss.pgh.pa.us, sdandapani@counterpane.com
Subject: Re: creating of temporary table takes very long
In-Reply-To: <13997.1145376597@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox