Re: Questions about indexes with text_pattern_ops

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Gregory Stark <stark(at)enterprisedb(dot)com>
Cc: "Kaare Rasmussen" <kaare(at)jasonic(dot)dk>, pgsql-hackers(at)postgresql(dot)org
Subject: Re: Questions about indexes with text_pattern_ops
Date: 2008-02-25 16:29:39
Message-ID: 27907.1203956979@sss.pgh.pa.us
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

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.

>> In the meantime, though, I think the only way that Kaare's query can use
>> that index is if he writes
>> WHERE b LIKE 'whatever' AND b <> '';
>> (with whatever spelling of <> the index predicate has). There is not
>> anything in the predicate proving machinery that knows enough about LIKE
>> to be able to show that "b LIKE 'whatever'" implies "b <> ''".

> 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.

regards, tom lane

In response to

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Tom Lane 2008-02-25 17:00:05 Re: Strange behavior with leap dates and centuries BC
Previous Message Roberts, Jon 2008-02-25 16:27:17 Re: Tuning 8.3