From: | "Tomas Vondra" <tv(at)fuzzy(dot)cz> |
---|---|
To: | "Tom Lane" <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
Cc: | "Tomas Vondra" <tv(at)fuzzy(dot)cz>, "Rémy-Christophe Schermesser" <rcs(at)netcosports(dot)com>, pgsql-general(at)postgresql(dot)org, "Bertrand Paquet" <bertrand(at)netcosports(dot)com> |
Subject: | Re: Performance problem on 2 PG versions on same query |
Date: | 2014-11-05 17:17:14 |
Message-ID: | be4f450aa81ab87a483a289606413530.squirrel@2.emaily.eu |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
Dne 5 Listopad 2014, 18:10, Tom Lane napsal(a):
> "Tomas Vondra" <tv(at)fuzzy(dot)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.
I'd bet that's mostly due to rescans in the merge join ...
Tomas
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2014-11-05 17:30:04 | Re: Performance problem on 2 PG versions on same query |
Previous Message | Tom Lane | 2014-11-05 17:10:11 | Re: Performance problem on 2 PG versions on same query |