2012/5/8 Antonio Goméz Soto <antonio.gomez.soto@xxxxxxxxx>: > 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@xxxxxxxxxxxxxx) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
