Search Postgresql Archives

Re: massive performance hit when using "Limit 1"

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



Richard Huxton wrote:
Rich Doughty wrote:


This one goes nuts and doesn't return. is there any way i can
force a query plan similar to the one above?

  EXPLAIN SELECT _t.* FROM
       tokens.ta_tokens       _t INNER JOIN
       tokens.ta_tokens_stock _s ON _t.token_id = _s.token_id
  WHERE
       _s.retailer_id = '96599' AND
       _t.value       = '10'
  ORDER BY
       _t.number ASC
  LIMIT '1';
                                                    QUERY PLAN
-------------------------------------------------------------------------------------------------------------------
 Limit  (cost=0.00..14967.39 rows=1 width=27)
   ->  Nested Loop  (cost=0.00..22316378.56 rows=1491 width=27)
-> Index Scan using ta_tokens_number_key on ta_tokens _t (cost=0.00..15519868.33 rows=1488768 width=27)
               Filter: ((value)::numeric = 10::numeric)
-> Index Scan using ta_tokens_stock_pkey on ta_tokens_stock _s (cost=0.00..4.55 rows=1 width=4) Index Cond: (("outer".token_id)::integer = (_s.token_id)::integer)
               Filter: ((retailer_id)::integer = 96599)


I *think* what's happening here is that PG thinks it will use the index on _t.number (since you are going to sort by that anyway) and pretty soon find a row that will:
  1. have value=10
  2. join to a row in _s with the right retailer_id
It turns out that isn't the case, and so the query takes forever. Without knowing what "value" and "number" mean it's difficult to be sure, but I'd guess it's the "token_id" join part that's the problem, since at a guess a high-numbered retailer will have tokens with high-numbered "retailer_id".

If you'd posted EXPLAIN ANALYSE then we'd be able to see what actually did happen.

no chance. it takes far too long to return (days...).

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.

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


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux