Обсуждение: BUG #5492: Query performs slowly and sequence corrupted
The following bug has been logged online: Bug reference: 5492 Logged by: Piergiorgio Buongiovanni Email address: piergiorgio.buongiovanni@netspa.it PostgreSQL version: 8:4.3 Operating system: x86_64-redhat-linux-gnu Description: Query performs slowly and sequence corrupted Details: Our production platform is based on: "PostgreSQL 8.4.3 on x86_64-redhat-linux-gnu, compiled by GCC gcc (GCC) 4.1.2 20080704 (Red Hat 4.1.2-46), 64-bit" We had a problem with a sequence on the Subject table of the business schema approx one week ago. Today we have noted that a simple query SELECT * FROM business.Subject performs very slowly and it needs up to 400000ms to extract 86 rows. We remember we had the same problem when the related sequence was corrupted one week ago. So we have noted that the sequence is newly corrupted: the last inserted row in the Subject table has the value 100 and the sequence has a very high value. So we used the following command: ALTER SEQUENCE business.subject_isid_seq RESTART WITH 101 CACHE 10; to fix up the sequence, but this command hasn't worked as the START value is now 134920. I reused the previous command to re-set the sequence value to the right one, but I see that the START value is now 59100. I reused the previous command another time and the START value is now 30440. I think this is a bug. I have a lot of problems with this sequence. Could anybody please suggest me anything to solve this problem? Kind regards
On Mon, Jun 7, 2010 at 5:36 PM, Piergiorgio Buongiovanni <piergiorgio.buongiovanni@netspa.it> wrote: > I reused the previous command to re-set the sequence value to the right one, > but I see that the START value is now 59100. I reused the previous command > another time and the START value is now 30440. > > I think this is a bug. I have a lot of problems with this sequence. Sequences wouldn't directly affect retrieval times. But one way you could get both of these symptoms is by having an application which inserts many rows but aborts and rolls back the inserts without committing. Perhaps a large copy which is interrupted. That would fill the table with garbage dead records which could slow down retrieval depending on the access method and also increase the sequence value. You'll need to provide a lot more data before people would be able to help you. I suggest you post the results of "explain analyze select ..." as well as some details of what previous operations you've done on this database to pgsql-general@postgresql.org. pgsql-bugs is not really appropriate unless you're reporting a specific problem with postgresql in general, not an operational problem with your database. -- greg
On Mon, Jun 7, 2010 at 5:33 PM, Greg Stark <gsstark@mit.edu> wrote: > On Mon, Jun 7, 2010 at 5:36 PM, Piergiorgio Buongiovanni > <piergiorgio.buongiovanni@netspa.it> wrote: >> I reused the previous command to re-set the sequence value to the right one, >> but I see that the START value is now 59100. I reused the previous command >> another time and the START value is now 30440. >> >> I think this is a bug. I have a lot of problems with this sequence. > > Sequences wouldn't directly affect retrieval times. But one way you > could get both of these symptoms is by having an application which > inserts many rows but aborts and rolls back the inserts without > committing. Perhaps a large copy which is interrupted. That would fill > the table with garbage dead records which could slow down retrieval > depending on the access method and also increase the sequence value. If this is what happened, CLUSTER on the table might be enough to fix the problem. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise Postgres Company