Search code examples
databasepostgresqlcachingdatabase-performancestado

Clustered DB - Stado - Slow first query

Using PostgreSQL in a clustered database (stado) on two nodes. I managed to configure stado coordinator and nodes agents successfully but when I try running a heavy query, the first time it takes too long to show results then after that it was fast.

When I restart the server it goes slow again. It's like stado does some caching or something. I thought the problem was because of stado initialization and thus configured agents but still the problem exists! Any ideas?

EDIT

Query:

SELECT id,position,timestamp
FROM   table t1
WHERE  id <> 0
AND    ST_Intersects(ST_Buffer_Meters(ST_SetSRID(
                     ST_MakePoint(61.4019, 15.218205), 4326), 1160006), position)
AND    timestamp BETWEEN '2013-10-01' AND '2014-01-01';

Explain:

ٍٍStep 0
_______
Target: CREATE UNLOGGED TABLE "TMPTT7_1" ( "XCOL1" INT) WITHOUT OIDS
SELECT: SELECT count(*) AS "XCOL1" FROM "t1" WHERE "t1"."timestamp" BETWEEN '2013-10-01' AND '2014-01-01' AND ("t1"."id"<>0) AND ST_Intersects(ST_Buffer_Meters(ST_SetSRID(
                         ST_MakePoint(61.4019, 15.218205), 4326), 1160006), "t1"."position")

Step: 1
_______
Select: SELECT SUM("XCOL1") AS "EXPRESSION6" FROM "TMPTT7_1"
Drop:
TMPTT7_1
Solution

  • Two reasons.

    Caching, obviously. When a query is executed the first time with cold cache, obviously the cache is populated. That goes for system cache as well as database cache, both work together, at least in standard Postgres. Can make a huge difference.

    Query plan caching, possibly. To a much lesser degree. If you run the same query in a single session repeatedly, plans for PL/pgSQL functions for instance are cached.

    Depending on your type of connection to the database, there may also be network latency, which may be higher for the first call.