Full text search query is slow on first query

Question

I have an airports table which contains a list of nearly 4k airports. The table has a searchable column which is a ts_vector column and an index airports_searchable_index:

searchable tsvector NULL

CREATE INDEX airports_searchable_index ON airports USING gin (searchable)

Given I have an indexed document in the searchable column and I attempt to run a query against that column, I get very quick responses on my dev machine (around 3ms for the query) but around 650ms on production (using the exact same data!). The weird part is that my production machine is much stronger than my local dev machine. A query for example:

select * from "airports" where searchable @@ to_tsquery('public.hebrew', 'ltn:*') order by "popularity" desc limit 100

I've opened PGAdmin and tried doing some tests. What I saw that for the first time I run the query above in a new "Query Tool Panel", it takes anywhere between 650-800ms to execute. However, on the second run, it takes 30-60ms to run even if I change the query term. I had concluded from that, that Postgres is possible opening the document in memory for each connection and run the query against that. Since I'm using PHP to talk with my backend, every request is going to open it's own connection to the DB, hence causing Postgres to constantly re-opening the document.

Could it be a misconfiguration on my production server?

Here is an explain query (for production server):

Limit  (cost=24.03..24.04 rows=1 width=8) (actual time=0.048..0.048 rows=1 loops=1)
  Output: id, popularity
  Buffers: shared hit=4
  ->  Sort  (cost=24.03..24.04 rows=1 width=8) (actual time=0.047..0.047 rows=1 loops=1)
        Output: id, popularity
        Sort Key: airports.popularity DESC
        Sort Method: quicksort  Memory: 25kB
        Buffers: shared hit=4
        ->  Bitmap Heap Scan on lametayel.airports  (cost=20.01..24.02 rows=1 width=8) (actual time=0.040..0.040 rows=1 loops=1)
              Output: id, popularity
              Recheck Cond: (airports.searchable @@ '''ltn'':*'::tsquery)
              Heap Blocks: exact=1
              Buffers: shared hit=4
              ->  Bitmap Index Scan on airports_searchable_index  (cost=0.00..20.01 rows=1 width=0) (actual time=0.036..0.036 rows=1 loops=1)
                    Index Cond: (airports.searchable @@ '''ltn'':*'::tsquery)
                    Buffers: shared hit=3
Planning time: 0.304 ms
Execution time: 0.078 ms

Here is an explain query (for development server):

Limit  (cost=28.03..28.04 rows=1 width=8) (actual time=0.065..0.067 rows=1 loops=1)
  Output: id, popularity
  Buffers: shared hit=5
  ->  Sort  (cost=28.03..28.04 rows=1 width=8) (actual time=0.064..0.065 rows=1 loops=1)
        Output: id, popularity
        Sort Key: airports.popularity DESC
        Sort Method: quicksort  Memory: 25kB
        Buffers: shared hit=5
        ->  Bitmap Heap Scan on lametayel.airports  (cost=24.01..28.02 rows=1 width=8) (actual time=0.046..0.047 rows=1 loops=1)
              Output: id, popularity
              Recheck Cond: (airports.searchable @@ '''ltn'':*'::tsquery)
              Heap Blocks: exact=1
              Buffers: shared hit=5
              ->  Bitmap Index Scan on airports_searchable_index  (cost=0.00..24.01 rows=1 width=0) (actual time=0.038..0.038 rows=1 loops=1)
                    Index Cond: (airports.searchable @@ '''ltn'':*'::tsquery)
                    Buffers: shared hit=4
Planning time: 0.534 ms
Execution time: 0.122 ms

Full text search query is slow on first query

0 Answers