Re: Bad plan after vacuum analyze

От: Tom Lane
Тема: Re: Bad plan after vacuum analyze
Дата: ,
Msg-id: 13964.1115837926@sss.pgh.pa.us
(см: обсуждение, исходный текст)
Ответ на: Re: Bad plan after vacuum analyze  (Josh Berkus)
Ответы: Re: Bad plan after vacuum analyze  (Guillaume Smet)
Список: pgsql-performance

Скрыть дерево обсуждения

Bad plan after vacuum analyze  (Guillaume Smet, )
 Re: Bad plan after vacuum analyze  (Josh Berkus, )
  Re: Bad plan after vacuum analyze  (Tom Lane, )
   Re: Bad plan after vacuum analyze  (Guillaume Smet, )
    Re: Bad plan after vacuum analyze  (Tom Lane, )
     Re: Bad plan after vacuum analyze  (Guillaume Smet, )
      Re: Bad plan after vacuum analyze  (Tom Lane, )
       Re: Bad plan after vacuum analyze  (Guillaume Smet, )
        Re: Bad plan after vacuum analyze  (Markus Bertheau, )
 Re: Bad plan after vacuum analyze  (Mischa Sandberg, )

Josh Berkus <> writes:
>          ->  Merge Right Join  (cost=8.92..9.26 rows=1 width=529) (actual
> time=129.100..129.103 rows=1 loops=1)
>                Merge Cond: ("outer".object_id = "inner".parent_application_id)
>                ->  Index Scan using acs_objects_object_id_p_hhkb1 on
> acs_objects t98  (cost=0.00..2554.07 rows=33510 width=81) (actual
> time=0.043..56.392 rows=33510 loops=1)
>                ->  Sort  (cost=8.92..8.93 rows=1 width=452) (actual
> time=0.309..0.310 rows=1 loops=1)
>                      Sort Key: t22.parent_application_id

> Here the planner chooses a merge right join.  This decision seems to have been
> made entirely on the basis of the cost of the join itself (total of 17)
> without taking the cost of the sort and index access (total of 2600+) into
> account.

> Tom, is this a possible error in planner logic?

No, it certainly hasn't forgotten to add in the costs of the inputs.
There might be a bug here, but if so it's much more subtle than that.

It looks to me like the planner believes that the one value of
t22.parent_application_id joins to something very early in the
acs_objects_object_id_p_hhkb1 sort order, and that it will therefore not
be necessary to run the indexscan to completion (or indeed very far at
all, considering that it's including such a small fraction of the total
indexscan cost).

andrew@supernews pointed out recently that this effect doesn't apply to
the outer side of an outer join; releases before 7.4.8 mistakenly think
it does.  But unless my wires are totally crossed today, acs_objects is
the nullable side here and so that error isn't applicable anyway.

So, the usual questions: have these two tables been ANALYZEd lately?
If so, can we see the pg_stats rows for the object_id and
parent_application_id columns?

            regards, tom lane


В списке pgsql-performance по дате сообщения:

От: Enrico Weigelt
Дата:
Сообщение: Re: BLOB's bypassing the OS Filesystem for better Image loading speed?
От: Bruce Momjian
Дата:
Сообщение: Re: Intel SRCS16 SATA raid?