Re: Odd problem with planner choosing seq scan - Mailing list pgsql-performance

From Tom Lane
Subject Re: Odd problem with planner choosing seq scan
Date
Msg-id 16596.1177209911@sss.pgh.pa.us
Whole thread Raw
In response to Re: Odd problem with planner choosing seq scan  (Colin McGuigan <cmcguigan@earthcomber.com>)
List pgsql-performance
Colin McGuigan <cmcguigan@earthcomber.com> 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:

Previous
From: Colin McGuigan
Date:
Subject: Re: Odd problem with planner choosing seq scan
Next
From: Ulrich Cech
Date:
Subject: Re: Large objetcs performance