On 11/09/2007, Tom Lane <tgl@xxxxxxxxxxxxx> wrote: > Richard Huxton <dev@xxxxxxxxxxxx> 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 ---------------------------(end of broadcast)--------------------------- TIP 5: don't forget to increase your free space map settings
