pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: pierre <pierre@desertmoon.com>
To: David Hartwig <daveh@insightdist.com>
Cc: pgsql-sql@postgreSQL.org
Subject: Re: [SQL] Joining bug????
Date: Tue, 27 Oct 1998 19:38:55 -0600
Message-ID: <199810280138.BAA10002@out1.ibm.net> (raw)

>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



view thread (16+ messages)  latest in thread

Message-ID: <199810280138.BAA10002@out1.ibm.net>
Permalink:  ../199810280138.BAA10002@out1.ibm.net/
Also on:    postgresql.org/message-id/199810280138.BAA10002@out1.ibm.net

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-sql@postgresql.org
  Cc: pierre@desertmoon.com, daveh@insightdist.com
  Subject: Re: [SQL] Joining bug????
  In-Reply-To: <199810280138.BAA10002@out1.ibm.net>
  References: <199810280138.BAA10002@out1.ibm.net>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox