Date: Wed, 14 Jul 2004 22:51:07 +0400
From: "Alexander M. Pravking" <fduch@antar.bryansk.ru>
To: pgsql-sql@postgresql.org
Subject: Indexable (constant LIKE prefix_keyfield) ?
Message-ID: <20040714185107.GL1737@dyatel.antar.bryansk.ru>
Mime-Version: 1.0
Content-Type: text/plain; charset=koi8-r
Content-Disposition: inline
User-Agent: Mutt/1.4.2.1i

I was impressed that LIKE operator can be indexed in 7.4 with non-C
locale. But how about the reverse condition?

What I need is to:
SELECT * FROM prefixes WHERE 'literal' LIKE prefix || '%';
or
SELECT * FROM prefixes WHERE 'literal' ~ ('^' || prefix);

Prefix is of type text (variable-length), which may contain only ASCII
chars (database runs under ru_RU.KOI8-R locale). Only the longest prefix
should be taken if more than one matches.

There's no strict definition for "prefixes" yet, and I seek for how to
make it possible to use an index by this query. The ways I see:

1. Sequentially rtrim('literal') and compare it to prefix.
    Really bad idea.

2. Use 2 fields: prefix_le and prefix_gt, then
    'literal' >= prefix_le AND 'literal' < prefix_gt
    (or 'literal' ~>=~ prefix_le AND 'literal' ~<~ prefix_gt, but it
    seems there's no need to).

    a) supply both fields from outside (I don't like this idea).

    b) supply only prefix (=prefix_le), and calculate prefix_gt (using
    trigger?) as prefix_le "plus one".

    Digging the backend sources, I've found make_greater_string used
    to expand indexable LIKE or regexp condition. Can I use it for my
    needs somehow? Or have I to write my own in this case?

3. Create some magical index I dunno about :)

4.  SELECT * FROM prefixes
    WHERE prefix <= 'literal' AND 'literal' LIKE prefix || '%'
    ORDER BY prefix DESC LIMIT 1;
    Looks like the best way, but I'm not sure this is always correct.


Comments, suggestions, please?

-- 
Fduch M. Pravking