Re: Text pattern JOINs that use indexes

On 16 Mar 2004, at 18:07, joseph speigle wrote:

>> Thanks, Tom, I can now see why the planner is making the choice it
>> does. I suppose in theory if I could guarantee that "test.filename"
>> didn't contain '%' then the planner could do better, if it was clever
>> enough.
>>
>> Do you have a suggestion for how I achieve what I want? My current
>> solution is a function with nested FOR loops, but it seems a great
>> shame to have to write it all out by hand.
>
> can you forward the solution as you have it now? I am very interested
> in how this question turns out.

I'm afraid I can't forward the exact code as it contains client
confidential stuff, but here's basically what I do. Imagine the
"foo_prefixes" table contains a small number (thousands) of prefixes
with ids, and the bar_strings table contains a large number (millions)
of strings with ids. You want a view showing the strings which match
the prefixes. You can't write:

CREATE VIEW foo_strings AS
SELECT foo.id AS foo_id, bar.string AS bar_string
FROM foo_prefixes, bar_strings
WHERE bar_strings.string LIKE foo_prefixes.prefix || '%';

Well, you can write that, but it won't use a btree index on
bar_strings(string) because the planned doesn't know that the prefix
doesn't contain wildcards. So instead we have to plan each lookup with
a constant string:

CREATE OR REPLACE FUNCTION foo_strings() RETURNS SETOF record AS '
DECLARE
r record;
s record;
BEGIN
FOR r IN
SELECT id, prefix FROM foo_prefixes
LOOP
FOR s IN EXECUTE ''
SELECT '' || r.id || '' AS foo_id,
string AS bar_string
FROM bar_strings
WHERE string LIKE '''''' || r.prefix || ''%''''''
LOOP
RETURN NEXT s;
END LOOP;
END LOOP;
RETURN;
END;
' LANGUAGE 'plpgsql';

CREATE VIEW foo_strings AS
SELECT * FROM foo_strings() AS (foo_id int, bar_string text);

---
Richard Brooksby <rb(at)ravenbrook(dot)com>
Senior Consultant
Ravenbrook Limited <http://www.ravenbrook.com/>
PO Box 205, Cambridge CB2 1AN, United Kingdom
Voice: +44 777 9996245 Fax: +44 870 1641432