tidscan not work ? Pg 8.4.5 + WinXP

From: pasman pasmański <pasman(dot)p(at)gmail(dot)com>
To: pgsql-performance(at)postgresql(dot)org
Subject: tidscan not work ? Pg 8.4.5 + WinXP
Date: 2010-11-30 11:43:05
Message-ID: AANLkTimQhTkwM7_DU69XtKVNKqXTZebofzn1qysdOYBw@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance

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

Responses

Browse pgsql-performance by date

  From Date Subject
Next Message Dimitri Fontaine 2010-11-30 11:45:57 Re: Simple database, multiple instances?
Previous Message bakkiya 2010-11-30 11:38:00 postgresql statements are waiting