![]() 34 Plan Rows: 41 Plan Width: 11Īctual Loops: 1 Index Cond: " (((fullname)::text ~>=~ 'Devonshire'::text) AND ((fullname)::text ~=~ 'Devonshire'::text) AND ((fullname)::text ~<~ 'Devonshirf'::text)) "įilter: " ((fullname)::text ~~ 'Devonshire%'::text) " Scan Direction: " Forward " Index Name: " idx_featnames_short_fullname_btree_ops " takes 13ms returns 8 rows, uses btree ops index SELECT DISTINCT fullnameĮXPLAIN (ANALYZE true, FORMAT yaml ) SELECT DISTINCT fullname ON featnames_short USING btree (fullname varchar_pattern_ops ) So here is theīasic old in action: CREATE INDEX idx_featnames_short_fullname_btree_ops Normally if you plan to do LIKE searches, you would create a btree ops on the column or functional index on the upper/lower of the column. You canĭownload the trimmed data set from here if you want to play along. In this article we'll demonstrate.įor this example we'll use a table of 490,000 someodd records consisting of Massachusetts street segments and their names excerpted from TIGER 2010 data. This is one example of how you can use applications designed for many databases and still be able to utilize advanced features in You work with, if you have a trigram index in place, it will just be doing it a little faster and sometimes a lot faster using the more clever PostgreSQL 9.1 planner. ![]() So while PostgreSQL 9.1 might be understanding LIKE much like all the other databases Have had before and you can do it in such a fashion that doesn't require any PostgreSQL trigram specific syntactical expressions. This means you can have an even faster trigram search than you ever There are 2 main enhancements happening with trigrams in PostgreSQL 9.1īoth of which depesz has already touched on in FASTER LIKE/ILIKEĪnd KNNGIST. Well just like 1 + 2 being a common mathematical expression, abc LIKE '%b%' is a common logical relational database expression that we have long taken for granted as not an indexable operation in mostĭatabases (not any other database to I can think of) until PostgreSQL 9.1, which can utilize trigram indices (the Lisp programmer behind the curtain) to make it fast. This brings us to what does this have to do with trigrams I find this episode interesting because while the Lisp programmer I feel is more right, the Smalltalk programmer has managed to follow the rest of the crowd and still stick "+ should be pushing those other two around."
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |