X-Original-To: pgsql-performance-postgresql.org@postgresql.org Received: from localhost (wm.hub.org [200.46.204.128]) by postgresql.org (Postfix) with ESMTP id 2FB3F9FB1CD for ; Mon, 11 Sep 2006 08:29:23 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.128]) (amavisd-new, port 10024) with ESMTP id 50256-03 for ; Mon, 11 Sep 2006 11:29:15 +0000 (UTC) X-Greylist: domain auto-whitelisted by SQLgrey- Received: from web60815.mail.yahoo.com (web60815.mail.yahoo.com [209.73.178.238]) by postgresql.org (Postfix) with SMTP id F324E9FA5F7 for ; Mon, 11 Sep 2006 08:29:14 -0300 (ADT) Received: (qmail 63080 invoked by uid 60001); 11 Sep 2006 11:29:13 -0000 DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=s1024; d=yahoo.com; h=Message-ID:Received:Date:From:Subject:To:In-Reply-To:MIME-Version:Content-Type:Content-Transfer-Encoding; b=rE1rLXqkmL2luU1K2u4hKjoiKxSNTEAM2rirvLXym0XOKjZW6HrFYV7Qs89dUSoTmbd5bE8qQozZYWPyk6QRriUA1eX4/I4aEUqoT5FHUzCR8RAEQLUuc/unsvzIGwVqJevdPUw86uBODHBBDeDfQnQOfOBtVDfDP9kS1JJN5vA= ; Message-ID: <20060911112913.63078.qmail@web60815.mail.yahoo.com> Received: from [84.176.156.139] by web60815.mail.yahoo.com via HTTP; Mon, 11 Sep 2006 04:29:13 PDT Date: Mon, 11 Sep 2006 04:29:13 -0700 (PDT) From: fardeen memon Subject: Re: Performance problem with joins To: pgsql-performance@postgresql.org In-Reply-To: <8114.1157726776@sss.pgh.pa.us> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="0-1416824215-1157974153=:62851" Content-Transfer-Encoding: 8bit X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=3.73 tagged_above=0 required=5 tests=DNS_FROM_RFC_ABUSE, DNS_FROM_RFC_POST, DNS_FROM_RFC_WHOIS, HTML_30_40, HTML_MESSAGE X-Spam-Level: *** X-Archive-Number: 200609/57 X-Sequence-Number: 20743 --0-1416824215-1157974153=:62851 Content-Type: text/plain; charset=iso-8859-1 Content-Transfer-Encoding: 8bit Thanks for the reply .. you are right after i changed tra_date to timestamp in the view it considered the index and the performance did increase a bit .. but still compared to the query without the joins its much less .. any idea why? here is the output of the explain query after changing the tra_date column to timestamp. "Merge Right Join (cost=229025.77..231549.17 rows=32995 width=366)" "Merge Cond: ("outer".wp_id = "inner".wp_id)" "-> Index Scan using pk_wertpapier on wertpapier wp (cost=0.00..1132.90 rows=30654 width=12)" "-> Sort (cost=229025.77..229108.26 rows=32995 width=366)" " Sort Key: fir.wp_id" "-> Hash Left Join (cost=190376.33..226549.51 rows=32995 width=366)" " Hash Cond: ("outer".fun_id = "inner".fun_id)" "-> Hash Left Join (cost=190374.58..226147.09 rows=32995 width=336)" " Hash Cond: ("outer".tra_id = "inner".tra_id)" "-> Merge Right Join (cost=182608.86..211107.70 rows=32995 width=326)" " Merge Cond: ("outer".tra_id = "inner".tra_id)" "-> Index Scan using uk1_perfcache270 on perfcache270 cac270 (cost=0.00..26360.00 rows=695309 width=19)" "-> Sort (cost=182608.86..182691.35 rows=32995 width=315)" " Sort Key: tra.tra_id" "-> Hash Left Join (cost=143070.31..180132.60 rows=32995 width=315)" " Hash Cond: ("outer".tra_id = "inner".tra_id)" "-> Hash Left Join (cost=134981.89..163162.72 rows=32995 width=305)" " Hash Cond: ("outer".tra_id = "inner".tra_id)" "-> Merge Right Join (cost=116451.34..130707.85 rows=32995 width=294)" " Merge Cond: ("outer".tra_id = "inner".tra_id)" "-> Index Scan using pk_indexperfcache90 on indexperfcache90 incac90 (cost=0.00..12969.65 rows=395189 width=18)" "-> Sort (cost=116451.34..116533.83 rows=32995 width=284)" " Sort Key: tra.tra_id" "-> Merge Right Join (cost=80758.34..113975.08 rows=32995 width=284)" " Merge Cond: ("outer".tra_id = "inner".tra_id)" "-> Index Scan using uk1_perfcache90 on perfcache90 cac90 (cost=0.00..30740.84 rows=814044 width=19)" "-> Sort (cost=80758.34..80840.83 rows=32995 width=273)" " Sort Key: tra.tra_id" "-> Hash Left Join (cost=26205.11..78282.08 rows=32995 width=273)" " Hash Cond: (('U'::text || ("outer".fir_cusip)::text) = ("inner".fir_cusip)::text)" "-> Hash Join (cost=24911.18..75586.30 rows=32995 width=263)" " Hash Cond: ("outer".per_id = "inner".per_id)" "-> Hash Join (cost=1658.41..40649.44 rows=32995 width=236)" " Hash Cond: ("outer".fir_id = "inner".fir_id)" "-> Bitmap Heap Scan on trade tra (cost=207.48..38208.67 rows=32995 width=180)" " Recheck Cond: (tra_date > '2006-06-30 00:00:00'::timestamp without time zone)" "-> Bitmap Index Scan on trade_date_index (cost=0.00..207.48 rows=32995 width=0)" " Index Cond: (tra_date > '2006-06-30 00:00:00'::timestamp without time zone)" "-> Hash (cost=1374.54..1374.54 rows=30554 width=56)" "-> Seq Scan on firma fir (cost=0.00..1374.54 rows=30554 width=56)" "-> Hash (cost=22630.62..22630.62 rows=248862 width=27)" "-> Seq Scan on person per (cost=0.00..22630.62 rows=248862 width=27)" "-> Hash (cost=1234.74..1234.74 rows=23674 width=34)" "-> Seq Scan on kurs_latest kurl (cost=0.00..1234.74 rows=23674 width=34)" "-> Hash (cost=16590.44..16590.44 rows=776044 width=19)" "-> Seq Scan on perfcache180 cac180 (cost=0.00..16590.44 rows=776044 width=19)" "-> Hash (cost=7137.93..7137.93 rows=380193 width=18)" "-> Seq Scan on indexperfcache180 incac180 (cost=0.00..7137.93 rows=380193 width=18)" "-> Hash (cost=6847.57..6847.57 rows=367257 width=18)" "-> Seq Scan on indexperfcache270 incac270 (cost=0.00..6847.57 rows=367257 width=18)" "-> Hash (cost=1.60..1.60 rows=60 width=34)" "-> Seq Scan on funktion fun (cost=0.00..1.60 rows=60 width=34)" It is still doing a sequence scan on the person , perfcache180 and perfcache270 table and with out the joins it performs a index scan on these tables. Is something wrong with the view? once again thanks for your help. Tom Lane wrote: fardeen memon writes: > What is it that i am doing wrong? I think the forced coercion to date type in the view case is preventing the planner from making a good guess about the selectivity of the condition on tra_date. It has stats about tra_date's distribution, but none about the distribution of "tra_date::date". regards, tom lane --------------------------------- Stay in the know. Pulse on the new Yahoo.com. Check it out. --0-1416824215-1157974153=:62851 Content-Type: text/html; charset=iso-8859-1 Content-Transfer-Encoding: 8bit Thanks for the reply .. you are right after i changed  tra_date to timestamp in the view it considered the index and the performance did increase a bit .. but still compared to the query without the joins its much less .. any idea why?

