From: Tom Lane <tgl@sss.pgh.pa.us>
To: Scott Marlowe <scott.marlowe@gmail.com>
Cc: pgsql-performance@postgresql.org <pgsql-performance@postgresql.org>
Subject: Re: subselect requires offset 0 for good performance.
Date: Fri, 02 Aug 2013 16:51:23 -0400
Message-ID: <8360.1375476683@sss.pgh.pa.us> (raw)
In-Reply-To: <CAOR=d=0EJw=LHpqFk-y4HSvVuESQDbZ8uExfAxv0KDS7T7sEHw@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>
<CAOR=d=02KtqKwD36UCu1wV3krbnQ_3T5yjjihONauCp_OhvwKA@mail.gmail.com>
<6895.1375471878@sss.pgh.pa.us>
<CAOR=d=1noLyUHs9SZ6VqABK3VF6ECErbeLGJe0WfWZ_zDe7LKg@mail.gmail.com>
<CAOR=d=0EJw=LHpqFk-y4HSvVuESQDbZ8uExfAxv0KDS7T7sEHw@mail.gmail.com>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-performance>
Scott Marlowe <scott.marlowe@gmail.com> writes:
> Yep. Added the indexes and performance went right into the dumper. New
> plan on new table with old data added in random order now looks like
> the old table, only worse because it's on a slower drive. Just to be
> complete here's the plan: http://explain.depesz.com/s/PYH Note that I
> created new table with order by random() and created indexes. Ran
> analyze on it, and the select plan looks similar now:
> http://explain.depesz.com/s/bsE> So maybe I can make a test case now. But to summarize, when it can use
> indexes this query gets REAL slow because it lacks a materialize step.
> That seem about right?
Well, the plans shown here could *not* use a materialize step because the
inner scan makes use of a value from the current outer row. The
materialized plan has to omit one of the index conditions from the inner
scan and then apply it as a join condition.
I suspect the real reason that the fast case is fast is that the inner
relation, even without the p.tree_sortkey >= pro_partners.tree_sortkey
condition, is empty, and thus the join runs very quickly. But the planner
doesn't know that. Its estimate of the row count isn't very large, but
it's definitely not zero, plus it thinks that adding the additional index
condition reduces the rowcount by a factor of 3 from there. So it comes
to the wrong conclusion about the value of materializing a fixed inner
relation as opposed to using a parameterized indexscan.
Have you tried increasing the statistics targets for these join columns?
It's also possible that what you need to do is adjust the planner's
cost parameters ...
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
Subject: Re: subselect requires offset 0 for good performance.
In-Reply-To: <8360.1375476683@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