Re: Missed index opportunity for outer join?

Tom Lane wrote:
> rm_pg@cheapcomplexdevices.com writes:
>>On Mon, 5 Dec 2005, Tom Lane wrote:
>
>>I speculate that the seq_scan wasn't really the slow part
>>compared to not using using both parts of the index in the
>>second part of the plan.  The table point_features is tens of
>>thousands of rows, while the table facets is tens of millions.
>
> Agreed, but it's still odd that it would use a seqscan in one case and
> not the other.

Hmm.  Unfortunately that was happening on a production system
and the amount of data in the tables has changed - and now I'm
no longer getting a seq_scan when I try to reproduce it.   That
system is still using 8.1.0.

The "point_features" table is pretty dynamic and it's possible
that the data changed between my 'explain analyze' statement in
the first post in this thread.   However since both of them
show an estimate of "rows=948" and returned an actual of 917 I
don't think that happened.

> I found the reason why the fac_id=261 clause isn't getting used as an
> index qual; it's a bit of excessive paranoia that goes back to 2002.
> I've fixed that for 8.1.1, but am still wondering about the seqscan
> on the other side of the join.

I now have a development system with cvs-tip; but have not yet
reproduced the seq scan on it either.  I'm using the same data
that was in "point_features" with "featureid=120" - but don't have
any good way of knowing what other data may have been in the table
at the time.   If desired, I could set up a cron job to periodically
explain analyze that query and see if it recurs.