2012/5/8 Antonio Goméz Soto <antonio.gomez.soto@gmail.com>:
> Hi Tomas,
>
> thanks for responding.
>
> Op 08-05-12 17:34, Tomas Vondra schreef:
>> Hi,
>>
>> On 8 Květen 2012, 16:48, Antonio Goméz Soto wrote:
>>> Hi,
>>>
>>> I am running PostgreSQL 8.1, on CentOS 5. I have two machines, same
>>> hardware, with the same database layout,
>>> they have different data, and the same query run 10 times as slow on one
>>> machine compared to the other.
>>
>> First of all, to analyze runtime differences it's important to provide
>> EXPLAIN ANALYZE output, not just EXPLAIN. Re-run the queries and use
>> explain.depesz.com to post the output.
>>
>
> Allright, thanks, didn't know that. Reran the queries, and they are posted here:
>
> The slow one: http://explain.depesz.com/s/2Si
>
> The fast one: http://explain.depesz.com/s/c9m3
>
probably cdr table on "slow machine" needs VACUUM FULL.
Regards
Pavel
>> Second, what do you mean 'different data'? If there is different amount of
>> data, it may be perfectly expected that the query runs much slower on the
>> machine with more data. For example the plans contain this:
>>
>> A: Seq Scan on cdr (cost=0.00..77039.87 rows=1486187 width=159)
>> B: Seq Scan on cdr (cost=0.00..408379.70 rows=781370 width=161)
>>
>> That suggests that the second database contains about 1/2 the rows.
>>
>
> That is true.
>
>> The seq scan nodes reveal another interesting fact - while the expected
>> row count is about 50% in the second plan, the estimated cost is about 5x
>> higher (both compared to the first plan).
>>
>> The important thing here is that most of the cost estimate comes from the
>> number of pages, therefore I suppose the cdr occupies about 5x the space
>> in the second case, although it's much more 'sparse'.
>>
>> Do this on both machines to verify that
>>
>> SELECT relpages, reltuples FROM pg_class WHERE relname = 'cdr';
>
> Slow machine:
>
> relpages | reltuples
> ----------+-----------
> 400566 | 982321
>
> Fast machine:
>
> relpages | reltuples
> ----------+-------------
> 62076 | 1.48375e+06
>
>
>>
>> That might happen for example by deleting a lot of rows recently (without
>> running VACUUM FULL after) or by not running autovacuum at all. Which is
>> quite likely, because it was introduced in 8.1 and was off by default.
>>
>
> Autovacuum is running on both machines and does not report errors. But
> I did not run a vacuum full. There currently are users on the machine,
> so I can try that later tonight.
>
>> BTW if you care about performance, you should upgrade to a more recent
>> version (preferably 9.x) because 8.1 is not supported for several years
>> IIRC and there were many improvements since then.
>>
>
> I would like to, but I am bound to distribution-supplied software versions.
>
> Thanks a lot for helping,
> Antonio
>
>> Tomas
>>
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general