| From: | Robert Haas <robertmhaas(at)gmail(dot)com> |
|---|---|
| To: | pgsql-hackers(at)postgresql(dot)org, Teodor Sigaev <teodor(at)sigaev(dot)ru>, Oleg Bartunov <oleg(at)sai(dot)msu(dot)su> |
| Subject: | spgist text_ops and LIKE |
| Date: | 2012-02-01 22:50:45 |
| Message-ID: | CA+TgmobxzEkZO-bxWM+7qeuewG481eGqVEyfhDWHq7rokUKY8Q@mail.gmail.com |
| Views: | Whole Thread | Raw Message | Download mbox | Resend email |
| Thread: | |
| Lists: | pgsql-hackers |
Is spgist intended to support prefix searches with LIKE?
I ask because, first, it seems like something spgist ought to be good
at (unless I'm confused), and, second, the text_ops opfamily includes
these operators:
~<~(text,text)
~<=~(text,text)
~>=~(text,text)
~>~(text,text)
...which seems to be the same operators that are used for btree
pattern-matching searches:
rhaas=# explain select count(*) from person where last_name like 'WAR%';
QUERY PLAN
------------------------------------------------------------------------------------------
Aggregate (cost=2519.27..2519.28 rows=1 width=0)
-> Bitmap Heap Scan on person (cost=24.70..2496.75 rows=9005 width=0)
Filter: (last_name ~~ 'WAR%'::text)
-> Bitmap Index Scan on person_tpo (cost=0.00..22.45
rows=900 width=0)
Index Cond: ((last_name ~>=~ 'WAR'::text) AND
(last_name ~<~ 'WAS'::text))
(5 rows)
...but when I create an index like this:
create index person_spg on person using spgist (last_name text_ops);
...I can't get LIKE to use it, even if I disable seqscans.
Thoughts?
--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
| From | Date | Subject | |
|---|---|---|---|
| Next Message | Jim Nasby | 2012-02-01 23:47:05 | Re: feature request - datum_compute_size and datum write_should be public |
| Previous Message | Jim Nasby | 2012-02-01 22:42:27 | Re: Refactoring log_newpage |