Queries on Postgresql very slow!

[FBELGHAIT]

Hi,

I have a large table that i imported from MsAccess, i tried a simple query on MsAccess and on Postgresql, and i noticed that postgres is very slow to load the table, and some times he loose the connection to the database completly

The query is like : select * from table, my table is 65000 record.

if some one have any tips on that please help me

Thanks a lot

 

[rycamor]

1. What operating system and what version of PostgreSQL are you running?

2. How many columns are in your table, and what kind of data is in the columns?

3. Do you have a primary key established? Are you indexing any columns?

4. How are you connecting to PostgreSQL? ("loose the connection to the database completly&quot -------------------------------------------

"Calculus is just the meaningless manipulation of higher symbols"
                          -unknown F student

 

[FBELGHAIT]

Hi,

i've postgres version 7.1.3 and is runing on AIX, the ODBC driver i'm using is version 7.1.9. In my table there is 44 fields all of them are of type VARCHAR,FLOAT8,DATESTAMP. also i have 3 indexes and one primary key.
i'm using windows 95. and i'm using pgadminII to open the database. when i try to open my table, or it takes about 1 minute to open it, or pgadmin become not responding and freese completly.

 

[rycamor]

ODBC is most likely your problem. It depends on several things that are beyond the control of Postgres:

1. The speed of your network
2. The abilities of the client machine
3. The speed and configuration of ODBC itself
4. The speed and configuration of the ODBC client application

Thus, even if PostgreSQL is tuned right on your AIX machine, you are trying to retrieve ALL rows into your client environment, using ODBC, which is not really the purpose of ODBC anyway. Unless your network is quite fast, this is not going to be as fast as Access running locally, because of the retrieval bottleneck.

If you are trying to retrieve ALL records, and then manipulate them on the client side, what is the point of having a server? Generally, you would want to just retrieve a few records at a time. Also, though, there might be something wrong with your network. The fact that the ODBC connection times out during the query might be an indication of that.

PostgreSQL on an AIX machine should be fairly fast. Have you tried doing these queries from the command line? Have you tried this query from other ODBC clients, such as Access? -------------------------------------------

"Calculus is just the meaningless manipulation of higher symbols"
                          -unknown F student