here is the output of the explain query after changing the tra_date column to timestamp.

"Merge Right Join  (cost=229025.77..231549.17 rows=32995 width=366)"
"Merge Cond: ("outer".wp_id = "inner".wp_id)"
"->  Index Scan using pk_wertpapier on wertpapier wp  (cost=0.00..1132.90 rows=30654 width=12)"
"->  Sort  (cost=229025.77..229108.26 rows=32995 width=366)"
"    Sort Key: fir.wp_id"
"->  Hash Left Join  (cost=190376.33..226549.51 rows=32995 width=366)"
"    Hash Cond: ("outer".fun_id = "inner".fun_id)"
"->  Hash Left Join  (cost=190374.58..226147.09 rows=32995 width=336)"
"    Hash Cond: ("outer".tra_id = "inner".tra_id)"
"->  Merge Right Join  (cost=182608.86..211107.70 rows=32995 width=326)"
"    Merge Cond: ("outer".tra_id = "inner".tra_id)"
"->  Index Scan using uk1_perfcache270 on perfcache270 cac270  (cost=0.00..26360.00 rows=695309 width=19)"
"->  Sort  (cost=182608.86..182691.35 rows=32995 width=315)"
"    Sort Key: tra.tra_id"
"->  Hash Left Join  (cost=143070.31..180132.60 rows=32995 width=315)"
"    Hash Cond: ("outer".tra_id = "inner".tra_id)"
"->  Hash Left Join  (cost=134981.89..163162.72 rows=32995 width=305)"
"    Hash Cond: ("outer".tra_id = "inner".tra_id)"
"->  Merge Right Join  (cost=116451.34..130707.85 rows=32995 width=294)"
"    Merge Cond: ("outer".tra_id = "inner".tra_id)"
"->  Index Scan using pk_indexperfcache90 on indexperfcache90 incac90  (cost=0.00..12969.65 rows=395189 width=18)"
"->  Sort  (cost=116451.34..116533.83 rows=32995 width=284)"
"    Sort Key: tra.tra_id"
"->  Merge Right Join  (cost=80758.34..113975.08 rows=32995 width=284)"
"    Merge Cond: ("outer".tra_id = "inner".tra_id)"
"->  Index Scan using uk1_perfcache90 on perfcache90 cac90  (cost=0.00..30740.84 rows=814044 width=19)"
"->  Sort  (cost=80758.34..80840.83 rows=32995 width=273)"
"    Sort Key: tra.tra_id"
"->  Hash Left Join  (cost=26205.11..78282.08 rows=32995 width=273)"
"    Hash Cond: (('U'::text || ("outer".fir_cusip)::text) = ("inner".fir_cusip)::text)"
"->  Hash Join  (cost=24911.18..75586.30 rows=32995 width=263)"
"    Hash Cond: ("outer".per_id = "inner".per_id)"
"->  Hash Join  (cost=1658.41..40649.44 rows=32995 width=236)"
"    Hash Cond: ("outer".fir_id = "inner".fir_id)"
"->  Bitmap Heap Scan on trade tra  (cost=207.48..38208.67 rows=32995 width=180)"
"    Recheck Cond: (tra_date > '2006-06-30 00:00:00'::timestamp without time zone)"
"->  Bitmap Index Scan on trade_date_index  (cost=0.00..207.48 rows=32995 width=0)"
"    Index Cond: (tra_date > '2006-06-30 00:00:00'::timestamp without time zone)"
"->  Hash  (cost=1374.54..1374.54 rows=30554 width=56)"
"->  Seq Scan on firma fir  (cost=0.00..1374.54 rows=30554 width=56)"
"->  Hash  (cost=22630.62..22630.62 rows=248862 width=27)"
"->  Seq Scan on person per  (cost=0.00..22630.62 rows=248862 width=27)"
"->  Hash  (cost=1234.74..1234.74 rows=23674 width=34)"
"->  Seq Scan on kurs_latest kurl  (cost=0.00..1234.74 rows=23674 width=34)"
"->  Hash  (cost=16590.44..16590.44 rows=776044 width=19)"
"->  Seq Scan on perfcache180 cac180  (cost=0.00..16590.44 rows=776044 width=19)"
"->  Hash  (cost=7137.93..7137.93 rows=380193 width=18)"
"->  Seq Scan on indexperfcache180 incac180  (cost=0.00..7137.93 rows=380193 width=18)"
"->  Hash  (cost=6847.57..6847.57 rows=367257 width=18)"
"->  Seq Scan on indexperfcache270 incac270  (cost=0.00..6847.57 rows=367257 width=18)"
"->  Hash  (cost=1.60..1.60 rows=60 width=34)"
"->  Seq Scan on funktion fun  (cost=0.00..1.60 rows=60 width=34)"


It is still doing a sequence scan on the person , perfcache180 and perfcache270 table and with out the joins it performs a index scan on these tables.

Is something wrong with the view?

once again thanks for your help.

Tom Lane <tgl@sss.pgh.pa.us> wrote:
fardeen memon writes:
> What is it that i am doing wrong?

I think the forced coercion to date type in the view case is preventing
the planner from making a good guess about the selectivity of the
condition on tra_date. It has stats about tra_date's distribution,
but none about the distribution of "tra_date::date".

regards, tom lane

Stay in the know. Pulse on the new Yahoo.com. Check it out. --0-1416824215-1157974153=:62851--