I'm trying to create a backend postgres database in order to provide data for a frontend Access database.
unfortunately, I can't remove the Access database and replace it by the postgres database.
what I'd like to do is to run access queries to postgres views
but I have performances problems :
Despite the postgres tables are very huge ( from 300000 rows to 10 million rows,more than 50 colums, and I can't alter them) I thought the postgres server would create the views quickly but on the contrary, it works very slowly, it takes more than one hour to fullfill a view !
I tried different configurations of the postgres ODBC driver, but nothing changes...
what can I do to improve the performances ?
or is postgres unable to hang on with huge tables and views ?
that would surprise me...
First off, it sounds like your database needs to be better-normalized ... but that's not possible in this case if you can't alter the tables -or- the views .... This is a tough one to answer without seeing your data or queries, but I know pg can handle databases that size without much problem if they're properly optimized.
My first guess off the top of my head is that your DB isn't properly indexed.