tags: PostgreSQL, index, regex

Action executed in 0.000

Each Tag

PostgreSQL, index, regex

Common tags - number of posts

import (2), database (2), tuning (2), planet (2), OSM (2), GIS (1), code (1), tile (1), Areca (1), TIGER (1), map (1), osmosis (1), Linux (1), string (1), XAPI (1), jxapi (1), devops (1), PostGIS (1), Po (1), femenism (1), pgsnapshot (1), RAID (1), programming (1), library (1), citext (1), procedural language (1), Akonadi (1), disk (1), trigram (1), render (1), aggregation (1), PHP (1), Python (1), ERD (1), up-to-date (1), search (1), beer (1), query (1), text (1), pnorman (1), mapnik (1), case (1), osm2pgsql (1), ack (1),

Sub groups 2

Sub groups 1

PostgreSQL (2), PostGIS, PostgreSQL, TIGER, beer, database, map, programming (1), ack, code, index, search (1), Akonadi, ERD, PostgreSQL, database (1), PHP, PostgreSQL, procedural language (1), GIS, PostgreSQL, library (1), OSM, PostgreSQL, XAPI, import, jxapi, osmosis, pgsnapshot, planet, pnorman, tuning (1), Areca, Linux, PostgreSQL, RAID, disk (1), PostgreSQL, aggregation, query (1), Po, index, string, trigram (1), PostgreSQL, Python, devops, femenism (1), PostgreSQL, case, citext, text (1), OSM, PostgreSQL, import, mapnik, osm2pgsql, planet, render, tile, tuning, up-to-date (1)

●●●●●○○○

select * from depesz; » Blog Archive » speeding up like ‘%xxx%’

DSHL logo

as most of you know postgresql can easily speedup searches using:

general idea is to use some kind of full text search/indexing – tsearch, lucene, sphinx, you name it.

but sometimes you can’t install fts/fti, or it doesn’t really solve your problem. is there any help? let’s find out.

url: http://www.depesz.com/index.php/2007/09/15/speeding-up-like-xxx/

type: article, format: blog

●●●●●○○○

select * from depesz; » Blog Archive » indexable ” field like ‘%something’”

DSHL logo

for the long time everybody knew that you can’t use index on “LIKE” operations.

then came text_pattern_ops, so we could use indexes for prefix searches:

but what if i’d like to search for ‘%something’? not prefix, but suffix. in my example – what can i do to use indexes when searching for people from given domain?

url: http://www.depesz.com/index.php/2007/07/30/indexable-field-like-something/

type: article, format: blog