X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 3513B3A614E for ; Fri, 28 Jan 2005 21:57:20 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 09120-09 for ; Fri, 28 Jan 2005 21:57:08 +0000 (GMT) Received: from mail.deg.cc (mail.deg.cc [64.139.134.201]) by svr1.postgresql.org (Postfix) with ESMTP id E656D3A60B9 for ; Fri, 28 Jan 2005 21:57:09 +0000 (GMT) Received: from deg.cc (kyle.deg.cc [198.70.16.205]) by mail.deg.cc (Postfix) with ESMTP id 5386856121; Fri, 28 Jan 2005 16:57:03 -0500 (EST) Message-ID: <41FAB54C.7050506@deg.cc> Date: Fri, 28 Jan 2005 16:57:32 -0500 From: Pallav Kalva User-Agent: Mozilla/5.0 (X11; U; Linux i686; en-US; rv:1.4.2) Gecko/20040308 X-Accept-Language: en-us, en MIME-Version: 1.0 To: Tom Lane Cc: PERFORM Subject: Re: Poor Performance on Postgres 8.0 References: <41FA5726.7040502@deg.cc> <2581.1106929396@sss.pgh.pa.us> <41FA6F29.9060009@deg.cc> <8990.1106931750@sss.pgh.pa.us> <41FA8697.1030200@deg.cc> <22896.1106941715@sss.pgh.pa.us> <41FA992B.2090405@deg.cc> <25930.1106945442@sss.pgh.pa.us> <41FAA76B.7070704@deg.cc> <26388.1106948064@sss.pgh.pa.us> In-Reply-To: <26388.1106948064@sss.pgh.pa.us> Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.05 tagged_above=0 required=5 tests=FORGED_RCVD_HELO X-Spam-Level: X-Archive-Number: 200501/601 X-Sequence-Number: 10291 The random_page_cost value is same on both the versions, the only thing difference between 7.4 version and 8 version is that 7.4 ver has 100k less records. For, now i created index on numericvalue column on attribute table and it used that index and it is much faster that way. it came down to 24msec. Also, i tried to see the matching id for squarefeet in attribute table there are 800 some records in attribute table for 8 version and 700 something in 7.4 version. Tom Lane wrote: >Pallav Kalva writes: > > >>On 8 >> common | attribute | fknamestringid | 0 | 4 >>| 80 | {2524,2434,2530,2522,2525,2523,2527,2526,2574,2531} | >>{0.219333,0.199333,0.076,0.0643333,0.0616667,0.05,0.0453333,0.042,0.04,0.0286667} >>| {2437,2528,2529,2538,2539,2540,2554,2562,2575,2584,2637} | 0.0274016 >> >> > >Given those stats, the planner is going to estimate that about 1/80th of >the attribute table matches any particular fknamestringid, and that's >what's driving it away from using the indexscan. I cannot tell whether >there are indeed a couple of thousand rows joining to the 'squareFeet' >string row (in which case the condition numericValue='775.0' must be >really selective) or whether this is an outlier case that joins to just >a few attribute rows. > >The slightly different stats values for 7.4 would have given it a >slightly lower value for the cost of an indexscan by >idx_attribute_fknamestringid, but certainly not as low as your original >message shows. Perhaps you have some difference in parameter settings >in your 7.4 installation --- most likely a lower random_page_cost. > > regards, tom lane > >---------------------------(end of broadcast)--------------------------- >TIP 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to majordomo@postgresql.org) > > >