Re: Odd problem with planner choosing seq scan

From: Tom Lane
Subject: Re: Odd problem with planner choosing seq scan
Date: ,
Msg-id: 16596.1177209911@sss.pgh.pa.us
(view: Whole thread, Raw)
In response to: Re: Odd problem with planner choosing seq scan  (Colin McGuigan)
List: pgsql-performance

Tree view

Odd problem with planner choosing seq scan  (Colin McGuigan, )
 Re: Odd problem with planner choosing seq scan  (Tom Lane, )
  Re: Odd problem with planner choosing seq scan  (Colin McGuigan, )
   Re: Odd problem with planner choosing seq scan  (Tom Lane, )

Colin McGuigan <> writes:
> I know I can do it by adjusting cost parameters, but I was really
> curious as to why adding a "LIMIT 5000" onto a SELECT from a table with
> only 530 rows in it would affect matters at all.

The LIMIT prevents the sub-select from being flattened into the main
query.  In the current code this has a side-effect of preventing any
statistical information from being used to estimate the selectivity
of the filter conditions --- so you get a default rowcount estimate
that's way too small, and that changes the shape of the join plan.
It's giving you the "right" answer for entirely the wrong reason.

            regards, tom lane


pgsql-performance by date:

From: Tom Lane
Date:
Subject: Re: Odd problem with planner choosing seq scan
From: Ulrich Cech
Date:
Subject: Re: Large objetcs performance