Re: Merge join and index scan strangeness

On Fri, 19 Feb 2010, Tom Lane wrote:

> Teodor Sigaev <teodor@sigaev.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.44rows=600 width=59) (actual time=0.008..0.179 rows=600 loops=1)   ->  Sort  (cost=45224.01..45811.10
rows=234839width=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=234839width=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@sai.msu.su, http://www.sai.msu.su/~megera/
phone: +007(495)939-16-83, +007(495)939-23-83