Re: Database Performance problem

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.