On Tue, Aug 13, 2013 at 4:50 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Scott Marlowe <scott.marlowe@gmail.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.
Thanks for the hints, we'll try them. As for the upgrade an upgrade to
9.1, possibly 9.2 is already in the planning stages. But you know how
production upgrades go, slow...