Re: massive performance hit when using "Limit 1"

Rich Doughty wrote:
> Richard Huxton wrote:
>
>> Rich Doughty wrote:

[snip]

>> Try the same query but with a low retailer_id (100 or something) and
>> see if it goes a lot quicker. If that is what the problem is, try
>> changing the ORDER BY to something like "_s.retailer_id, _t.value,
>> _t.number" and see if that gives the planner a nudge in the right
>> direction.
>
>
> the retailer_id would make no difference as thee are only 4000-ish rows in
> ta_tokens_stock and they all (for now) have the same retailer_id.

ooops. i (sort of) spoke too soon. i didn't read the second half of the
comment properly. changing the ORDER BY clause does force a more sensible
query plan.

many thanks. so that's one way to give the planner hints...

>> Failing that, a change to your indexes will almost certainly help.
>
>
> i'm not sure that's the case. the exact same query, but limited to >2 rows
> is fine.
>
> I found this in the 8.0.4 relnotes. i reckon its a good guess that's
> what the
> problem is:
>
> * Fix mis-planning of queries with small LIMIT values due to poorly thought
> out "fuzzy" cost comparison

--

- Rich Doughty