Received: from desertmoon.com (qmailr@desertmoon.com [198.182.124.2]) by hub.org (8.9.1/8.9.1) with SMTP id OAA04892 for ; Tue, 27 Oct 1998 14:00:11 -0500 (EST) (envelope-from pierre@desertmoon.com) From: pierre@desertmoon.com Received: (qmail 19037 invoked by uid 100); 27 Oct 1998 19:04:56 -0000 Message-ID: <19981027190456.19036.qmail@desertmoon.com> Date: 27 Oct 1998 12:04:56 -0700 Subject: Re: [SQL] Joining bug???? To: daveh@insightdist.com (David Hartwig) Date: Tue, 27 Oct 1998 12:04:56 -0700 (MST) Cc: pgsql-sql@postgreSQL.org In-Reply-To: <36360F15.9CD6A1B@insightdist.com> from "David Hartwig" at Oct 27, 98 01:21:09 pm X-Mailer: ELM [version 2.4 PL25] MIME-Version: 1.0 Content-Type: text/plain; charset=US-ASCII Content-Transfer-Encoding: 7bit > > 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. > My apologies I should have been a bit more specific. The indices are multi column, _id first prod_id second. Also A search for just the keyword photoshop returns just 35 or so values. I was under the impression that postgresql used the bottom up method of optimization. I.e. the most narrow criteria should be declared last, why doesn't the explain show the use of the keyword first? > Also, I could be wrong, but, I have found even batch loaded data needs a VACUUM > ANALYZE to gather distributions statistics. I'll try just about anything at this point....oh...and most of these tables have been clustered by their major indexes. -=pierre