Folks:
I have a live and a test database for an in-production system running on
7.2.4.
The test database is a copy of the live one. They are running on the same
copy of Postgres, on the same server. I use the test database to test
changes before I apply them to the production system. Periodically (like
today) I reload the test system from the backup of the live one.
I'm now having a problem with a huge complex query which runs very slowly on
the test database, but very quickly on the live database! The problems
seems to be that the test database seems to think that a nested loop is an
appropriate strategy for a table of 140,000 records, while the live one
realizes that it's not.
What really has me scratching my head is that the test database is an exact
copy of the live database, just a few hours older. And the live database has
never displayed this performance problem, whereas the test database has had
it for 2-3 weeks.
Both EXPLAIN ANALYZEs are attached.
--
-Josh Berkus
Aglio Database Solutions
San Francisco