Should the same query run at different times?

Question

PostgreSQL

Hello! Faced such a problem. There are 2 bases which are located on one server. One release, and the second for tests. The release database stores data for 10 days and there is N amount of data. The test database stores data for 5 days and there is approximately N/2 amount of data. The test database has absolutely identical data for the last 5 days of the release database.

What is the actual question. I want to select data for a single day (for example, today), but when I make a request to the release database, the data is returned to me in 8 seconds, And when I make a request to the test database, the data is returned in 5-6 seconds. Is it correct? I thought that the request should cut off all the data for the previous days in one and the other database and the answer should come in approximately the same time.

Request example:

SELECT
  (SELECT concat(p.latitude::TEXT, ' ', p.longitude::TEXT)
   FROM positions p
   WHERE p.route_list_id = rl.id
   ORDER BY p.time DESC
   LIMIT 1) as POSITION,
  *
FROM route_list_date rld
       INNER JOIN route_list rl on rld.id = rl.route_list_date_id
WHERE rld.date = '2020-02-17'

Answer 1

Of course, your test database may have completely different data in the cache.
By the way, some time is very long: either you have a problem with indexes or with a query, I suspect that the latter, looking at the select.

Answer 2

IMHO:
1. In order for the query to correctly cut off unnecessary data, you must have an actual index on the "date" field in the "route_list_date" table.
2. The actual index on the "id" field in the "route_list_date" tables and on the "route_list_date_id" field in the "route_list" table did not interfere (so that the inner join went faster.)

Answer 3

The query execution time can vary greatly depending on the load and state of the server.
If your database size is different, then the query execution time will also be different. This is fine. That's the way it should be.
Here it is best to look at the query execution plan and its resulting cost.
Add explain(analyze, buffers, verbose) to your query and look at the database output.