Re: Inefficient queryplan for query with intersectable

From: Tom Lane
Subject: Re: Inefficient queryplan for query with intersectable
Date: ,
Msg-id: 1116.1125152821@sss.pgh.pa.us
(view: Whole thread, Raw)
In response to: Re: Inefficient queryplan for query with intersectable  (Arjen van der Meijden)
Responses: Re: Inefficient queryplan for query with  (Ron)
Re: Inefficient queryplan for query with intersectable  (Arjen van der Meijden)
List: pgsql-performance

Tree view

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 by date:

From: Tom Lane
Date:
Subject: Re: Weird performance drop after VACUUM
From: Tom Lane
Date:
Subject: Re: Limit + group + join