On Tue, Feb 07, 2006 at 11:51:22AM -0500, [EMAIL PROTECTED] wrote: > "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?
Well, my point was that the test in question is probably generating close to 100k rows if not more. Trying to pull that much data from the database at once is either poor design (something I've seen far too often) or a pretty unusual set of requirements. In any case, it certainly wouldn't surprise me if psql gets in the way here. > 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? The original poster is sending me the generated files. I'll run an experiment with prepared statements and see what that gains us. But yes, trying to prepare that many statements over and over is a sure-fire way to slow things down. > 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. Off the top of my head, in the INNER JOIN case I believe there's about 8 different ways to execute that query, and PostgreSQL will consider all of them for every statement. So it certainly wouldn't surprise me if that was a major issue. -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Software http://pervasive.com work: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461
