From: | "Alexander M(dot) Pravking" <fduch(at)antar(dot)bryansk(dot)ru> |
---|---|
To: | pgsql-sql(at)postgresql(dot)org |
Subject: | Indexable (constant LIKE prefix_keyfield) ? |
Date: | 2004-07-14 18:51:07 |
Message-ID: | 20040714185107.GL1737@dyatel.antar.bryansk.ru |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
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
From | Date | Subject | |
---|---|---|---|
Next Message | R.Welz | 2004-07-15 00:23:06 | calling function , passing OLD as argument |
Previous Message | Marcos Medina | 2004-07-14 15:13:40 | FOR-IN-EXECUTE, why fail? |