Обсуждение: 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.
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: > 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