X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by postgresql.org (Postfix) with ESMTP id C238A9DCBBE for ; Tue, 21 Mar 2006 23:36:26 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 31926-05 for ; Tue, 21 Mar 2006 23:36:30 -0400 (AST) X-Greylist: delayed 12:20:00.55199 by SQLgrey- Received: from smtp1.netmagicians.com (smtp1.netmagicians.com [202.87.39.91]) by postgresql.org (Postfix) with ESMTP id E9D8A9DCBB3 for ; Tue, 21 Mar 2006 23:36:22 -0400 (AST) Received: from mailserver.tatashare.com (unknown [202.149.193.242]) by smtp1.netmagicians.com (Postfix) with ESMTP id DFD8E2103DF; Wed, 22 Mar 2006 09:06:20 +0530 (IST) Received: from aftabn463 (unknown [172.16.16.236]) by mailserver.tatashare.com (Postfix) with SMTP id F3CFA3E40AF; Wed, 22 Mar 2006 08:59:57 +0530 (IST) Reply-To: From: "Aftab Alam" To: "'Daniel Caune'" , "'Jeff Frost'" Cc: Subject: Re: Power cut and performance problem Date: Wed, 22 Mar 2006 09:08:08 +0530 Message-ID: <003001c64d62$09bd6a20$ec1010ac@aftabn463> MIME-Version: 1.0 Content-Type: multipart/mixed; boundary="----=_NextPart_000_0031_01C64D90.2375A620" X-Priority: 3 (Normal) X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook CWS, Build 9.0.2416 (9.0.2910.0) In-Reply-To: <1E293D3FF63A3740B10AD5AAD88535D201D30AFB@UBIMAIL1.ubisoft.org> X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2600.0000 X-MS-TNEF-Correlator: 0000000091057185765CF24DAD520D5993F699F124F57D00 Importance: Normal X-NetMagic-MailScanner-Information: Powered By NetMagic Mail Protection v1.0 X-NetMagic-MailScanner: Found to be clean X-NetMagic-MailScanner-From: aalam@tatashare.com X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, score=0.128 required=5 tests=[AWL=0.128] X-Spam-Score: 0.128 X-Spam-Level: X-Archive-Number: 200603/344 X-Sequence-Number: 24690 This is a multi-part message in MIME format. ------=_NextPart_000_0031_01C64D90.2375A620 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 7bit unsubscribe Regards, -----Original Message----- From: pgsql-sql-owner@postgresql.org [mailto:pgsql-sql-owner@postgresql.org]On Behalf Of Daniel Caune Sent: Tuesday, March 21, 2006 9:44 PM To: Jeff Frost Cc: pgsql-sql@postgresql.org Subject: Re: [SQL] Power cut and performance problem > > For example, the execution of the following query is fast as it used to > > be (gslog_event_id is the primary key on gslog_event): > > > > select max(gslog_event_id) from gslog_event; (=> Time: 0.773 ms) > > > > > > while the following query is really slow (several minutes): > > > > select min(gslog_event_id) from gslog_event; (index on the primary key > > is taken) > > > > > > I'm not a hardware expert at all, but I supposed that the whole > > performance would be degraded when a problem occurs with RAID disks. Am > > I wrong? Could it be something else? Are there some tools that check > > the state of a PostgreSQL database? > > You would be correct, a hardware problem should manifest itself on both > those > queries. What is the explain analyze output of those two queries? It's > possible you have a corrupt index on gslog_event. If that's the case, a > reindex would likely remedy the problem. Is postgres logging any errors? > The UNIX administrator confirms that this is not a RAID problem. I truncate my table. This is not the most efficient way, but it's okay because this is a data stage table. It seems that it fixes my performance problem. As you said, perhaps the problem was more related to index corruption. Truncating data and inserting new data recreate the index and therefore fix the problem. Thanks, -- Daniel ---------------------------(end of broadcast)--------------------------- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match ------=_NextPart_000_0031_01C64D90.2375A620 Content-Type: application/ms-tnef; name="winmail.dat" Content-Transfer-Encoding: base64 Content-Disposition: attachment; filename="winmail.dat" eJ8+IgkDAQaQCAAEAAAAAAABAAEAAQeQBgAIAAAA5AQAAAAAAADoAAEIgAcAGAAAAElQTS5NaWNy b3NvZnQgTWFpbC5Ob3RlADEIAQ2ABAACAAAAAgACAAEGgAMADgAAANYHAwAWAAkACAAAAAMACgEB A5AGAMALAAApAAAACwACAAEAAAALACMAAQAAAAMAJgAAAAAACwApAAEAAAADAC4AAAAAAAIBMQAB AAAALAEAAFBDREZFQjA5AAEAAgCbAAAAAAAAADihuxAF5RAaobsIACsqVsIAAG1zcHN0LmRsbAAA AAAATklUQfm/uAEAqgA32W4AAABDOlxEb2N1bWVudHMgYW5kIFNldHRpbmdzXEFkbWluaXN0cmF0 b3JcTG9jYWwgU2V0dGluZ3NcQXBwbGljYXRpb24gRGF0YVxNaWNyb3NvZnRcT3V0bG9va1xtYWls Ym94LnBzdAAYAAAAAAAAAJEFcYV2XPJNrVINWZP2mfGigAAAAAAAABgAAAAAAAAAkQVxhXZc8k2t Ug1Zk/aZ8cKAAAAQAAAARTZ0mG5jsUy9TyLc+gvGKywAAABSRTogW1NRTF0gUG93ZXIgY3V0IGFu ZCBwZXJmb3JtYW5jZSBwcm9ibGVtAAMANgAAAAAAHgBwAAEAAAAoAAAAW1NRTF0gUG93ZXIgY3V0 IGFuZCBwZXJmb3JtYW5jZSBwcm9ibGVtAAIBcQABAAAAFgAAAAHGTWIJN9EA9oQQ+k8XmE0iEhRs dvQAAAIBHQwBAAAAGQAAAFNNVFA6QUFMQU1AVEFUQVNIQVJFLkNPTQAAAAALAAEOAAAAAEAABg4A 3D4EYk3GAQIBCg4BAAAAGAAAAAAAAACRBXGFdlzyTa1SDVmT9pnxwoAAAAMAFA4BAAAACwAfDgEA AAACAQkQAQAAAMUFAADBBQAAqwkAAExaRnWjInsGAwAKAHJjcGcxMjXiMgNDdGV4BUEBAwH3/wqA AqQD5AcTAoAP8wBQBFY/CFUHshElDlEDAQIAY2jhCsBzZXQyBgAGwxEl9jMERhO3MBIsETMI7wn3 tjsYHw4wNREiDGBjAFAzCwkBZDM2FlALpiB1yQCAdWIE8mJlCqIKhJEKgFJlZwsRcywdql4gCuMf gR2qHaQtIVJPPQUQZwuAB0AF0AeQc2EsZ2UhUx2kRgNhOiAhDgBzcWwtI9Jvd4JuBJBAcG9zdAnB 1SPRLgWwZx2kWwDAAxCYdG86I78ky11PA6CEQmUT4GxmIE8o4M5EAHAIkAMgQ2Ec8B2VFwZgAjAj kFQKUHNkYRx5LAXQCsAT0CAyMQMrIAHQMDYgOTo0UDQgUE0dpFQmUCA6SgERICNRJNAdpENj7yOZ JK8qEx0gagWQKoEeYMEjkFtTUUxdLHAnENkSgWN1BUAAcGQjoASQ5QIQcgOBY2UjoANgAmBEZW0g Pwo+IDTARlMFsQ7AYW0LUGUrIHReaDMQDsAFkDIAaQIgIG5vKOA10gIQbBewA/BuiGcgcQpQcnkg BABdNxBhL0AyIAQgaQVAdT8UEAsxHcImQDRoHYAgKEMmgBewZ19ldiphX/5pMlA4ITXSMzAHcArA OACca2U4ADaBOqkpOjRnXTRoIBQQM3AwsCAAwHhdOp0pNxADYTzqOx+gKA49NMAHYiOQMC43NzIz P2BzKT3PPkl3aL8DEDMQNu839BggB0BsOABbOrEH4CgUEDsQciIBbf8LgDIAB5A9rz67C4A/r0C/ 9zqAC4ABAHg8sjvJHaQ8gWs0aDuSYTyAbkMPSPpJUidA4G5vMhEgE+Fk/ncKwDXyMnEyETIRRZAr IMpiMgFJPvB1cC8hCYD/NcFTATXSRNAG8B2VNMIyenp3CGBsMlA6YQEACcBhfwEAMlBE0AnwUdEz NTagYx8x8BQARMA40CuAUkFJRkRXMAQAa3MuHaRB3zOVUSNEwANgN5A/H6AIUfdW4TjROmFzA3AU IETgN5H/KXAUEFvBBxBFE1JhXLI1wH5vBvA7olMAHaQT0AWQa/80aDXSL0BTADMQNrFR4DGQ/y9D MUFXMFMAAaA4YF2ANGb9NGZZCGBWqAWhGCAwsCsg+1HpWEZzVRBW0gOBBpAHkP8FQDjQPwEo4AIg HaQG4DXQ/zRmNdBUITRmN8IIkFoQH6B+V1SCO5VSkQtiMiEHQHn2emEhMgBwMgE2s1QhNcCHVrBp 1mLFSXQnczRm5y8hAJAzYSB5Y+ET4DsQf1HRZKJT8GdBTLY6qWpRSW82wlMAboA1w2NikR7VYc80 ZhggTKRWtGxpPIBG4fsYIAeAZDgAO8QzU3IiBCD/LyZ1QDrQIdE3oABwTdUEkB8DYBQAYs0spTXh VU5J2lgyIGRH8QQAdEewJkDzMdECIGZpMsBe9DXBOCH/OCFRpFmDdnYdqlPAe5Ac8PtzMGERbXYR AaAzcGpQepD/fQg10gRgOHEBEQ3gCJACMJ9EwCsCU4I40HLBb2srAP9n9QWQKbBtEnz1UeBiQmDS /yKAgCZRUAVAFBAzgHx1ONF/fDA2IAQggAAdpDJ/dsNBfwQgb7IiYDtgKyCIURPgcP87plhkUkCH sQWwMxAYIAtgvw6wOTdw1XBlNnFqUVR/hP83goUzMjILgBQQACA3gicw/wfghTNk0UahDrA5RTXh TKT/MjJeAzKhRVGHgHYredwAcP9aAB7bIPYdpCk0IJ+YjyFT7igJ8DJQNrFiA2BXgHMxzz2QmO+b uCylSVAsIVFQfwOgR5EAkAIgBCAdgEciOOwuMDW0C1FuJzFZIUWQ/TgQZ1GgUmFvsQXAAQAAkP9d 0jm1H6ChwxPQXsBtEQORn0ykBPCioijgoINqb3tRYzeRF5F1bW5ywWJCdH55iFCk8W1gUaGhawDA dAsT0B2kfaegAAAAHgBCEAEAAABAAAAAPDFFMjkzRDNGRjYzQTM3NDBCMTBBRDVBQUQ4ODUzNUQy MDFEMzBBRkJAVUJJTUFJTDEudWJpc29mdC5vcmc+AAMACVkBAAAACwAAgAggBgAAAAAAwAAAAAAA AEYAAAAAA4UAAAAAAAADAAKACCAGAAAAAADAAAAAAAAARgAAAAAQhQAAAAAAAAMACIAIIAYAAAAA AMAAAAAAAABGAAAAAAGFAAAAAAAAAwBGgAggBgAAAAAAwAAAAAAAAEYAAAAAUoUAACdqAQAeAEeA CCAGAAAAAADAAAAAAAAARgAAAABUhQAAAQAAAAQAAAA5LjAACwBIgAggBgAAAAAAwAAAAAAAAEYA AAAABoUAAAAAAAALAEyACCAGAAAAAADAAAAAAAAARgAAAAAOhQAAAAAAAAMATYAIIAYAAAAAAMAA AAAAAABGAAAAABGFAAAAAAAAAwBPgAggBgAAAAAAwAAAAAAAAEYAAAAAGIUAAAAAAAAeAF2ACCAG AAAAAADAAAAAAAAARgAAAAA2hQAAAQAAAAEAAAAAAAAAHgBegAggBgAAAAAAwAAAAAAAAEYAAAAA N4UAAAEAAAABAAAAAAAAAB4AX4AIIAYAAAAAAMAAAAAAAABGAAAAADiFAAABAAAAAQAAAAAAAAAC AfgPAQAAABAAAACRBXGFdlzyTa1SDVmT9pnxAgH6DwEAAAAQAAAAkQVxhXZc8k2tUg1Zk/aZ8QIB +w8BAAAAmwAAAAAAAAA4obsQBeUQGqG7CAArKlbCAABtc3BzdC5kbGwAAAAAAE5JVEH5v7gBAKoA N9luAAAAQzpcRG9jdW1lbnRzIGFuZCBTZXR0aW5nc1xBZG1pbmlzdHJhdG9yXExvY2FsIFNldHRp bmdzXEFwcGxpY2F0aW9uIERhdGFcTWljcm9zb2Z0XE91dGxvb2tcbWFpbGJveC5wc3QAAAMA/g8F AAAAAwANNP03AAACAX8AAQAAADEAAAAwMDAwMDAwMDkxMDU3MTg1NzY1Q0YyNERBRDUyMEQ1OTkz RjY5OUYxMjRGNTdEMDAAAAAAAwAGEO4CProDAAcQrwUAAAMAEBAAAAAAAwAREAAAAAAeAAgQAQAA AGUAAABVTlNVQlNDUklCRVJFR0FSRFMsLS0tLS1PUklHSU5BTE1FU1NBR0UtLS0tLUZST006UEdT UUwtU1FMLU9XTkVSQFBPU1RHUkVTUUxPUkdNQUlMVE86UEdTUUwtU1FMLU9XTkVSAAAAANI8 ------=_NextPart_000_0031_01C64D90.2375A620--