Re: Questions about indexes with text_pattern_ops

"Tom Lane" <tgl(at)sss(dot)pgh(dot)pa(dot)us> writes:

> Gregory Stark <stark(at)enterprisedb(dot)com> writes:
>> "Tom Lane" <tgl(at)sss(dot)pgh(dot)pa(dot)us> writes:
>>> I'm intending to get rid of ~=~ and ~<>~ for 8.4; there's no longer any
>>> reason why those slots in the pattern_ops classes can't be filled by the
>>> plain = and <> operators. (There *was* a reason when they were first
>>> invented --- but now that texteq will only return true for exact bitwise
>>> match, I think it's OK to assume these are equivalent.)
>
>> The only question is whether we'll keep that forever. I thought it was a good
>> idea at the time but I'm starting to wonder about the implications for
>> multi-key indexes.
>
> How so? If you think this change is a bad idea you'd better speak up
> PDQ.

Well I think it's fine for 'foo ' != 'foo' even if they sort similarly.

But I'm not sure it makes sense for <'foo ','a'> to sort after <'foo','b'> if
the locale says that 'foo ' should be compare "equal" to 'foo' and 'a' before
'b'.

I'm starting to think "equality" and "sorts interchangeably" are not the same
operator at all. On the other hand it may not be worth the complexity that
might bring.

>> I was thinking that the inequalities that the LIKE index scan introduces would
>> imply the inequality. I take it we generate those inequalities too late in the
>> planning process to use them for other planning?
>
> Hmm, good point [ experiments... ] Yeah, it seems we don't reconsider
> partial indexes after those clauses have been generated. Not sure how
> expensive it'd be to change that.

Perhaps we should always generate those inequalities even if there's no index
that can use them. Then calculate the regexp selectivity based only on the
regexp after the prefix.

That might also help constraint exclusion. Maybe merge joins?

--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com
Ask me about EnterpriseDB's Slony Replication support!