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

Text pattern JOINs that use indexes

From: Richard Brooksby <rb(at)ravenbrook(dot)com>
To: pgsql-novice(at)postgresql(dot)org
Subject: Text pattern JOINs that use indexes
Date: 2004-03-15 18:48:25
Message-ID: 5735CE52-76B1-11D8-B40A-000393D3C042@ravenbrook.com (view raw or flat)
Thread:
Lists: pgsql-novice
I'm having a lot of trouble getting JOINs to work well with text  
patterns.  I have a smallish table (4000 rows) containing prefixes that  
I want to inner-join to a large table (500000 rows) of strings.  In  
other words, I want to look up the few entries in the 500000 row table  
which start with the strings in the 4000 row table.  PostgreSQL insists  
on doing a sequential scan of the large table, even though it is  
indexed on the field I'm using for the join.

Anyone got a solution?

Here are the "explains":

explain select * from files where name like 'foo%';
                                  QUERY PLAN
------------------------------------------------------------------------ 
-----
  Index Scan using files_name_key on files  (cost=0.00..6.01 rows=1  
width=97)
    Index Cond: ((name >= 'foo'::text) AND (name < 'fop'::text))
    Filter: (name ~~ 'foo%'::text)


explain select * from test join files on files.name like test.filename  
|| '%';
                              QUERY PLAN
---------------------------------------------------------------------
  Nested Loop  (cost=20.00..9450496.28 rows=1888140 width=129)
    Join Filter: ("outer".name ~~ ("inner".filename || '%'::text))
    ->  Seq Scan on files  (cost=0.00..9776.28 rows=377628 width=97)
    ->  Materialize  (cost=20.00..30.00 rows=1000 width=32)
          ->  Seq Scan on test  (cost=0.00..20.00 rows=1000 width=32)


Responses

pgsql-novice by date

Next:From: Tom LaneDate: 2004-03-15 19:29:48
Subject: Re: Text pattern JOINs that use indexes
Previous:From: Bill MoseleyDate: 2004-03-15 18:10:26
Subject: Granting access

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