Hi, Dne 5 Listopad 2014, 17:31, Rémy-Christophe Schermesser napsal(a): > Hi, > > We have 2 instances of PG, one in 9.1.1 and 9.1.14. They have the same > data, schema, PG configuration, and are almost identical machines, same > number of cores and memory, but different cloud provider. The data was > transferred with a pg_dump/pg_restore. We ran VACUUM ANALYSE, ANALYSE, and > REINDEX on both machines. What do you mean by "cloud provider"? Are you installing and configuring the machine on your own, or is this set-up by the provider? BTW you should do basic benchmarking first - numbers reported by the providers are just random numbers until you verify them. > One query take ~11 seconds on 9.1.1 and ~25 minutes on 9.1.14. Well, the first observation is that the queries produce different results: Limit (cost=100414.92..107502.31 rows=1000 width=279) (actual time=6200.302..11650.567 rows=1000 loops=1) Limit (cost=20.64..73294.62 rows=1000 width=279) (actual time=1419311.904..1419400.785 rows=3 loops=1) So while on 9.1.1 it produces 1000 rows very quickly, on 9.1.14 it only ever finds 3 rows (so the query needs to scan all the data, and the abort early does not trigger). There are other differences, though. For example on 9.1.1 the nested loop returns ~8k rows: Nested Loop (cost=88.78..7785.80 rows=2655 width=279) (actual time=190.009..9470.460 rows=7951 loops=1) while on 9.1.14 it produces ~120k rows: Nested Loop (cost=20.64..8045.28 rows=2694 width=279) (actual time=13.230..555.366 rows=121063 loops=1) This may be one of the reasons why the database decided to use different join method. Are there any differences in settings between the two machines (e.g. work_mem)? regards Tomas -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
