Re: Joel's Performance Issues WAS : Opteron vs Xeon

> John A Meinel <john(at)arbash-meinel(dot)com> writes:
> > Joel Fradkin wrote:
> >> Postgres was on the second run
> >> Total query runtime: 17109 ms.
> >> Data retrieval runtime: 72188 ms.
> >> 331640 rows retrieved.
>
> > How were you measuring "data retrieval time"?
>
> I suspect he's using pgadmin. We've seen reports before suggesting
that
> pgadmin can be amazingly slow, eg here
> http://archives.postgresql.org/pgsql-performance/2004-10/msg00427.php
> where the *actual* data retrieval time as shown by EXPLAIN ANALYZE
> was under three seconds, but pgadmin claimed the query runtime was 22
> sec and data retrieval runtime was 72 sec.

The problem is that pgAdmin takes your query results and puts it in a
grid. The grid is not designed to be used in that way for large
datasets. The time complexity is not linear and really breaks down
around 10k-100k rows depending on various factors. pgAdmin users just
have to become used to it and use limit or the filter feature at
appropriate times.

The ms sql enterprise manager uses cursors which has its own set of
nasty issues (no mvcc).

In fairness, unless you are running with \a switch, psql adds a fair
amount of time to the query too.

Joel:
"Postgres was on the second run
Total query runtime: 17109 ms.
Data retrieval runtime: 72188 ms.
331640 rows retrieved."

The Data retrieval runtime is time spend by pgAdmin formatting, etc.
The query runtime is the actual timing figure you should be concerned
with (you are not comparing apples to apples). I can send you a utility
I wrote in Delphi which adds only a few seconds overhead for 360k result
set. Or, go into psql, throw \a switch, and run query.

or:
psql -A -c "select * from myview where x" > output.txt

it should finish the above in 16-17 sec plus the time to write out the
file.

Joel, I have a lot of experience with all three databases you are
evaluating and you are making a huge mistake switching to mysql. you
can make a decent case for ms sql, but it's quite expensive at your
level of play as you know.

Merlin