My crystal ball says the two queries end up using different query plans. You can verify by running
EXPLAIN on each. More useful details with
EXPLAIN (ANALYZE, BUFFERS).
The subquery gets the greatest
time_track more than 5 minutes before the one of the greatest
id overall in table
eco.tracks. Assuming a strong positive correlation between
time_track, humans get a rough idea of the work to be done here: it only concerns the (probably very few) latest rows within the last 5 minutes in the table. But Postgres has no idea at the time of planning the query.
(Aside: It may be a lingering logic bug to assume that serial IDs are in sync with timestamps in the column
time_track. Not enough information to say more.)
For the first (slow) query, Postgres has to prepare a plan that best copes with any value that might come out of the subquery. That may very well be a sequential scan, which turns out to be a bad choice for only the few latest rows.
For the second (fast) query, Postgres knows from the given value what to expect. It can start by identifying the few relevant rows with an index or bitmap index scan on an index on
(id) which undoubtably exists (again assuming that's the PK). The rest is peanuts.
There are many ways. Anything to get Postgres to pick the appropriate query plan ...
1. Two separate queries
Dead simple. Build the second query with the actual value returned from the first. The downside: two round trips to the db server collecting two times network latency and varying overhead.
2. Dynamic SQL
CREATE OR REPLACE FUNCTION f_latest_tracks(_intv interval = '300 seconds')
RETURNS SETOF eco.tracks AS
RETURN QUERY EXECUTE
'SELECT DISTINCT ON (track) *
WHERE id > $1
ORDER BY track, time_track DESC'
WHERE time_track < (SELECT time_track - _intv FROM eco.tracks ORDER BY id DESC LIMIT 1)
ORDER BY id DESC
$func$ LANGUAGE plpgsql;
SELECT * FROM f_latest_tracks();
SELECT * FROM f_latest_tracks('10 minutes');
Why? The manual about Executing Dynamic Commands:
The important difference is that
EXECUTE will re-plan the command on
each execution, generating a plan that is specific to the current
parameter values; whereas PL/pgSQL may otherwise create a generic plan
and cache it for re-use. In situations where the best plan depends
strongly on the parameter values, it can be helpful to use
positively ensure that a generic plan is not selected.
Downside: You need to create a server-side function introducing a dependency on table
eco.tracks. And you need to be comfortable with PL/pgSQL, of course.
Using a parameter default for conveniencse. See: