Re: subselect requires offset 0 for good performance.

Scott Marlowe <scott(dot)marlowe(at)gmail(dot)com> writes:
> OK I'm bumping this one last time in the hopes that someone has an
> idea what to do to fix it.

> Query plan: http://explain.depesz.com/s/kJ54

> This query takes 180 seconds. It loops 17391 times across the lower
> index using entries from the upper index. That seems buggy to me.

There isn't all that much that the planner can do with that query. There
are no equality join clauses, so no possibility of a merge or hash join;
the only way to implement the join is a nestloop.

Things would probably be better if it left out the one join clause it's
putting into the inner indexscan condition, so it could materialize the
result of the inner indexscan and then do a nestloop join against the
Material node. I'd expect 9.0 and up to consider that a good idea ...
but looking back, I see this is 8.4, which means you're probably out of
luck on getting a better plan. 8.4's nearly out of warranty anyway ---
consider upgrading.

regards, tom lane