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

spgist text_ops and LIKE

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: (view raw, whole thread or download thread mbox)
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:


...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.


Robert Haas
The Enterprise PostgreSQL Company


pgsql-hackers by date

Next:From: Jim NasbyDate: 2012-02-01 23:47:05
Subject: Re: feature request - datum_compute_size and datum write_should be public
Previous:From: Jim NasbyDate: 2012-02-01 22:42:27
Subject: Re: Refactoring log_newpage

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