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 5B25E55DC4 for ; Tue, 22 Mar 2005 16:23:12 +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 84150-08 for ; Tue, 22 Mar 2005 16:23:02 +0000 (GMT) Received: from hosting.commandprompt.com (128.commandprompt.com [207.173.200.128]) by svr1.postgresql.org (Postfix) with ESMTP id 0F84656727 for ; Tue, 22 Mar 2005 16:23:01 +0000 (GMT) Received: from [127.0.0.1] (clbb-248.saw.net [64.146.135.248]) (authenticated bits=0) by hosting.commandprompt.com (8.12.8/8.12.8) with ESMTP id j2MGKGkh006826; Tue, 22 Mar 2005 08:20:17 -0800 Message-ID: <42404663.2010504@commandprompt.com> Date: Tue, 22 Mar 2005 08:22:59 -0800 From: "Joshua D. Drake" User-Agent: Mozilla Thunderbird 1.0 (Windows/20041206) X-Accept-Language: en-us, en MIME-Version: 1.0 To: Alex Turner Cc: "pgsql-performance@postgresql.org" Subject: Re: Planner issue References: <33c6269f05032206565ca4f9f1@mail.gmail.com> In-Reply-To: <33c6269f05032206565ca4f9f1@mail.gmail.com> Content-Type: multipart/mixed; boundary="------------030506020405060507040501" X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.04 tagged_above=0 required=5 tests=AWL X-Spam-Level: X-Archive-Number: 200503/453 X-Sequence-Number: 11158 This is a multi-part message in MIME format. --------------030506020405060507040501 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit Alex Turner wrote: >I get the following output from explain analyze on a certain subset of >a large query I'm doing. > > > Try increases the statistics on the listprice column with alter table and then re-run analyze. alter table foo alter column set statistics Sincerely, Joshua D. Drake >>From the looks of it, I need to increase how often postgres uses an >index over a seq scan, but I'm not sure how to do that. I looked >through the run-time configuration docs on the website, but didn't see >anything pertaining to index selectivity. > >Thanks, > >Alex Turner >netEconomist > > >trendmls=# explain analyze select listnum from propmain where >listprice<=300000 and listprice>=220000; > QUERY PLAN >-------------------------------------------------------------------------------------------------------------- > Seq Scan on propmain (cost=0.00..15556.05 rows=6228 width=4) (actual >time=0.093..506.730 rows=5671 loops=1) > Filter: ((listprice <= 300000::numeric) AND (listprice >= 220000::numeric)) > Total runtime: 510.482 ms >(3 rows) > >trendmls=# explain analyze select listnum from propmain where >listprice<=300000 and listprice>=250000; > QUERY PLAN >------------------------------------------------------------------------------------------------------------------------------------------ > Index Scan using propmain_listprice_i on propmain >(cost=0.00..12578.65 rows=3486 width=4) (actual time=0.103..16.418 >rows=3440 loops=1) > Index Cond: ((listprice <= 300000::numeric) AND (listprice >= >250000::numeric)) > Total runtime: 18.528 ms >(3 rows) > >---------------------------(end of broadcast)--------------------------- >TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org > > -- Command Prompt, Inc., home of Mammoth PostgreSQL - S/ODBC and S/JDBC Postgresql support, programming shared hosting and dedicated hosting. +1-503-667-4564 - jd@commandprompt.com - http://www.commandprompt.com PostgreSQL Replicator -- production quality replication for PostgreSQL --------------030506020405060507040501 Content-Type: text/x-vcard; charset=utf-8; name="jd.vcf" Content-Transfer-Encoding: 7bit Content-Disposition: attachment; filename="jd.vcf" begin:vcard fn:Joshua Drake n:Drake;Joshua org:Command Prompt, Inc. adr:;;PO Box 215 ;Cascade Locks;OR;97014;US email;internet:jd@commandprompt.com title:Consultant tel;work:503-667-4564 tel;fax:503-210-0334 x-mozilla-html:FALSE url:http://www.commandprompt.com version:2.1 end:vcard --------------030506020405060507040501--