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 C1D303A3E77 for ; Fri, 5 Nov 2004 21:09:13 +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 62285-04 for ; Fri, 5 Nov 2004 21:09:03 +0000 (GMT) Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by svr1.postgresql.org (Postfix) with ESMTP id 6286F3A202A for ; Fri, 5 Nov 2004 21:09:03 +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 iA5L8ub1012856; Fri, 5 Nov 2004 16:08:56 -0500 (EST) To: Allen Landsidel Cc: pgsql-performance@postgresql.org Subject: Re: Strange (?) Index behavior? In-reply-to: <88f1825a0411051236497f6bbf@mail.gmail.com> References: <88f1825a041105063955367088@mail.gmail.com> <20041105175159.GB8855@wolff.to> <88f1825a041105095631064cb0@mail.gmail.com> <10898.1099684660@sss.pgh.pa.us> <88f1825a0411051236497f6bbf@mail.gmail.com> Comments: In-reply-to Allen Landsidel message dated "Fri, 05 Nov 2004 15:36:28 -0500" Date: Fri, 05 Nov 2004 16:08:56 -0500 Message-ID: <12855.1099688936@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.0 tagged_above=0.0 required=5.0 tests= X-Spam-Level: X-Archive-Number: 200411/113 X-Sequence-Number: 9067 Allen Landsidel writes: > With seqscan enabled however, "AB%" will use the index, but "A%" will not. > The estimated cost for the query is much higher without the partial > indexes than it is with them, and the actual runtime of the query is > definitely longer without the partial indexes. OK. This suggests that the planner is drastically misestimating the selectivity of the 'A%' clause, which seems odd to me since in principle it could get that fairly well from the ANALYZE histogram. But it could well be that you need to increase the resolution of the histogram --- see ALTER TABLE SET STATISTICS. Did you ever show us EXPLAIN ANALYZE results for this query? regards, tom lane