Re: Yet another abort-early plan disaster on 9.3

From: Tom Lane
Subject: Re: Yet another abort-early plan disaster on 9.3
Date: ,
Msg-id: 13982.1412031637@sss.pgh.pa.us
(view: Whole thread, Raw)
In response to: Re: Yet another abort-early plan disaster on 9.3  (Simon Riggs)
Responses: Re: Yet another abort-early plan disaster on 9.3  (Gavin Flower)
Re: Yet another abort-early plan disaster on 9.3  (Simon Riggs)
List: pgsql-performance

Tree view

Yet another abort-early plan disaster on 9.3  (Josh Berkus, )
 Re: Yet another abort-early plan disaster on 9.3  (Merlin Moncure, )
 Re: Yet another abort-early plan disaster on 9.3  (Josh Berkus, )
  Re: Yet another abort-early plan disaster on 9.3  (Greg Stark, )
   Re: Yet another abort-early plan disaster on 9.3  (Claudio Freire, )
   Re: Yet another abort-early plan disaster on 9.3  (Tom Lane, )
 Re: Yet another abort-early plan disaster on 9.3  (Josh Berkus, )
  Re: Yet another abort-early plan disaster on 9.3  (Merlin Moncure, )
 Re: Yet another abort-early plan disaster on 9.3  (Josh Berkus, )
  Re: Yet another abort-early plan disaster on 9.3  (Simon Riggs, )
   Re: Yet another abort-early plan disaster on 9.3  (Merlin Moncure, )
    Re: Yet another abort-early plan disaster on 9.3  (Simon Riggs, )
     Re: Yet another abort-early plan disaster on 9.3  (Tom Lane, )
      Re: Yet another abort-early plan disaster on 9.3  (Gavin Flower, )
       Re: Yet another abort-early plan disaster on 9.3  (Jeff Janes, )
        Re: Yet another abort-early plan disaster on 9.3  (Gavin Flower, )
        Re: Yet another abort-early plan disaster on 9.3  (Merlin Moncure, )
      Re: Yet another abort-early plan disaster on 9.3  (Simon Riggs, )
       Re: Yet another abort-early plan disaster on 9.3  ("Graeme B. Bell", )
        Re: Yet another abort-early plan disaster on 9.3  (Claudio Freire, )
        Re: Yet another abort-early plan disaster on 9.3  (Tom Lane, )
         Re: Yet another abort-early plan disaster on 9.3  ("Graeme B. Bell", )
       Re: Yet another abort-early plan disaster on 9.3  (Simon Riggs, )
        Re: Yet another abort-early plan disaster on 9.3  (Simon Riggs, )
         Re: Yet another abort-early plan disaster on 9.3  (Simon Riggs, )
          Re: Yet another abort-early plan disaster on 9.3  (Michael Paquier, )
           Re: Yet another abort-early plan disaster on 9.3  (Evgeniy Shishkin, )
     Re: Yet another abort-early plan disaster on 9.3  (Simon Riggs, )
      Re: Yet another abort-early plan disaster on 9.3  (Merlin Moncure, )
       Re: Yet another abort-early plan disaster on 9.3  (Simon Riggs, )
    Re: Yet another abort-early plan disaster on 9.3  (Ryan Johnson, )
   Re: Yet another abort-early plan disaster on 9.3  (Greg Stark, )
 Re: Yet another abort-early plan disaster on 9.3  (Josh Berkus, )
  Re: Yet another abort-early plan disaster on 9.3  (Simon Riggs, )
  Re: Yet another abort-early plan disaster on 9.3  (Jeff Janes, )
   Re: Yet another abort-early plan disaster on 9.3  (Simon Riggs, )
 Re: Yet another abort-early plan disaster on 9.3  (Josh Berkus, )
  Re: Yet another abort-early plan disaster on 9.3  (Simon Riggs, )
   Re: Yet another abort-early plan disaster on 9.3  (Peter Geoghegan, )
 Re: Yet another abort-early plan disaster on 9.3  (Josh Berkus, )
  Re: Yet another abort-early plan disaster on 9.3  (Peter Geoghegan, )
   Re: Yet another abort-early plan disaster on 9.3  (Tomas Vondra, )
  Re: Yet another abort-early plan disaster on 9.3  (Jeff Janes, )
   Re: Yet another abort-early plan disaster on 9.3  (Tomas Vondra, )
  Re: Yet another abort-early plan disaster on 9.3  (Greg Stark, )
   Re: Yet another abort-early plan disaster on 9.3  ("Tomas Vondra", )
    Re: Yet another abort-early plan disaster on 9.3  (Craig James, )
     Re: Yet another abort-early plan disaster on 9.3  (Tomas Vondra, )
      Re: Yet another abort-early plan disaster on 9.3  (Craig James, )
       Re: Yet another abort-early plan disaster on 9.3  (Tomas Vondra, )
    Re: Yet another abort-early plan disaster on 9.3  (Tomas Vondra, )
     Re: Yet another abort-early plan disaster on 9.3  (Jeff Janes, )
      Re: Yet another abort-early plan disaster on 9.3  (Tomas Vondra, )
 Re: Yet another abort-early plan disaster on 9.3  (Josh Berkus, )
  Re: Yet another abort-early plan disaster on 9.3  (Tomas Vondra, )
   Re: Yet another abort-early plan disaster on 9.3  (Greg Stark, )
    Re: Yet another abort-early plan disaster on 9.3  (Tomas Vondra, )
     Re: Yet another abort-early plan disaster on 9.3  (Greg Stark, )
 Re: Yet another abort-early plan disaster on 9.3  (Josh Berkus, )
  Re: Yet another abort-early plan disaster on 9.3  (Simon Riggs, )

Simon Riggs <> writes:
> The way I'm seeing it, you can't assume the LIMIT will apply to any
> IndexScan that doesn't have an index condition. If it has just a
> filter, or nothing at all, just an ordering then it could easily scan
> the whole index if the stats are wrong.

That statement applies with equal force to *any* plan with a LIMIT;
it's not just index scans.

The real question is to what extent are the tuples satisfying the extra
filter condition randomly distributed with respect to the index order
(or physical order, if it's a seqscan).  The existing cost estimation
code effectively assumes that they're perfectly uniformly distributed;
which is a good average-case assumption but can be horribly wrong in
the worst case.

If we could settle on some other model for the probable distribution
of the matching tuples, we could adjust the cost estimates for LIMIT
accordingly.  I have not enough statistics background to know what a
realistic alternative would be.

Another possibility is to still assume a uniform distribution but estimate
for, say, a 90% probability instead of 50% probability that we'll find
enough tuples after scanning X amount of the table.  Again, I'm not too
sure what that translates to in terms of the actual math, but it sounds
like something a statistics person could do in their sleep.

I do not think we should estimate for the worst case though.  If we do,
we'll hear cries of anguish from a lot of people, including many of the
same ones complaining now, because the planner stopped picking fast-start
plans even for cases where they are orders of magnitude faster than the
alternatives.

            regards, tom lane



pgsql-performance by date:

From: Greg Stark
Date:
Subject: Re: Yet another abort-early plan disaster on 9.3
From: Simon Riggs
Date:
Subject: Re: Yet another abort-early plan disaster on 9.3