Thread

1. Re: [SQL] Joining bug????

   pierre@desertmoon.com — 1998-10-28T01:38:55Z

   >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
   
   

2. Re: [SQL] Joining bug????

   Bruce Momjian <maillist@candle.pha.pa.us> — 1998-10-28T02:19:18Z

   > 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. :)
   
   I have beefed up the FAQ:
   
      For column-specific optimization statistics, use vacuum analyze.
      Vacuum analyze is important for complex multi-join queries, so the
      optimizer can estimate the number of rows returned from each table,
      and choose the proper join order. The backend does not keep track of
      column statistics on its own, and vacuum analyze must be run to
      collect them periodically.
   
   -- 
     Bruce Momjian                        |  http://www.op.net/~candle
     maillist@candle.pha.pa.us            |  (610) 853-3000
     +  If your life is a hard drive,     |  830 Blythe Avenue
     +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026