From: | pierre <pierre(at)desertmoon(dot)com> |
---|---|
To: | "David Hartwig" <daveh(at)insightdist(dot)com> |
Cc: | <pgsql-sql(at)postgreSQL(dot)org> |
Subject: | Re: [SQL] Joining bug???? |
Date: | 1998-10-28 01:38:55 |
Message-ID: | 199810280138.BAA10002@out1.ibm.net |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
>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(at)desertmoon(dot)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
From | Date | Subject | |
---|---|---|---|
Next Message | Bruce Momjian | 1998-10-28 02:19:18 | Re: [SQL] Joining bug???? |
Previous Message | Howie | 1998-10-27 23:48:56 | Re: [SQL] Joining bug???? |