I have observed a weird situation that over time the performance of a query (a combination of queries explained below) degrades, meaning at the start of testing (for a few minutes) the time of the query is 2ms then next day it got to 15ms then day after 30ms.
By query I refer here to a combination of either:
- insert a row into table 2, select a row from table 2, select a row in table 3, update a row in table 3, commit
- insert a row into table 1, select a row in table 3, update a row in table 3, commit
I wonder what might be the reason of that or which settings from the configuration file should I consider setting and how? I observed the problem on Ubuntu machine where database was set and the primary keys were not added. On the other hand on Win which I develop on it was not observed (it was running constantly on average 3ms per query for 7 days).
I noticed that in the new database (on Ubuntu) there were no primary keys on any table, as oppose to the one I develop on. Could the lack of primary keys have the negative impact on this sort of query?
I thought I will ask this question in the mean time as I am moving my whole db from my development machine to the test one.
On development I used PostgreSQL 8.4 (CPU: Intel i7 740QM, RAM: 6GB), on test there is PostgreSQL 9.1 (CPU: Intel i3-2100, RAM: 3.8GB).
autovacuum related parameters:
#autovacuum = on #log_autovacuum_min_duration = -1 #autovacuum_max_workers = 3 #autovacuum_naptime = 1min #autovacuum_vacuum_threshold = 50 #autovacuum_analyze_threshold = 50 #autovacuum_vacuum_scale_factor = 0.2 #autovacuum_analyze_scale_factor = 0.1 #autovacuum_freeze_max_age = 200000000 #autovacuum_vacuum_cost_delay = 20ms #autovacuum_vacuum_cost_limit = -1
It appears that the problem occurs on the development machine as well, but I remember it running fine before. Never the less I did some more testing and did run EXPLAIN ANALYZE on the query, which I takes the most time, and it is an update (I also seen selects take a while on the table as well) presented below:
EXPLAIN ANALYZE UPDATE ais_track SET latest_dynamic = '2012-09-10 22:22:22.222' WHERE mmsi = 123456789 AND ais_system = 1;
The below results are on Win as restore still is in progress on the Ubuntu, and I got this at first:
Index Scan using pk_track on ais_track (cost=0.00..4.46 rows=1 width=36) (actual time=1.090..2.460 rows=1 loops=1) Index Cond: ((mmsi = 123456789) AND (ais_system = 1)) Total runtime: 8.681 ms
Then on 2nd repeat and further repeats of the same update query for a few times I get something of this form:
Index Scan using pk_track on ais_track (cost=0.00..4.46 rows=1 width=36) (actual time=0.699..1.797 rows=1 loops=1) Index Cond: ((mmsi = 123456789) AND (ais_system = 1)) Total runtime: 1.850 ms
After a hundred repeats or so it got to over 2ms.
EXPLAIN ANALYZE for Select:
EXPLAIN ANALYZE SELECT * FROM ais_track WHERE mmsi = 123456789 AND ais_system = 1
Index Scan using pk_track on ais_track (cost=0.00..4.46 rows=1 width=38) (actual time=1.283..2.522 rows=1 loops=1) Index Cond: ((mmsi = 123456789) AND (ais_system = 1)) Total runtime: 2.560 ms
After a hundred or so runs:
Index Scan using pk_track on ais_track (cost=0.00..4.46 rows=1 width=38) (actual time=0.027..1.357 rows=1 loops=1) Index Cond: ((mmsi = 123456789) AND (ais_system = 1)) Total runtime: 1.382 ms
The table used in query:
CREATE TABLE ais_track ( ais_system integer NOT NULL, mmsi integer NOT NULL, ext_id integer, latest_dynamic timestamp without time zone, latest_static timestamp without time zone, "name" character varying, CONSTRAINT pk_track PRIMARY KEY (mmsi, ais_system) )
And two indexes:
CREATE INDEX ais_track_mmsi ON ais_track USING btree (mmsi); CREATE INDEX ais_track_sys ON ais_track USING btree (ais_system);
NOTE: The table size is 11000 and it doesn't change.