Thread: Database Performance problem
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.
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:
PostgreSQL version : 7.2.5On 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 query1. 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 secondsSounds 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
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
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: > 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. > > > > ---------------------------(end of broadcast)--------------------------- > TIP 4: Don't 'kill -9' the postmaster