X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by postgresql.org (Postfix) with ESMTP id 785A611F6004 for ; Tue, 18 Apr 2006 13:10:09 -0300 (ADT) X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Score: 0.127 X-Spam-Level: X-Spam-Status: No, score=0.127 required=5 tests=[AWL=0.127] Received: from postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id wIt04t9mwPC0 for ; Tue, 18 Apr 2006 13:10:02 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey- Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by postgresql.org (Postfix) with ESMTP id 63C9111F6262 for ; Tue, 18 Apr 2006 13:10:00 -0300 (ADT) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.13.6/8.13.6) with ESMTP id k3IG9vHs013998; Tue, 18 Apr 2006 12:09:57 -0400 (EDT) To: "Sriram Dandapani" cc: "Pgsql-Performance \(E-mail\)" Subject: Re: creating of temporary table takes very long In-reply-to: <6992E470F12A444BB787B5C937B9D4DF0406A654@ca-mail1.cis.local> References: <6992E470F12A444BB787B5C937B9D4DF0406A654@ca-mail1.cis.local> Comments: In-reply-to "Sriram Dandapani" message dated "Mon, 17 Apr 2006 13:05:17 -0700" Date: Tue, 18 Apr 2006 12:09:57 -0400 Message-ID: <13997.1145376597@sss.pgh.pa.us> From: Tom Lane X-Archive-Number: 200604/384 X-Sequence-Number: 18468 "Sriram Dandapani" 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