Skip site navigation (1) Skip section navigation (2)

Re: Text pattern JOINs that use indexes

From: Richard Brooksby <rb(at)ravenbrook(dot)com>
To: joseph speigle <joe(dot)speigle(at)jklh(dot)us>
Cc: pgsql-novice(at)postgresql(dot)org
Subject: Re: Text pattern JOINs that use indexes
Date: 2004-03-16 18:32:09
Message-ID: 3C58456A-7778-11D8-82C5-000393D3C042@ravenbrook.com (view raw or flat)
Thread:
Lists: pgsql-novice
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


In response to

Responses

pgsql-novice by date

Next:From: Markus BertheauDate: 2004-03-16 19:18:04
Subject: Re: conversion of postgres database to oracle
Previous:From: joseph speigleDate: 2004-03-16 18:07:14
Subject: Re: Text pattern JOINs that use indexes

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group