Re: Why does row estimation on nested loop make no sense to me

Поиск
Список
Период
Сортировка
От Amit Langote
Тема Re: Why does row estimation on nested loop make no sense to me
Дата
Msg-id CA+HiwqFJokKGwqwDf6ZtxD95eUT2GB4-0zngOp477CqG44-yAg@mail.gmail.com
обсуждение исходный текст
Ответ на Re: Why does row estimation on nested loop make no sense to me  (Jeff Amiel <becauseimjeff@yahoo.com>)
Ответы Re: Why does row estimation on nested loop make no sense to me  (Jeff Amiel <becauseimjeff@yahoo.com>)
Список pgsql-general
>> explain analyze
>> select  era.child_entity  from entity_rel era  join user_entity ue on ue.entity_id = era.parent_entity and
ue.user_id=12345
>>
>> Nested Loop  (cost=0.00..2903.37 rows=29107 width=4) (actual time=0.028..0.274 rows=201 loops=1)
>>   ->  Index Only Scan using entity_pk on user_entity ue  (cost=0.00..62.68 rows=2 width=4) (actual time=0.011..0.012
rows=1loops=1) 
>>         Index Cond: (user_id = 12345)
>>         Heap Fetches: 1
>>   ->  Index Scan using rel_parent on entity_rel era  (cost=0.00..1261.85 rows=317 width=8) (actual time=0.013..0.164
rows=201loops=1) 
>>         Index Cond: (parent_entity = ue.entity_id)
>> Total runtime: 0.361 ms


I noticed when the explain output in your first mail shows Index Cond:
(user_id = 10954) whereas your query says: ue.user_id=12345. Something
with that? Although, your explain analyze does show the same values at
both places with the row estimate being 29107 in both cases, which,
well, looks awful and quite unexpected though there seem to have been
similar observations before

>
>>Have you tried analyze (it's probably a case of insufficient/outdated
>>statistics to planner's disposal) or probably consider changing
>>default_statistics_target?
>
>
> Again - my question revolves not around the whether or not I am getting good or bad estimates - my question is
relatedto the fact that the nested-loop row estimation does not appear to be derived from the nodes below it - it is
offby orders of magnitude.  I've never seen this before. 
> That aside, yes - I did analyze and tweak stats target during experimentation - no change.

Did you also check select count(*) on both the relations and found
related numbers?

--
Amit Langote


В списке pgsql-general по дате отправления:

Предыдущее
От: Alfonso Afonso
Дата:
Сообщение: Re: Best practice on inherited tables
Следующее
От: Jeff Amiel
Дата:
Сообщение: Re: Why does row estimation on nested loop make no sense to me