Обсуждение: Re: [SQL] Joining bug????

>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

> 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