Re: Low performance on Windows problem - Mailing list pgsql-performance

On Tue, Sep 13, 2005 at 07:58:20AM -0400, Merlin Moncure wrote:
> Can you give specific examples of cases that are not performing like you
> expect?  If possible, give a few queries with explain analyze times and
> all that.

O.K. I have found one particular problem:

2005-09-13 14:43:02 LOG:  statement: declare SQL_CUR03949008 cursor for
SELECT * FROM t_umlpattern
2005-09-13 14:43:02 LOG:  duration: 0.000 ms
2005-09-13 14:43:02 LOG:  statement: fetch 1000 in SQL_CUR03949008
2005-09-13 14:43:22 LOG:  duration: 20185.000 ms

This command is executed while a model is loaded from the repository.

The table definition is:
CREATE TABLE t_umlpattern (
    PatternID INTEGER DEFAULT nextval('"patternid_seq"'::text) NOT NULL
PRIMARY KEY,
    PatternCategory VARCHAR(100),
    PatternName VARCHAR(150),
    Style VARCHAR(250),
    Notes TEXT,
    PatternXML TEXT,
    Version VARCHAR(50)
);

It has just 23 rows but the PatternXML column is rather large. The table
dump has over 900 kB.

Now
select * from t_umlpattern limit 2

takes 1500+ msec on the Windows machine and 60 on a comparable Linux
machine. Both selects performed from remote PgAdmin.
The same select performed localy on the windows machine takes 60 msec.

So I guess the problem is in the transfer of the bigger amount of data from
the Windows server.

I put the dump at http://www.insula.cz/dali/misc/table.zip

Could anybody confirm the difference?

Thanks for any suggestions.

Dalibor Sramek

--
Dalibor Sramek  http://www.insula.cz/dali             \ In the eyes of cats
 /              dalibor.sramek@insula.cz               \  all things
/      >H blog  http://www.transhumanismus.cz/blog.php  \   belong to cats.