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 4424A3A4027 for ; Sat, 6 Nov 2004 07:27:32 +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 03273-06 for ; Sat, 6 Nov 2004 07:27:26 +0000 (GMT) Received: from wproxy.gmail.com (wproxy.gmail.com [64.233.184.207]) by svr1.postgresql.org (Postfix) with ESMTP id F318D3A2B0B for ; Sat, 6 Nov 2004 07:27:26 +0000 (GMT) Received: by wproxy.gmail.com with SMTP id 58so80488wri for ; Fri, 05 Nov 2004 23:27:25 -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=GrbC+gbJ4S+MfWdBIxHXIj8Ez/FpKNXAz1Wq05xEiG0mYJsWDyEpv1w4q9UHOyfImav2LTTnXvQkbJzdLOJnfUpIaoI5JznKuFwZlmIvefx2GQDlUw/GlDADaBbsF2Gl98W90I6OiwRCFrr78mfB8vq9yIN1U+C7ZucLfON4NX4= Received: by 10.54.21.14 with SMTP id 14mr131514wru; Fri, 05 Nov 2004 23:27:25 -0800 (PST) Received: by 10.54.43.36 with HTTP; Fri, 5 Nov 2004 23:27:25 -0800 (PST) Message-ID: <88f1825a04110523276f83653b@mail.gmail.com> Date: Sat, 6 Nov 2004 02:27:25 -0500 From: Allen Landsidel Reply-To: Allen Landsidel To: Tom Lane Subject: Re: Strange (?) Index behavior? Cc: Matt Clark , pgsql-performance@postgresql.org In-Reply-To: <25492.1099713863@sss.pgh.pa.us> Mime-Version: 1.0 Content-Type: text/plain; charset=US-ASCII Content-Transfer-Encoding: 7bit References: <88f1825a041105095631064cb0@mail.gmail.com> <008401c4c366$127d5260$8300a8c0@solent> <88f1825a04110513028759dd3@mail.gmail.com> <418C1757.70909@ymogen.net> <25492.1099713863@sss.pgh.pa.us> 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/124 X-Sequence-Number: 9078 On Fri, 05 Nov 2004 23:04:23 -0500, Tom Lane wrote: > Matt Clark writes: > > Well, 74000/76000000 ~= 0.1%, way less than 1/26, so no surprise that an > > indexscan is better, and also no surprise that the planner can't know > > that I is such an uncommon initial char. > > But it *can* know that, at least given adequate ANALYZE statistics. > I'm pretty convinced that the basic answer to Allen's problem is to > increase the histogram size. How large he needs to make it is not > clear --- obviously his data distribution is not uniform, but I don't > have a fix on how badly non-uniform. > Tom just an update, it's now 2am.. several hours since I started that EXPLAIN ANALYZE and it still hasn't finished, so I've aborted it. I will do the example with the more precise substring instead to illustrate the performance differences, both with and without the substring index and report back here. I'm also interested in something someone else posted, namely that the 36 indexes I have, "A%" through "Z%" and "0%" through "9%" could be replaced with a single index like: "CREATE INDEX idx_table_field_substr ON table substr(field, 1, 1);" I'm wondering, histogram and other information aside, will this function as well (or better) than creating all the individual indexes?