On Fri, 2003-07-11 at 11:36, Scott Cain wrote:
> Rod,
>
> I see what you mean about the scan on the feature_pkey taking a long
> time. I tried several things to remedy that. I created an index on
> feature (feature_id,type_id) (which I don't think makes sense since
> feature_id is the primary key, so add another column really doesn't
It may be the primary key, but the system looked like it was throwing
away many rows based on type_id. If it was throwing away many more rows
than found, the index with type_id may have been cheaper.
It is difficult to tell from an EXPLAIN ANALYZE as it doesn't tell you
exactly how many rows were filtered, just the cost to read them and how
many were used after the filter.
> help). I also created a index on feature (type_id, feature_id), but the
> planner doesn't use it. Also, there was an already existing index on
> feature (type_id) that the planner never used.
It cannot use more than one index for a given table scan at the moment.
There are proposals on how to 'fix' that, but those require significant
overhauls of various systems.
> Any other ideas?
Out of curiosity, what do you get if you disable hash joins?
set enable_hashjoin = false;
How about a partial index on (feature_id) where type_id = 219?