Database Performance problem

On Mon, 2003-01-20 at 05:11, 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

Sounds like you're using SQL Server, w/ Access as the front-end.

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)

Sooooo,

What version of Pg are you using?
What OS, and it's version?
What are the table definitions?
What does postgresql.conf look like? (Please eliminate comments.)
http://www.tuxedo.org/~esr/faqs/smart-questions.html

-- 
+------------------------------------------------------------+
| Ron Johnson, Jr.     mailto:ron.l.johnson@cox.net          |
| Jefferson, LA  USA   http://members.cox.net/ron.l.johnson  |
|                                                            |
| "Basically, I got on the plane with a bomb. Basically, I   |
|  tried to ignite it. Basically, yeah, I intended to damage |
|  the plane."                                               |
|    RICHARD REID, who tried to blow up American Airlines    |
|                  Flight 63                                 |
+------------------------------------------------------------+

Ron Johnson wrote:

On Mon, 2003-01-20 at 05:11, 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

Sounds like you're using SQL Server, w/ Access as the front-end.

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)

Sooooo,

What version of Pg are you using?
What OS, and it's version?
What are the table definitions?
What does postgresql.conf look like? (Please eliminate comments.)
http://www.tuxedo.org/~esr/faqs/smart-questions.html

PostgreSQL version : 7.2.5
ODBC-driver version : 7.2.1
Server version (I tried on 2 different servers):
Debian Linux 2.4.19 and Debian Linux 2.4.18

Content of Postgresql.conf :
debug_level = 0
log_connections = on
log_pid = on
log_timestamp = on
syslog = 2
silent_mode = off
syslog_facility = LOCAL0
trace_notify = off
max_connections = 64
shared_buffers = 128
tcpip_socket = 1

T-persons :
CREATE TABLE "T-persons" (
"Id-person" int8 DEFAULT nextval('"T-persons_Id-persons_seq"'::text)
NOT NULL,
"ID-address" int8,
------ Here follow a lot more fields -------
CONSTRAINT "T-persons_pkey" PRIMARY KEY ("Id-person")
) WITH OIDS;
GRANT ALL ON TABLE "T-persons" TO platteau;

T-address
CREATE TABLE "T-address" (
"ID-address" int8 DEFAULT nextval('"T-address_ID-address_seq"'::text)
NOT NULL,
"Street" varchar(50),
"Number" varchar(50),
-------- Here follow a few more fields ----------
CONSTRAINT "T-address_pkey" PRIMARY KEY ("ID-address")
) WITH OIDS;
GRANT ALL ON TABLE "T-address" TO platteau;

Thanks.
Joepie.

joepie Platteau <joepie.Platteau@kulak.ac.be> writes:

shared_buffers = 128

Try boosting that to 1000 or so. The default sort_mem is on the miserly
side as well; you could try 5000 or 10000 for that.

But probably a more important bit of advice is to run ANALYZE. I think
most likely Postgres is choosing a bad query plan because it doesn't
have any statistics about the table contents.

If ANALYZE doesn't help, let's see the output of EXPLAIN ANALYZE for the
problem query.

regards, tom lane

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

Hello there!

Thank you for all the answers. The problem is solved.
When I edited the postgresql.conf (which still had the standard
configuration), and changed the following :

shared_buffers = 1024
sort_mem = 10000

I also restarted postgresql and everything that took approx. 55 seconds now took only 3 seconds!

Thanks.
Joepie.

joepie Platteau wrote: