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 9AD6F56711 for ; Tue, 22 Mar 2005 19:36:52 +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 49878-07 for ; Tue, 22 Mar 2005 19:36:49 +0000 (GMT) Received: from wproxy.gmail.com (wproxy.gmail.com [64.233.184.203]) by svr1.postgresql.org (Postfix) with ESMTP id 2ECBF566EE for ; Tue, 22 Mar 2005 19:36:46 +0000 (GMT) Received: by wproxy.gmail.com with SMTP id 70so1946181wra for ; Tue, 22 Mar 2005 11:36:47 -0800 (PST) DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com; h=received:message-id:date:from:reply-to:to:subject:cc:in-reply-to:mime-version:content-type:content-transfer-encoding:references; b=bkhaXP8whBv38ZRlj4CljTvyuZp+v22aZdnXxnoVJMoMG6iM6Bz1+gfJqW5iPJSBP24TMwF8yc9hl7gHvDsbL80dz2zgM+MTYt0ozT9KAIiROo+ybh3C7RJ3VpgQ9daf4oawjYyejMz52WEMsYY44ad0hgq2p8Frw9syOvzq97s= Received: by 10.54.51.64 with SMTP id y64mr594996wry; Tue, 22 Mar 2005 11:36:46 -0800 (PST) Received: by 10.54.86.16 with HTTP; Tue, 22 Mar 2005 11:36:46 -0800 (PST) Message-ID: <33c6269f05032211366ce4563b@mail.gmail.com> Date: Tue, 22 Mar 2005 14:36:46 -0500 From: Alex Turner Reply-To: Alex Turner To: "Joshua D. Drake" Subject: Re: Planner issue Cc: "pgsql-performance@postgresql.org" In-Reply-To: <42404663.2010504@commandprompt.com> Mime-Version: 1.0 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit References: <33c6269f05032206565ca4f9f1@mail.gmail.com> <42404663.2010504@commandprompt.com> X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.24 tagged_above=0 required=5 tests=AWL, DNS_FROM_RFC_ABUSE, RCVD_BY_IP X-Spam-Level: X-Archive-Number: 200503/465 X-Sequence-Number: 11170 This helps a bit when I set it to 1000 - but it's still pretty bad: I will use an index 220-300, but not 200-300. Alex trendmls=# explain analyze select listnum from propmain where listprice<=300000 and listprice>=200000; QUERY PLAN -------------------------------------------------------------------------------------------------------------- Seq Scan on propmain (cost=0.00..15517.56 rows=6842 width=4) (actual time=0.039..239.760 rows=6847 loops=1) Filter: ((listprice <= 300000::numeric) AND (listprice >= 200000::numeric)) Total runtime: 244.301 ms (3 rows) trendmls=# set enable_seqscan=off; SET trendmls=# explain analyze select listnum from propmain where listprice<=300000 and listprice>=200000; QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------------------ Index Scan using propmain_listprice_i on propmain (cost=0.00..22395.95 rows=6842 width=4) (actual time=0.084..25.751 rows=6847 loops=1) Index Cond: ((listprice <= 300000::numeric) AND (listprice >= 200000::numeric)) Total runtime: 30.193 ms (3 rows) trendmls=# On Tue, 22 Mar 2005 08:22:59 -0800, Joshua D. Drake wrote: > 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 > > >