Re: Performance problem on 2 PG versions on same query - Mailing list pgsql-general

"Tomas Vondra" <tv@fuzzy.cz> writes:
> Dne 5 Listopad 2014, 17:31, Rémy-Christophe Schermesser napsal(a):
>> 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.

> Well, the first observation is that the queries produce different results:

Yeah.  Another reason to not believe that the databases contain identical
data is here:

>>            ->  Seq Scan on andalertsmatch am  (cost=0.00..71287.87
>> rows=1064987 width=52) (actual time=0.000..1680.077 rows=1064987 loops=1)

>>         ->  Index Scan using andalertsmatch_a_mid_idx on andalertsmatch am
>> (cost=0.00..180798.61 rows=1173762 width=52) (actual
>> time=0.015..875294.427 rows=1826118122 loops=1)

For some reason there's over 1000 times more rows in andalertsmatch in
the 9.1.14 installation.  I'm betting on a foulup somewhere in the data
dump/restore process.

            regards, tom lane