Database Reference
In-Depth Information
The GIN index requires three times more space than GiST,
but is three times faster than GiST.
Let's take an example where we want to search a word from millions of words
using partial matches. The GIN index is best suited for these kinds of queries:
warehouse_db=# CREATE EXTENSION pg_trgm;
After creating the
pg_trgm
extension, we create the
words
table using the
following statement:
warehouse_db=# CREATE TABLE words(lineno INT, simple_words TEXT,
special_words TEXT);
We can insert data into the
words
table using the following statement:
warehouse_db=# INSERT INTO words VALUES
(generate_series(1,2000000), md5(random()::TEXT),
md5(random()::TEXT));
Let's try to execute a query to search for the words that have
a31
in
simple_words
and
special_words
and note the execution time of the query as follows:
warehouse_db=# SELECT count(*) FROM words WHERE simple_words LIKE
'%a31%' AND special_words LIKE '%a31%';
count
-------
116
(1 row)
Time: 1081.796 ms
Create a multicolumn index and try to execute the same query and note down the
timing of the query in the following manner:
warehouse_db=# CREATE INDEX words_idx ON words (simple_words,
special_words);
Time: 32822.055 ms
The result can be seen using the following statement:
warehouse_db=# SELECT count(*) FROM words WHERE simple_words LIKE
'%a31%' AND special_words LIKE '%a31%';
count
-------
116
(1 row)
Time: 1075.467 ms
