wade <[EMAIL PROTECTED]> 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 planner about the idea that a mergejoin won't necessarily read both input streams to the end. If one variable's range is much less than the other, an indexscan-based merge can win by not scanning all of the other table. Per example from Reinhard Max. 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 ---------------------------(end of broadcast)--------------------------- TIP 4: Don't 'kill -9' the postmaster