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
