From: Tom Lane <tgl@sss.pgh.pa.us>
To: Scott Marlowe <scott.marlowe@gmail.com>
Cc: Vik Fearing <vik.fearing@dalibo.com>
Cc: pgsql-performance@postgresql.org <pgsql-performance@postgresql.org>
Subject: Re: subselect requires offset 0 for good performance.
Date: Tue, 13 Aug 2013 18:50:50 -0400
Message-ID: <21534.1376434250@sss.pgh.pa.us> (raw)
In-Reply-To: <CAOR=d=0S6NqnX=TfX1=81M442=LM6QaN1fy7FHowVCfVskipOg@mail.gmail.com>
References: <CAOR=d=2Fq-TzwzRf92BOeeHGqhkPXVG5_zs+_aR690ZxPp3JAg@mail.gmail.com>
<4522.1375400642@sss.pgh.pa.us>
<CAOR=d=3UVax7k_nT7Gm+Y2N0=0WZekT2dyOzxWM4wjsryfQL=Q@mail.gmail.com>
<51FB61C8.4090206@dalibo.com>
<5204170D.4020608@dalibo.com>
<CAOR=d=0S6NqnX=TfX1=81M442=LM6QaN1fy7FHowVCfVskipOg@mail.gmail.com>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-performance>
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.
regards, tom lane
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: tgl@sss.pgh.pa.us, scott.marlowe@gmail.com, vik.fearing@dalibo.com
Subject: Re: subselect requires offset 0 for good performance.
In-Reply-To: <21534.1376434250@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox