> John A Meinel <[EMAIL PROTECTED]> 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 ---------------------------(end of broadcast)--------------------------- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match