I have a table, that in production, currently has a little over 3
million records in production. In development, the same table has
about 10 million records (we have cleaned production a few weeks
ago). One of my queries joins this table with another, and in
development, the particular condition uses an IndexScan on the "stamp"
column (the time this record was inserted) which makes it run fast.
In Production however (different machine, similar specs/postgresql
settings) the planner uses a seq scan on that table, causing the query
performance to be abysmal (sometimes over 10 seconds in production,
around 1 second in development). What can I do to tweak this/
troubleshoot it? I have another table with similar structure etc.
that has the same issue. Thanks!!!
Here is the query:
SELECT node,count(*) AS counts FROM u_counts c,res r WHERE
c.res_id=r.id AND stamp > (current_timestamp - interval '1 day') AND
r.rtype='u' AND r.location=1 GROUP BY node;
The tables have an index on u_counts.res_id, u_counts.stamp,
res.location, and res.rtype
Here is the production explain analyze:
HashAggregate (cost=472824.67..472824.77 rows=8 width=6) (actual
time=12482.856..12482.872 rows=9 loops=1)
-> Hash Join (cost=16.71..471847.28 rows=195479 width=6) (actual
time=1217.532..10618.930 rows=1035998 loops=1)
Hash Cond: (c.res_id = r.id)
-> Seq Scan on u_counts c (cost=0.00..466319.96
rows=948218 width=4) (actual time=1217.183..7343.507 rows=1035998
loops=1)
Filter: (stamp > (now() - '1 day'::interval))
-> Hash (cost=15.88..15.88 rows=67 width=10) (actual
time=0.299..0.299 rows=60 loops=1)
-> Seq Scan on res r (cost=0.00..15.88 rows=67
width=10) (actual time=0.027..0.195 rows=60 loops=1)
Filter: (((rtype)::text = 'u'::text) AND
(location = 1))
Total runtime: 12482.961 ms
Here is the development explain analyze:
HashAggregate (cost=72.91..73.02 rows=9 width=6) (actual
time=3108.793..3108.807 rows=9 loops=1)
-> Hash Join (cost=10.42..71.27 rows=327 width=6) (actual
time=0.608..2446.714 rows=392173 loops=1)
Hash Cond: (c.res_id = r.id)
-> Index Scan using u_counts_i2 on u_counts c
(cost=0.00..53.53 rows=1082 width=4) (actual time=0.277..1224.582
rows=392173 loops=1)
Index Cond: (stamp > (now() - '1 day'::interval))
-> Hash (cost=9.53..9.53 rows=71 width=10) (actual
time=0.310..0.310 rows=78 loops=1)
-> Seq Scan on res r (cost=0.00..9.53 rows=71
width=10) (actual time=0.010..0.189 rows=78 loops=1)
Filter: (((rtype)::text = 'u'::text) AND
(location = 1))
Total runtime: 3108.891 ms
---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster
