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.
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.
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';
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.
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.
Tomas