From: | Gregory Stark <stark(at)enterprisedb(dot)com> |
---|---|
To: | "Tom Lane" <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
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:14:19 |
Message-ID: | 87ejb13tes.fsf@oxford.xeocode.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
"Tom Lane" <tgl(at)sss(dot)pgh(dot)pa(dot)us> writes:
> Gregory Stark <stark(at)enterprisedb(dot)com> writes:
>> Hm, for a simple = or <> I think it doesn't matter which operator class you
>> use. For < or > it would produce different answers. Postgres isn't clever enough
>> to notice that this is equivalent though so I think you would have to do
>> something like (untested):
>
>> CREATE INDEX new_index ON a (b text_pattern_ops) WHERE b ~<>~ '';
>
>> That uses the same operator that the LIKE clause will use for the index range.
>
> 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.
> 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?
--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com
Ask me about EnterpriseDB's 24x7 Postgres support!
From | Date | Subject | |
---|---|---|---|
Next Message | Gregory Stark | 2008-02-25 16:23:39 | Re: Tuning 8.3 |
Previous Message | Andrew Dunstan | 2008-02-25 16:10:57 | Re: Tuning 8.3 |