From: Aftab Alam <aalam@tatashare.com>
To: 'Daniel Caune' <daniel.caune@ubisoft.com>
To: 'Jeff Frost' <jeff@frostconsultingllc.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: Power cut and performance problem
Date: Wed, 22 Mar 2006 09:08:08 +0530
Message-ID: <003001c64d62$09bd6a20$ec1010ac@aftabn463> (raw)
In-Reply-To: <1E293D3FF63A3740B10AD5AAD88535D201D30AFB@UBIMAIL1.ubisoft.org>
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
Attachments:
winmail.dat (application/ms-tnef, 3.1K) download
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: aalam@tatashare.com, daniel.caune@ubisoft.com, jeff@frostconsultingllc.com
Subject: Re: Power cut and performance problem
In-Reply-To: <003001c64d62$09bd6a20$ec1010ac@aftabn463>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox