"Jim C. Nasby" <[EMAIL PROTECTED]> wrote: > On Tue, Feb 07, 2006 at 09:54:22AM -0600, Jim C. Nasby wrote: > > Hrm, that's rather odd. What does top show when it's running through > > psql? Are the test scripts available for download? I'll try this on my > > machine as well... > > I see theh tcl now... is TCL piping into psql, or are there a set of raw > files you could post or send me? If you're piping from TCL, I'd be > curious to see what the difference is if you run this manually. For > these large data sets I also think it's not very reflective of the > database to send the result set all the way back through the client, > since that's not very representative of the real world. In the case of > PostgreSQL, a good alternative would be > > SELECT count(*) FROM ( > SELECT t1.a FROM ... > ) a > ; > > But I'm not sure if all the other databases support that.
SQLite supports the syntax above, FWIW. Your theory is that SQLite does well because it doesn't need to send data back and forth between the client and server? You're probably right. On the other hand, what good is the data if the client never sees it? You'll notice that SQLite seems to do particularly well on the tests that involve a lot of SQL. For example, test 2 with 25000 separate INSERT statements. SQLite ran in 0.7 seconds versus 16.5 seconds for PostgreSQL. Probably a big fraction of the 16.5 seconds PostgreSQL used were in transmitting all of that SQL over a socket to the server. I'm wondering if the use of prepared statements might reduce the performance gap somewhat? Notice that when doing an equally large insert in Test 12, but an insert that involves much less SQL and parsing, that PostgreSQL is actually a little faster than SQLite. Any volunteers to run the experiment? Jim? Another explanation for the poor performance by PostgreSQL in test 2 might be the PostgreSQL parser is less efficient. Or perhaps the PostgreSQL spends a lot more time trying to optimize - which can pay off on a big query but is a drag for lots of silly little inserts. A test using prepared statements would help clearify the issue. -- D. Richard Hipp <[EMAIL PROTECTED]>
