Re: Inefficient queryplan for query with intersectable

От: Tom Lane
Тема: Re: Inefficient queryplan for query with intersectable
Дата: ,
Msg-id: 1116.1125152821@sss.pgh.pa.us
(см: обсуждение, исходный текст)
Ответ на: Re: Inefficient queryplan for query with intersectable  (Arjen van der Meijden)
Ответы: Re: Inefficient queryplan for query with  (Ron)
Re: Inefficient queryplan for query with intersectable  (Arjen van der Meijden)
Список: pgsql-performance

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

Inefficient queryplan for query with intersectable subselects/joins  (Arjen van der Meijden, )
 Re: Inefficient queryplan for query with intersectable  (Richard Huxton, )
  Re: Inefficient queryplan for query with intersectable  (Arjen van der Meijden, )
   Re: Inefficient queryplan for query with intersectable  (Tom Lane, )
    Re: Inefficient queryplan for query with intersectable  (Arjen van der Meijden, )
     Re: Inefficient queryplan for query with intersectable  (Tom Lane, )
      Re: Inefficient queryplan for query with  (Ron, )
      Re: Inefficient queryplan for query with intersectable  (Arjen van der Meijden, )

Arjen van der Meijden <> writes:
> But appareantly there is a bug in the explain mechanism of the 8.1devel
> I'm using (I downloaded a nightly 25 august somewhere in the morning
> (CEST)), since it returned:
> ERROR:  bogus varno: 9

Yeah, someone else sent in a test case for this failure (or at least one
with a similar symptom) yesterday.  I'll try to fix it today.

> Is a nested loop normally so much (3x) more costly than a hash join? Or
> is it just this query that gets estimated wronly?

There's been some discussion that we are overestimating the cost of
nestloops in general, because we don't take into account that successive
scans of the inner relation are likely to find many pages already in
cache from the earlier scans.  So far no one's come up with a good cost
model to use for this, though.

            regards, tom lane


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

От: Tom Lane
Дата:
Сообщение: Re: Weird performance drop after VACUUM
От: Tom Lane
Дата:
Сообщение: Re: Limit + group + join