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 > -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
