Re: Partial index with regexp not working

On 11/09/2007, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Richard Huxton <dev(at)archonet(dot)com> writes:
> > The planner isn't smart enough to figure out which queries can use this
> > index by examining them, it just looks for (NOT paid) in the WHERE
> > clause and if it doesn't find it, ignores the index.
>
> Well, it's a little bit brighter than that: it has some smarts about
> btree-indexable comparisons and about null-testing. For instance, it
> can figure out that "x > 3" implies "x > 0", and so a query WHERE x > 3
> could use a partial index WHERE x > 0. Also, assuming that the >
> operator is strict, it would recognize that WHERE x IS NOT NULL is
> implied.
>
> But there's certainly not anything in there that can make inferences
> about regex matches.

The suggestion in this thread that a regex index will come into play
only when the WHERE condition specifically mentions it was indeed the
key for me.

In my case, the ratio of alphanumeric values for a column to values
that are just plain IP addresses (numeric-plus-dots) is about 1:15, so
an index on the alphanumeric is a HUGE benefit.

This is a very smart difference from the world of the other major open
source database, so I'd say the planner is bright enough even when it
comes to regex.

Many thanks