2009/7/29 Rick Hillegas <[email protected]>: > Some great work has been put into improving Derby's "explain" capabilities > in the next feature release. In the meantime, you can view Derby query plans > by using the SYSCS_UTIL.SYSCS_SET_RUNTIMESTATISTICS() system procedure. > Please see the Derby Tuning Guide: > http://db.apache.org/derby/docs/10.5/tuning/tuning-single.html#ctundepth13055
As I've already mentioned to Kristian, from what I understood from the Tuning Guide, the information is all available once the query is finished which was quite cumbersome in my case. I was interested in testing a dozen or two combinations with different indexes setups and if each test took a long time (because the query had to be actually executed, rather than just it's plan displayed), it would take forever. I'm glad to hear there are improvements on the way as Derby remains the DB of choice on my project. > Have you tried > > ( id_group1, id ) > > This index covers your query and is in a useful sort order. If Derby does > not use this index on your large data set, then there may be a bug. It would > be interesting to see the query plan which Derby follows when you have > declared this index. Interesting idea, Rick. At the moment I don't have the Derby instance available, but I did try it out on postgresql. However, I've had no lock: the column order doesn't seem to impact the execution plan: it's invariably a seq. scan...still a nice try, though. I like your methodical, precise analytical approach. :)
