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 4BBCE3AA535 for ; Tue, 14 Dec 2004 19:35:42 +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 25590-09 for ; Tue, 14 Dec 2004 19:35:30 +0000 (GMT) Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by svr1.postgresql.org (Postfix) with ESMTP id 8969E3AA04B for ; Tue, 14 Dec 2004 19:35:28 +0000 (GMT) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.13.1/8.13.1) with ESMTP id iBEJZHe3016697; Tue, 14 Dec 2004 14:35:17 -0500 (EST) To: Sven Willenberger Cc: pgsql-performance@postgresql.org, andrew@catalyst.net.nz Subject: Re: Using LIMIT changes index used by planner In-reply-to: <1103048932.5534.22.camel@lanshark.dmv.com> References: <41BD3317.9090507@dmv.com> <1102931789.11712.9.camel@lamb.mcmillan.net.nz> <41BE1270.7060908@dmv.com> <28820.1102977787@sss.pgh.pa.us> <1103048932.5534.22.camel@lanshark.dmv.com> Comments: In-reply-to Sven Willenberger message dated "Tue, 14 Dec 2004 13:28:52 -0500" Date: Tue, 14 Dec 2004 14:35:17 -0500 Message-ID: <16696.1103052917@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.005 tagged_above=0 required=5 tests=AWL X-Spam-Level: X-Archive-Number: 200412/183 X-Sequence-Number: 9558 Sven Willenberger writes: > On a related note, is there a way (other than set enable_seqscan=off) to > give a hint to the planner that it is cheaper to use and index scan > versus seq scan? There are basically two things you can do. One: if the planner's rowcount estimates are badly off, you can try increasing the stats targets for relevant columns in hopes of making the estimates better. A too-large rowcount estimate will improperly bias the decision towards seqscan. Two: if the rowcounts are in the right ballpark but the estimated costs have nothing to do with reality, you can try tuning the planner's cost parameters to make the model match local reality a bit better. random_page_cost is the grossest knob here; effective_cache_size is also worth looking at. See the pgsql-performance archives for more discussion. > -> Index Scan using orderdate_idx on custacct > (cost=0.00..2657990.68 rows=43297 width=41) (actual > time=4.432..28145.212 rows=44333 loops=1) In this case there's already a pretty good match between actual and estimated rowcount, so increasing the stats targets isn't likely to improve the plan choice; especially since a more accurate estimate would shift the costs in the "wrong" direction anyway. Look to the cost parameters, instead. Standard disclaimer: don't twiddle the cost parameters on the basis of only one test case. regards, tom lane