Thread: Query performance. 7.2.3 Vs. 7.3

Query performance. 7.2.3 Vs. 7.3

From
wade
Date:
  While playing with one of my DBs under 7.3 to make use of its better
explain features, I came across a query that runs significantly slower
under 7.3 than
7.2.3.  At first, I thought it would be a hardware issue, so i installed both
versions on the same box.  
7.2.3 tends to run the query in 80% of the time 7.3 does.
Explain output can be found at http://arch.wavefire.com/72v73a.txt

Please don't hesitate to drop me a line if you require more info.-Wade Klaver


Re: Query performance. 7.2.3 Vs. 7.3

From
Tom Lane
Date:
wade <wade@wavefire.com> writes:
>   While playing with one of my DBs under 7.3 to make use of its better
> explain features, I came across a query that runs significantly slower
> under 7.3 than
> 7.2.3.  At first, I thought it would be a hardware issue, so i installed both
> versions on the same box.  
> 7.2.3 tends to run the query in 80% of the time 7.3 does.
> Explain output can be found at http://arch.wavefire.com/72v73a.txt

The difference evidently is that 7.3 chooses a mergejoin where 7.2
picks a hashjoin.

AFAICT this must be a consequence of the reduction in mergejoin
estimated costs associated with this patch:

2002-02-28 23:09  tgl
* src/: backend/executor/nodeMergejoin.c,backend/optimizer/path/costsize.c,
backend/utils/adt/selfuncs.c,backend/utils/cache/lsyscache.c,include/utils/lsyscache.h,include/utils/selfuncs.h: Teach
plannerabout the idea that amergejoin won't necessarily read both input streams to the end.  Ifone variable's range is
muchless than the other, anindexscan-based merge can win by not scanning all of the othertable.    Per example from
ReinhardMax.
 

since we really didn't do anything else in 7.3 that changed the behavior
of costsize.c.

I can't get totally excited about a 20% estimation error (if the planner
was never off by more than that, I'd be overjoyed ;-)) ... but if you
want to dig into the statistics and try to figure out why this added
logic is misestimating in your particular case, I'd be interested to
hear.  Probably the first thing to look at is why the estimated row
counts are off by almost a factor of 3 for that join.
        regards, tom lane


Re: Query performance. 7.2.3 Vs. 7.3

From
Neil Conway
Date:
On Thu, 2002-11-28 at 21:23, Tom Lane wrote:
> wade <wade@wavefire.com> writes:
> > Explain output can be found at http://arch.wavefire.com/72v73a.txt
> 
> The difference evidently is that 7.3 chooses a mergejoin where 7.2
> picks a hashjoin.

I was looking at this a bit in IRC, and I was more concerned by the fact
that 7.3 was 20% than 7.2 on the same hardware, when they both used the
same query plan (consider the data at the end of the URL above, after
the execution of 'SET enable_mergejoin = off;').

Also, is it expected that the cardinality estimates for join steps won't
be very accurate, right? (estimated: 19 rows, actual: 765 rows)

Cheers,

Neil
-- 
Neil Conway <neilc@samurai.com> || PGP Key ID: DB3C29FC





Re: Query performance. 7.2.3 Vs. 7.3

From
Tom Lane
Date:
Neil Conway <neilc@samurai.com> writes:
> I was looking at this a bit in IRC, and I was more concerned by the fact
> that 7.3 was 20% than 7.2 on the same hardware, when they both used the
> same query plan (consider the data at the end of the URL above, after
> the execution of 'SET enable_mergejoin = off;').

Hm.  Are we sure that both versions were built with the same
optimization level, etc?  (My private bet is that Wade's 7.2 didn't
have multibyte or locale support --- but that's a long shot when we
don't know the datatypes of the columns being joined on...)

> Also, is it expected that the cardinality estimates for join steps won't
> be very accurate, right? (estimated: 19 rows, actual: 765 rows)

Well, it'd be nice to do better --- I was hoping Wade would look into
why the row estimates were off so much.
        regards, tom lane


Re: Query performance. 7.2.3 Vs. 7.3

From
wade
Date:
At 09:58 PM 11/28/02 -0500, you wrote:
>Hm.  Are we sure that both versions were built with the same
>optimization level, etc?  (My private bet is that Wade's 7.2 didn't
>have multibyte or locale support --- but that's a long shot when we
>don't know the datatypes of the columns being joined on...)
>
>> Also, is it expected that the cardinality estimates for join steps won't
>> be very accurate, right? (estimated: 19 rows, actual: 765 rows)
OK, I've updated the link http://arch.wavefire.com/72v73a.txt to include
the table schema for those involved in the query.  As far as locale suport
et al, I can tell you that both are built using a straigh, out-of-the-box
./configure.
>
>Well, it'd be nice to do better --- I was hoping Wade would look into
>why the row estimates were off so much.
I'd love to :).  But where to start? Can you point me at a thread where a
similar procedure was explained to someone else?-Wade
>
>            regards, tom lane
>
>---------------------------(end of broadcast)---------------------------
>TIP 4: Don't 'kill -9' the postmaster
>
>