Postgres Pro
Downloads
Home   >   mailing lists

Thread: Poor performance - need advice

Poor performance - need advice

From
"Mark Alliban"
Date:
We have a table with 95,000 records, indexed on an int8 type (ClientNumber).
It is currently taking up to 10 seconds to retrieve one row based upon the
following statement:

SELECT * FROM ClientTable WHERE ClientNumber=1234566789;

The client numbers are random and there is currently no locking on the
database. I am running SCO OpenServer, PostgreSQL version 6.5.3. Is there
any way we can reduce the time taken by this query?

Re: Poor performance - need advice

From
"Mark Alliban"
Date:
>We have a table with 95,000 records, indexed on an int8 type
(ClientNumber).
>It is currently taking up to 10 seconds to retrieve one row based upon the
>following statement:
>
>SELECT * FROM ClientTable WHERE ClientNumber=1234566789;
>
>The client numbers are random and there is currently no locking on the
>database. I am running SCO OpenServer, PostgreSQL version 6.5.3. Is there
>any way we can reduce the time taken by this query?


I have tried an EXPLAIN of the above statement, and it tells me that
Postgres is doing a Seq Scan on the table to return the results, even though
it should be using the index. How can I make sure that it will always use
the index when I specify ClientNumber?

Re: [GENERAL] Poor performance - need advice

From
Ed Loehr
Date:
Mark Alliban wrote:
>
> We have a table with 95,000 records, indexed on an int8 type (ClientNumber).
> It is currently taking up to 10 seconds to retrieve one row based upon the
> following statement:
>
> SELECT * FROM ClientTable WHERE ClientNumber=1234566789;
>
> The client numbers are random and there is currently no locking on the
> database. I am running SCO OpenServer, PostgreSQL version 6.5.3. Is there
> any way we can reduce the time taken by this query?

Pardon me for asking what may be obvious, but are you running 'vacuum
analyze' regularly?  If not, that may fix the problem.

Cheers,
Ed Loehr

6.5.3 help

From
Chairudin Sentosa
Date:
Hi all,

I am installing 6.5.3 on SuSE 6.2,
after finish I can not find /usr/local/pgsql/data.

I remember when I installed 6.5.2 it was created
automatically during installation.

What happen with 6.5.3?

Regards,
Chai

Re: [GENERAL] 6.5.3 help (Please ignore, I was sleepy...)

From
Chairudin Sentosa
Date:
Please ignore the email.
I was sleepy.... :)

Regards,
Chai

Chairudin Sentosa wrote:
>
> Hi all,
>
> I am installing 6.5.3 on SuSE 6.2,
> after finish I can not find /usr/local/pgsql/data.
>
> I remember when I installed 6.5.2 it was created
> automatically during installation.
>
> What happen with 6.5.3?
>
> Regards,
> Chai
>
> ************