Postgres query is sometimes taking a very long time

Question

I have implemented a FTS engine in my website using GIN tsvector and it works quite well, but there are a few times when it seems to take a very long time, for no specific reason. I am copying the output of the EXPLAIN ANALYE command below:

sitedb=# EXPLAIN ANALYZE SELECT id, title FROM post_1 WHERE search_vector @@   to_tsquery('quantum');
                                                      QUERY PLAN                                                           
-------------------------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on post_1  (cost=315.68..105654.80 rows=32443 width=106) (actual   time=76.963..17281.184 rows=31925 loops=1)
   Recheck Cond: (search_vector @@ to_tsquery('quantum'::text))
   Heap Blocks: exact=29259
   ->  Bitmap Index Scan on index1_idx  (cost=0.00..307.57 rows=32443 width=0) (actual time=60.208..60.209 rows=31925 loops=1)
     Index Cond: (search_vector @@ to_tsquery('quantum'::text))
 Planning Time: 47.648 ms
 Execution Time: 17308.511 ms
(7 rows)

I thought at some point that changing work_mem would help. I have set it up to 86MB and it is still the same.

The weird thing is that if I re-run the same command right after, it is much faster. See below:

sitedb=# EXPLAIN ANALYZE SELECT id, title FROM post_1 WHERE search_vector @@ to_tsquery('quantum');
                                                      QUERY PLAN                                                           
-------------------------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on post_1  (cost=315.68..105654.80 rows=32443 width=106) (actual time=44.542..495.525 rows=31925 loops=1)
   Recheck Cond: (search_vector @@ to_tsquery('quantum'::text))
   Heap Blocks: exact=29259
   ->  Bitmap Index Scan on index1_idx  (cost=0.00..307.57 rows=32443 width=0) (actual time=29.256..29.256 rows=31925 loops=1)
     Index Cond: (search_vector @@ to_tsquery('quantum'::text))
 Planning Time: 0.597 ms
 Execution Time: 502.296 ms
(7 rows)

Would anyone have an idea?

Thank you very much.

Given that the plans are identical but the planning and execution times are so different, my first guess is that this is happening at the operating system or even lower level. Perhaps your server swapped out the postgres process and spent a lot of time swapping it back in. Or maybe your drive went to power-save sleep. If your server is on a VM running on shared hardware, then there are lots of things that could cause such a "wake up" delay. — Mike Organek
– Mike Organek, Commented Aug 3, 2020 at 16:42
If the first time you run a query it's slow, then subsequent queries are fast, you probably had a cold cache. The data on disk had to first be loaded into memory cache where it is much faster to access. — Schwern
– Schwern, Commented Aug 3, 2020 at 18:01
If that is the problem, then you will need to search for options for whatever OS you are using. — Mike Organek
– Mike Organek, Commented Aug 3, 2020 at 18:04
Schwern, would you have an idea how to resolve this cache problem? — Pierre
– Pierre, Commented Aug 3, 2020 at 19:42

jjanes · Accepted Answer · 2020-08-04 18:04:17Z

1

It is probably a cold cache. E.g it had to read 29,259 pages and few of them were already in memory. The 2nd time you run it, they are in memory, so it is faster. You could help confirm this by doing EXPLAIN (ANALYZE, BUFFERS) after turning track_io_timing on.

You can increase effective_io_concurrency so that PostgreSQL will have multiple IO requests outstanding at once. How effective this is will depend on your IO hardware. It should be more effective on striped RAID or JBOD, for example.

If your cache was cold because you recently restarted, well, don't restart very often, or use pg_prewarm to warm up the cache when you do. If it can't stay in cache because your frequently-used data is too big for it to stay in memory, then get more RAM, or get faster disks (like SSD, of if they already are SSD then get better ones).

answered Aug 4, 2020 at 18:04

jjanes

44.9k5 gold badges39 silver badges48 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Postgres query is sometimes taking a very long time

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related