Re: Performance problem on 2 PG versions on same query

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