Re: Database Performance problem - Mailing list pgsql-general

From Justin Clift
Subject Re: Database Performance problem
Date
Msg-id 3E2C3771.9020605@postgresql.org
Whole thread Raw
In response to Database Performance problem  (joepie Platteau <joepie.Platteau@kulak.ac.be>)
List pgsql-general
Hi Joepie,

Just a few standard questions:

  + Which version of PostgreSQL are you using?
  + Which Operating System is it running on?
  + Did you run ANALYZE on your database (regardless of running VACUUM)?

and

  + Have you tuned the memory configuration of PostgreSQL at all, or is
it still not configured (i.e. left at the defaults)?

As an additional thought, there is a SQL command called EXPLAIN that you
will probably want to look up in the PostgreSQL manuals.  It helps you
figure out what a SQL query is doing behind the scenes, an people
experienced with PostgreSQL can generally take one look at it and
suggest ways to speed things up.

:-)

Regards and best wishes,

Justin Clift


joepie Platteau wrote:
> A question about the performance of postgresql...
>
> Situation :
> I created a Postgresql Database with 2 tables : T-Persons and T-Address
> In both tables are almost 70000 records.
> I also created the same Database in MsAccess (and put it on the same
> server) with the same tables (and data)
>
> I connect these tables from my MsAccess2000 - frontend and run the
> following query :
>
> SELECT [T-persons].[Id-person], [T-address].Street, [T-address].Number
> FROM [T-persons] INNER JOIN [T-addres] ON [T-persons].[ID-address] =
> [T-address].[ID-address];
>
> When I run this query
> 1. with the tables of Postgresql, it takes approx. 58 seconds.
> 2. with the tables of MsAccess, it takes approx. 6 seconds.
> 3. directly (without connection to Access) on the server, it takes 5
> seconds
>
> I already tried optimising the Postgresql with Vacuum and I also created
> an index on the field "ID-address" in T-persons.
> All this doesn't help.  I also have the latest version of ODBC.
>
> Are there any other possibilities to make this run faster?
> I tried to make the query as Pass-through query, but this also doesn't
> help a lot...(52 seconds)
>
> Thanks,
> Joepie.


--
"My grandfather once told me that there are two kinds of people: those
who work and those who take the credit. He told me to try to be in the
first group; there was less competition there."
- Indira Gandhi


pgsql-general by date:

Previous
From: Manfred Koizar
Date:
Subject: Re: Altering a table - positioning new columns
Next
From: Tim Frank
Date:
Subject: Re: Altering a table - positioning new columns