Patrice Beliveau wrote: > I have a database in a production server (8.1.9) with to schema > containing the sames table same index, same every thing, but with > different data. When I execute a query in one schema, it take much more > time to execute then the other schema. [snip] > I'm wondering where to start searching to fix this problem
> Production server schema 1 query plan: > Nested Loop (cost=569.23..634.43 rows=1 width=121) (actual > time=1032.811..1032.811 rows=0 loops=1) [snip] > Total runtime: 1034.204 ms > Production server schema 2 query plan: > Nested Loop (cost=133.42..793.12 rows=1 width=123) (actual > time=0.130..0.130 rows=0 loops=1) [snip] > Total runtime: 0.305 ms Well there's something strange - the estimated costs are fairly similar (643.43 vs 793.12) but the times are clearly very different (1034 vs 0.3ms) The suspicious line from the first plan is: > -> Seq Scan on mrp m (cost=0.00..119.92 rows=5892 > width=39) (actual time=0.343..939.462 rows=5892 loops=1) This is taking up almost all the time in the query and yet only seems to be scanning 5892 rows. Run a vacuum verbose against table "mrp" and see if it's got a lot of dead rows. If it has, run VACUUM FULL and REINDEX against it and see if that solves your problem. I'm guessing you have / had a long-running transaction interfering with vacuum on this table, or perhaps a bulk update/delete? -- Richard Huxton Archonet Ltd -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
