Hello.
How to use tid scans? This below not works :-(
Always is used merge join.
DROP TABLE IF EXISTS test1;
CREATE TABLE test1 as select i,hashint4(i)::text from
generate_series(1,10000) as a(i);
DROP TABLE IF EXISTS test2;
CREATE TABLE test2 as select j,j%10000 as i,null::tid as ct from
generate_series(1,1000000) as a(j);
UPDATE test2 SET ct=test1.ctid
FROM test1 WHERE test2.i=test1.i;
VACUUM ANALYZE test1;
VACUUM ANALYZE test2;
SET enable_tidscan = true;
SELECT * FROM test1 join test2 on(test1.ctid=test2.ct)
------------------------
Explain analyze
------------------------
"Merge Join (cost=249703.68..283698.78 rows=1999633 width=28) (actual
time=7567.582..19524.865 rows=999900 loops=1)"
" Output: test1.i, test1.hashint4, test2.j, test2.i, test2.ct"
" Merge Cond: (test2.ct = test1.ctid)"
" -> Sort (cost=248955.55..253955.30 rows=1999900 width=14) (actual
time=7513.539..10361.598 rows=999901 loops=1)"
" Output: test2.j, test2.i, test2.ct"
" Sort Key: test2.ct"
" Sort Method: external sort Disk: 23456kB"
" -> Seq Scan on test2 (cost=0.00..16456.80 rows=1999900
width=14) (actual time=0.551..2234.130 rows=1000000 loops=1)"
" Output: test2.j, test2.i, test2.ct"
" -> Sort (cost=748.14..773.14 rows=10000 width=20) (actual
time=54.020..2193.688 rows=999901 loops=1)"
" Output: test1.i, test1.hashint4, test1.ctid"
" Sort Key: test1.ctid"
" Sort Method: quicksort Memory: 960kB"
" -> Seq Scan on test1 (cost=0.00..83.75 rows=10000 width=20)
(actual time=0.030..26.205 rows=10000 loops=1)"
" Output: test1.i, test1.hashint4, test1.ctid"
"Total runtime: 21635.881 ms"
------------
pasman
