Re: 2 machines, same database, same query, 10 times slower?

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

> 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
>