>
> 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