PostgresQl why is the second query faster than the first one?

Question

PostgreSQL

I'm doing a full-text search on postgres, but for some reason it works very slowly, but especially the first query. For example, the word president occurs in 7,000 lines out of 120k. The first request takes about 6 seconds, and the second one takes 400ms. I have tsvector stored in a single column and create a gin index on it

Answer 1

To increase the speed of queries, it would be necessary to index the tables, there will be a noticeable difference, but as for the time difference, in my opinion, this is just caching, that is, when it is requested again, it simply displays the last result and also looks for a difference between the current state of the table and what is in memory

Answer 2

See explain (analyze, buffers), highly desirable with track_io_timing enabled. Almost certainly you are reading the first request from the disk, the second - already from shared_buffers. Or from the page cache of the operating system.

Answer 3

What would be fast:
1) take the required amount of data, for example, 50-100 first, instead of pumping everything out.
2) Use specialized tools (sphinxsearch/elasticsearch).

Answer 4

because postgre caches the data it read to execute the request.