From: Allen Landsidel <alandsidel@gmail.com>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Matt Clark <matt@ymogen.net>
Cc: pgsql-performance@postgresql.org
Subject: Re: Strange (?) Index behavior?
Date: Sat, 6 Nov 2004 02:27:25 -0500
Message-ID: <88f1825a04110523276f83653b@mail.gmail.com> (raw)
In-Reply-To: <25492.1099713863@sss.pgh.pa.us>
References: <88f1825a041105095631064cb0@mail.gmail.com>
<008401c4c366$127d5260$8300a8c0@solent>
<88f1825a04110513028759dd3@mail.gmail.com>
<418C1757.70909@ymogen.net>
<25492.1099713863@sss.pgh.pa.us>
On Fri, 05 Nov 2004 23:04:23 -0500, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Matt Clark <matt@ymogen.net> 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?
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: alandsidel@gmail.com, tgl@sss.pgh.pa.us, matt@ymogen.net
Subject: Re: Strange (?) Index behavior?
In-Reply-To: <88f1825a04110523276f83653b@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox