On Friday 05 December 2003 16:51, Steve Wampler wrote: > I need some help tracking down a sudden, massive slowdown > in inserts in one of our databases. > > PG: 7.2.3 (RedHat 8.0) > > Background. We currently run nearly identical systems > at two sites: Site A is a 'lab' site used for development, > Site B is a production site. > > The databases in question have identical structure: > > A simple table with 4 columns with a trigger function > on inserts (which checks to see if the entry already > exists, and if so, changes the insert into an update...) > A simple view with 4 columns into the above table. > > All access is through jdbc (JDK 1.3.1, jdbc 7.1-1.3), > postgresql.conf's are identical. > > The two sites were performing at comparable speeds until > a few days ago, when we deleted several million records > from each database and then did a vacuum full; analyze > on both. Now inserts at Site B are several orders of > magnitude slower than at Site A. The odd thing is that > Site B's DB now has only 60,000 records while Site A's is > up around 3 million. Inserts at A average 63ms, inserts > at B are now up at 4.5 seconds! > > EXPLAIN doesn't show any difference between the two. > > Can someone suggest ways to track this down? I don't know > much about postgresql internals/configuration. >
What does explain analyze show for the insert query? Are there FK and/or Indexes involved here? Did you you reindex? A vacuum verbose could give you a good indication if you need to reindex, compare the # of pages in the index with the # in the table. Robert Treat -- Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL ---------------------------(end of broadcast)--------------------------- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match