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 |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
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)
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2004-03-15 19:29:48 | Re: Text pattern JOINs that use indexes |
Previous Message | Bill Moseley | 2004-03-15 18:10:26 | Granting access |