Proper indexation settings for search in large PostgreSQL table

Question

Trying to get search results from database as fast as possible. The problem is, even after creating index for every field that search goes on, results coming back from PostgreSQL server are insanely slow: at least 20 seconds.

Any suggestions?

More details

Here is the sql query:

SELECT COUNT(*) 
FROM "Part" 
WHERE ("part_no"='aeroflex') 
   OR ("msn"='aeroflex') 
   OR ("nsn"='aeroflex') 
   OR ("name" ILIKE '%aeroflex%') 
   OR ("manufacturer" ILIKE '%aeroflex%') 
   OR ("mfg_sku"='aeroflex')

And here is the SQL dump of table which consists of 2.8 million rows

-- ----------------------------
--  Table structure for Part
-- ----------------------------
DROP TABLE IF EXISTS "electronic_parts"."Part";
CREATE TABLE "electronic_parts"."Part" (
    "id" int4 NOT NULL DEFAULT nextval('"Part_id_seq"'::regclass),
    "part_no" varchar COLLATE "default",
    "manufacturer" varchar COLLATE "default",
    "description" text COLLATE "default",
    "slug" varchar COLLATE "default",
    "nsn" varchar COLLATE "default",
    "price" numeric,
    "name" varchar COLLATE "default",
    "mfg_sku" varchar COLLATE "default",
    "msn" varchar COLLATE "default"
)
WITH (OIDS=FALSE);
ALTER TABLE "electronic_parts"."Part" OWNER TO "root";

-- ----------------------------
--  Primary key structure for table Part
-- ----------------------------
ALTER TABLE "electronic_parts"."Part" ADD PRIMARY KEY ("id") NOT DEFERRABLE INITIALLY IMMEDIATE;

-- ----------------------------
--  Indexes structure for table Part
-- ----------------------------
CREATE INDEX  "part_i1" ON "electronic_parts"."Part" USING btree(msn COLLATE "default" "pg_catalog"."text_ops" ASC NULLS LAST);
CREATE INDEX  "part_i2" ON "electronic_parts"."Part" USING gin(to_tsvector('english'::regconfig, name::text) "pg_catalog"."tsvector_ops") WITH (FASTUPDATE = YES);
CREATE INDEX  "part_i2" ON "electronic_parts"."Part" USING gin(to_tsvector('english'::regconfig, name::text) "pg_catalog"."tsvector_ops") WITH (FASTUPDATE = YES);
CREATE INDEX  "part_i3" ON "electronic_parts"."Part" USING gin(to_tsvector('english'::regconfig, manufacturer::text) "pg_catalog"."tsvector_ops") WITH (FASTUPDATE = YES);
CREATE INDEX  "part_i3" ON "electronic_parts"."Part" USING gin(to_tsvector('english'::regconfig, manufacturer::text) "pg_catalog"."tsvector_ops") WITH (FASTUPDATE = YES);
CREATE INDEX  "part_i4" ON "electronic_parts"."Part" USING gin(to_tsvector('english'::regconfig, description) "pg_catalog"."tsvector_ops") WITH (FASTUPDATE = YES);
CREATE INDEX  "part_i4" ON "electronic_parts"."Part" USING gin(to_tsvector('english'::regconfig, description) "pg_catalog"."tsvector_ops") WITH (FASTUPDATE = YES);
CREATE UNIQUE INDEX  "part_u1" ON "electronic_parts"."Part" USING btree(part_no COLLATE "default" "pg_catalog"."text_ops" ASC NULLS LAST);
CREATE UNIQUE INDEX  "part_u2" ON "electronic_parts"."Part" USING btree(nsn COLLATE "default" "pg_catalog"."text_ops" ASC NULLS LAST);
CREATE UNIQUE INDEX  "part_u3" ON "electronic_parts"."Part" USING btree(mfg_sku COLLATE "default" "pg_catalog"."text_ops" ASC NULLS LAST);
CREATE UNIQUE INDEX  "part_u4" ON "electronic_parts"."Part" USING btree(slug COLLATE "default" "pg_catalog"."text_ops" ASC NULLS LAST);

Here is the EXPLAIN

https://explain.depesz.com/s/5Lh

System specs

DB: PostgreSQL 9.5.6 on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 5.4.0-6ubuntu1~16.04.4) 5.4.0 20160609, 64-bit

OS: Ubuntu 16.04.2 LTS

RAM: 512MB (It's just for development purposes)

There is no load at all on server. Currently testing as 1 user.

Please always add `EXPLAIN ANALYZE` results of your queries when the question is performance related. -- The standard `text_ops` operator class will not help `LIKE` and `ILIKE` queries, when the pattern is not left anchored (i.e. it should help if you search for `"manufacturer" LIKE 'aeroflex%'` -- notice that the pattern does not start with `%` neither `_`). -- `gin_trgm_ops` or `gist_trgm_ops` of the [`pg_trgm`](https://www.postgresql.org/docs/current/static/pgtrgm.html) module can help you with these `ILIKE` searches. — pozs, Mar 24 '17 at 15:19
Read through the [info link on the postgresql-performance tag](http://stackoverflow.com/tags/postgresql-performance/info). Edit your question, and include those things. — Mike Sherrill 'Cat Recall', Mar 24 '17 at 15:47
@MikeSherrill'CatRecall' updated my question. https://explain.depesz.com/s/5Lh — demonoid, Mar 24 '17 at 16:10
@pozs please help me with more details. gin_trgm_ops or gist_trgm_ops which one to select? I'm newbie to postgre. Previous database was MySQL — demonoid, Mar 24 '17 at 16:11
@demonoid then the [module's docs](https://www.postgresql.org/docs/current/static/pgtrgm.html#AEN182717) are a good start. -- also, some [general information about GIN vs. GiST here](https://www.postgresql.org/docs/current/static/textsearch-indexes.html) [and here](http://stackoverflow.com/questions/28975517/difference-between-gist-and-gin-index). — pozs, Mar 24 '17 at 16:15
@pozs do you think that everything else is good in my structure? — demonoid, Mar 24 '17 at 16:21
@demonoid PostgreSQL might not choose any index because of your query's complexity, but in theory it could (with proper indexes). But this really depends on a lot of things. — pozs, Mar 24 '17 at 16:23
@pozs tried everything you said. updated my question. nothing changed in terms of performancehttps://explain.depesz.com/s/qjRT — demonoid, Mar 24 '17 at 17:13
These two conditions probably cause the table scan on Parts: `("name" ILIKE '%aeroflex%')`, `("manufacturer" ILIKE '%aeroflex%')`. You can test by commenting out or removing these two, and running `explain analyze...` again. — Mike Sherrill 'Cat Recall', Mar 24 '17 at 17:49
@demonoid you are supposed to add the `gin_trgm_ops` or `gist_trgm_ops` index to speed up the `ILIKE` query, not the FTS (`tsvector_ops`) one. The second link I added is just for comparison of the two. — pozs, Mar 27 '17 at 07:57

score 0 · Answer 1 · answered Mar 24 '17 at 19:08

Likely, it's the following two parts to your query that are slowing it down: ("name" ILIKE '%aeroflex%') and ("manufacturer" ILIKE '%aeroflex%'). The reason for this, is postgres can't use a regular index with an ilike operator that doesn't start the string (i.e. it can use an index to speed up 'aeroflex%', but not '%aeroflex%'.

Your indexes show that you did some research into this, by creating indexes with to_tsvector, but ilike doesn't use these indexes, instead you need to use the special operator @@ with to_tsquery

To take advantage of to_tsvector indexes you've already created use a where constraint like:

where to_tsvector('english', "name") @@ to_tsquery('english', 'aeroflex')

Alternatively, if you'd prefer to use like and ilike, you can make use of the pgtrm module.

First: Activate the module

CREATE EXTENSION pg_trgm;

Next: Add the index on the columns you want to filter with like or ilike

CREATE INDEX part_name_trigram_i ON "Part" USING GIN("name" gin_trgm_ops);

You can find more information on trigrams from the documentation page https://www.postgresql.org/docs/current/static/pgtrgm.html and on natural language search in postgres at https://www.postgresql.org/docs/current/static/datatype-textsearch.html

Proper indexation settings for search in large PostgreSQL table

More details

System specs

1 Answers1