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 ?
Show quoted text
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
Import Notes
Reference msg id not found: 3B5F061A.3041F519@t-online.de
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
Import Notes
Reference msg id not found: 3B5F061A.3041F519@t-online.de
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.
1. I assume following index exists:
create index table1_x0 on table1 (var1);
"select distinct" when executed through a btree index could benefit from
an optimization as follows:
for each "key" that is already in a sorted list output skip the heap tuple
lookup, since for a duplicate it is not necessary to know the tx status,
not sure if that is implementable though.
(PostreSQL needs to look up the tx status in the table for each key in index)
The performance difference is unfortunately to be expected, since Centura can
probably do the distinct by only looking at the index.
You could try to normalize your schema (pull those values out into an extra table)
if you need such "distinct" queries to be fast.
Andreas
Import Notes
Resolved by subject fallback
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
Import Notes
Reference msg id not found: 3B5F061A.3041F519@t-online.de