Slightly noob-ish question/problem: I have a query that behaves differently in two "identical" setups - when run against second server, it is 100x slower.
Setup is AWS RDS Postgres 12 - two identical instances (as far as what is transparent to customers in cloud, no idea about the actual underlying hardware is, obviously): same instance types, same disk size, same IOPS, same network. Same Parameter Group on both instances; both instances rebooted and have params in-sync, according to AWS Console.
"Same"-ish data is in both servers - the second one was created from the snapshot of the first, and then had some additional data added/removed, but the tables size (pg_database_size, pg_total_relation_size) and select count (*) on relevant tables both give close results (think MBs/1000s of rows diff, and actually the original "faster" server is "bigger". Tables have millions of records)
VACUUM ANALYZE run on all tables. VACUUM FULL also ran on "slower" server, just-in-case.
No index bloat detected; indexes are size of KBs and MBs.
EXPLAIN ANALYZE generates nearly identical plans, and estimated cost and rows count is actually larger in "faster" server.
"Fast" server:
Planning time: 5.542ms
Execution Time: 32.542ms
"Slow" server:
Planning Time: 3.655ms
Execution Time: 3606.235ms
I'm quickly running out of ideas here. What else could be taken into account? I'm thinking maybe the "quality" of data added in second server is different (not under my control)? Or "identical hardware" is not really identical. The "slower" server does run in a different zone, but it would be weird if it was some temp issue since this is going on for a couple of days now, with same results.
Maybe the query is showing its ugly side and is actually really slower than what the "fast" instance shows? And it showed itself with "new" data added (though, why? Haven't checked each row but they look the same as any other, its just data generated by a load-test app). I don't really control the query that much, my main concern right now is to eliminate any hardware/setup issues (but again, AWS is paid to do this, so not sure where the issue is)
As mentioned, parameters are the same - impossible to do otherwise in AWS since both instances use the same Parameter Group, and no one touches these machines manually beside me so to manually override something. Oh and also, no traffic incoming to either instance at all.
EXPLAIN (ANALYZE, BUFFERS), but since you chose not to post them for some reason, you'll have to figure it out by yourself.