With what I've been able to gather thus far, if you need to run a full text search on a table in a PostgreSQL (psql 9.6.2, server 9.6.5) database with lots of entries (say on the order 1.2M+), the recommended approach is to create an index for that table (in this case we've created a GIN index) which should allow you to run a query like this:
SELECT * FROM speech WHERE speech_tsv @@ plainto_tsquery('a text string')
In addition to the results of this query sometimes not containing anything related search string, it often will take anywhere from 8-10 seconds.
The db is deployed on a fairly sizable, multicore EC2 instance, so I'm thinking, is it possible there's something else we could do to the db to help these queries run faster?
Or this the query execution time about reasonable given the huge amount of files and text we're asking it to search through (even via indexing)?
Here's what the table looks like:
Table "public.speech"
Column | Type | Modifiers
---------------+-----------------------------+-----------------------------------------------------
speech_id | integer | not null default nextval('speech_id_seq'::regclass)
speechtype_id | smallint | not null
title | character varying | not null default ''::character varying
speechdate | date | default now()
location | character varying | not null default ''::character varying
source | character varying | not null default ''::character varying
speechtext | text | not null
url | character varying | not null default ''::character varying
release_id | smallint |
created | timestamp without time zone |
modified | timestamp without time zone |
speech_tsv | tsvector |
key | boolean |
summary | text |
quote | text |
Indexes:
"speech_pk" PRIMARY KEY, btree (speech_id)
"speech__release_id" btree (release_id)
"speech__speech_tsv" gin (speech_tsv)
"speech__speechdate" btree (speechdate)
"speech__speechtype_id" btree (speechtype_id)
Foreign-key constraints:
"speech__release_id_fk" FOREIGN KEY (release_id) REFERENCES release(release_id) MATCH FULL ON DELETE RESTRICT DEFERRABLE INITIALLY DEFERRED
"speech__speechtype_id_fk" FOREIGN KEY (speechtype_id) REFERENCES speechtype(speechtype_id) MATCH FULL DEFERRABLE INITIALLY DEFERRED
Referenced by:
TABLE "factcheck_speech" CONSTRAINT "factcheck_speech_speech_id_fkey" FOREIGN KEY (speech_id) REFERENCES speech(speech_id) MATCH FULL ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED
TABLE "speech_candidate" CONSTRAINT "speech_candidate__speech_id_fk" FOREIGN KEY (speech_id) REFERENCES speech(speech_id) MATCH FULL ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED
TABLE "speech_category" CONSTRAINT "speech_category__speech_id_fk" FOREIGN KEY (speech_id) REFERENCES speech(speech_id) MATCH FULL ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED
TABLE "speech_tag" CONSTRAINT "speech_tag__speech_fk" FOREIGN KEY (speech_id) REFERENCES speech(speech_id) MATCH FULL ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED
TABLE "speechlocking" CONSTRAINT "speechlocking__fkey" FOREIGN KEY (speech_id) REFERENCES speech(speech_id) MATCH FULL ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED
Triggers:
speech_updated BEFORE INSERT OR UPDATE ON speech FOR EACH ROW EXECUTE PROCEDURE pvs_speech_updated()
update_speech_created BEFORE INSERT ON speech FOR EACH ROW EXECUTE PROCEDURE update_created_column()
update_speech_modified BEFORE UPDATE ON speech FOR EACH ROW EXECUTE PROCEDURE update_modified_column()
(speechtext
would be the column holding all the text to be searched, obviously)
Here is an example EXPLAIN (ANALYZE,BUFFERS)
query executed directly on the server (although these queries are actually executed within a Python application, so it's running a bit quicker here with no network latency, etc):
QUERY PLAN
-------------------------------------------------------------------------
Bitmap Heap Scan on speech (cost=294.85..7931.12 rows=6142 width=1058) (actual time=400.623..67768.222 rows=27267 loops=1)
Recheck Cond: (speech_tsv @@ plainto_tsquery('gun'::text))
Heap Blocks: exact=23582
Buffers: shared hit=2413 read=21424
-> Bitmap Index Scan on speech__speech_tsv (cost=0.00..293.31 rows=6142 width=0) (actual time=279.709..279.709 rows=30535 loops=1)
Index Cond: (speech_tsv @@ plainto_tsquery('gun'::text))
Buffers: shared hit=241 read=14
Planning time: 0.187 ms
Execution time: 67778.684 ms
(9 rows)
explain (analyze, buffers)
of the slow query, preferably with track_io_timing turned on.