Обсуждение: Re: Slow Performance in PostgreSQL

pgsql-odbc doesn't seem a right place to post.
I forwarded my reply to pgsql-hackers also.

Michael Rudolph wrote:
> 
> Hi,
> 
> I have a performance-problem with my Postgresql-database: up to now I
> had centura sqlbase but for some reasons I have to change to Postgres.
> After copying the data of an existing database I made some tests for
> performance which where very disappointing. There is one table, let's
> say table1, with about 11.0000 rows. On doing a "select distinct var1
> from table1" via ODBC-Driver, I had to wait about 15 sec for the result.
> The same select on centura sqlbase lasted about 3 sec. Ok, I thought, it
> might be the ODBC-Driver with that bad performance and I did that query
> directly with psql. Not much better, it lasted around 10 sec.
> 

How many rows are returned by the query in reality ?
Does the table *table1* have an index on the column *var1*
in your centura sqlbase ?

> This isn't what I can give to my users because in my application I have
> to do a lot of queries of that kind. The users would have a lot of time
> to hang around, waiting for the application, and thinking, how to kill
> me ;-) .
> 
> I made some more tests with indices and with more indices but the
> improvement is not worth to mention.
> 
> I am now at a point, wondering, if the transfer from centura to postgres
> is the right way but I can't imagine why the performance of centura is
> so much better. The conditions are nearly the same (256 MB RAM, Pentium
> III) beside of the OS, which is Novell Netware for Centura and Linux
> (Kernel 2.4.0) for Postgres. The version of Postgres is 7.0.3.
> 
> Does anybody have an idea what else I can do? Thank yo very much for
> your help.
> 
> Michael

> How many rows are returned by the query in reality ?
> Does the table *table1* have an index on the column *var1*
> in your centura sqlbase ?

... and did you do a "vacuum analyze"? What is the schema? What is the
query? What is the result of "explain" for that query?

All of these things are relevant for any inquiry regarding poor
performance. I'm sure you will get acceptable performance once things
are adjusted.
                  - Thomas

Michael Rudolph wrote:
> 
> To let all of you know the status of my problem: I got on one large
> step when realized, that I forgot a "vacuum analyze" after copying the
> data. When I did this, my performance gain was huge - but didn't reach
> the performance of centura at all. It is still a bit slower. Maybe I
> can optimize the ODBC-Connection in any way, because there is still
> one unsolved question when watching the postgres-logfile: Every query
> is done two times (I don't know why) and both queries of one type need
> the same execution time. So I think, if I manage to reduce that load,
> I can get an acceptable performance.
> 

Could you turn on mylog debug though it generates a lot of
debug output ? To turn on it, please add the Windows registry
entry
HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBCINST.INI\PostgreSQL\Debug
as 1. To turn off mylog debug, please set the entry to 0 or
remove it.

regards,
Hiroshi Inoue