On 19 Sep 2014 19:40, "Josh Berkus" <josh@agliodbs.com> wrote:
>
> On 09/19/2014 10:15 AM, Merlin Moncure wrote:
> > On Wed, Sep 17, 2014 at 7:11 PM, Josh Berkus <josh@agliodbs.com> wrote:
> >> This is the core issue with abort-early plans; they depend on our
> >> statistics being extremely accurate, which we know they are not. And if
> >> they're wrong, the execution time climbs by 1000X or more. Abort-early
> >> plans are inherently riskier than other types of query plans.
All plans are risky if the stats are wrong. It's one of the perennial digressions that many postgres newcomers make to track worst case costs and provide a knob for planner aggressiveness but it always breaks down when you try to quantify the level of risk because you discover that even such simple things as indeed scans versus sequential scans can be equally risky either way.
> >> What I'm not clear on is why upgrading from 9.1 to 9.3 would bring about
> >> this change. The stats are no more than 10% different across the
> >> version change.
There's no difference. Postgres has been estimating LIMIT costs this way since before I came to postgres in 7.3.
> > Is there a canonical case of where 'abort early' plans help? (I'm new
> > to that term -- is it a recent planner innovation...got any handy
> > links?)
>
> Yeah, here's an example of the canonical case:
>
> Table t1 ( a, b, c )
>
> - "b" is low-cardinality
> - "c" is high-cardinality
> - There are separate indexes on both b and c.
>
> SELECT a, b, c FROM t1
> WHERE b = 2
> ORDER BY c LIMIT 1;
You badly want a partial index on c WHERE b=2 for each value of 2 which appears in your queries.
It would be neat to have an opclass which worked like that. Which would amount to having prefix compression perhaps.
What plan does 9.1 come up with?