Received: from out1.ibm.net (out1.ibm.net [165.87.194.252]) by hub.org (8.9.1/8.9.1) with ESMTP id UAA15748 for ; Tue, 27 Oct 1998 20:39:09 -0500 (EST) (envelope-from pierre@desertmoon.com) Received: from [32.100.208.197] (slip-32-100-208-197.tx.us.ibm.net [32.100.208.197]) by out1.ibm.net (8.8.5/8.6.9) with SMTP id BAA10002; Wed, 28 Oct 1998 01:38:55 GMT Message-Id: <199810280138.BAA10002@out1.ibm.net> Subject: Re: [SQL] Joining bug???? Date: Tue, 27 Oct 1998 19:38:55 -0600 x-mailer: Claris Emailer 2.0v3, January 22, 1998 From: pierre To: "David Hartwig" cc: Mime-Version: 1.0 Content-Type: text/plain; charset="US-ASCII" >Nice query. You mentioned having indexes on id columns etc. Are the >indices >single column, or multi-column? You may want to consider making some of >them >multi-column. The EXPLAIN does indicate usage of your indices, but it >may not be >optimal. If the distribution of your data in the indices is such that >the each >index scan must go through large chunks of data to qualify the other >attributes in >the join, it will run slow. By adding some well placed multi-part >indices, the >index scans will be narrower in their scan sets. Or not. > >Also, I could be wrong, but, I have found even batch loaded data needs a >VACUUM >ANALYZE to gather distributions statistics. > >pierre@desertmoon.com wrote: > >> > Thanks David. The vacuum analyze did the trick. I made the invalid assumption that the statistics would be up to date just after a copy and index creation. They were not. As soon as I ran the vacuum across all my tables the explains changed and I got a MAJOR speed increase and the most complicated query takes no more than 4-8 seconds. This is perfect. Perhaps this (bug??) should be documented? I've seen documentation relating to the vacuum analyze, but I always made that invalid assumption. *sigh* Ah well you live and you learn. :) -=pierre