Re: Merge join and index scan strangeness

On Fri, 19 Feb 2010, Tom Lane wrote:

> Teodor Sigaev <teodor(at)sigaev(dot)ru> writes:
>>> I found something strange with merge join. Let there are two table
>
>> Sorry, postgresql's version is 8.4 from today CVS
>
> Can't reproduce it here, either in HEAD or 8.4. Sure you have a clean
> build with no local modifications? The outright-incorrect last plan
> you show seems to indicate something rather badly wrong with pathkey
> matching.

I reproduced on my machine
PostgreSQL 8.4.2 on x86_64-unknown-linux-gnu, compiled by GCC gcc (Ubuntu 4.4.1-4ubuntu9) 4.4.1, 64-bit
Notice, plan is different and index scan on t1 uses wrong index.

Merge Join (cost=45224.01..251225.22 rows=9760080 width=86) (actual time=1687.545..1687.545 rows=0 loops=1)
Merge Cond: ((t2.f1 = t1.f1) AND (t2.f2 = t1.f2) AND (t2.f3 = t1.f3) AND (t2.f4 = t1.f4))
-> Index Scan using i2 on t2 (cost=0.00..65.44 rows=600 width=59) (actual time=0.008..0.179 rows=600 loops=1)
-> Sort (cost=45224.01..45811.10 rows=234839 width=69) (actual time=1612.586..1645.436 rows=161842 loops=1)
Sort Key: t1.f1, t1.f2, t1.f3, t1.f4
Sort Method: external sort Disk: 20888kB
-> Index Scan using i11 on t1 (cost=0.00..24274.83 rows=234839 width=69) (actual time=0.637..137.659 rows=234839 loops=1)
Total runtime: 1969.029 ms
(8 rows)

Regards,
Oleg
_____________________________________________________________
Oleg Bartunov, Research Scientist, Head of AstroNet (www.astronet.ru)
Sternberg Astronomical Institute, Moscow University, Russia
Internet: oleg(at)sai(dot)msu(dot)su, http://www.sai.msu.su/~megera/
phone: +007(495)939-16-83, +007(495)939-23-83