Skip site navigation (1) Skip section navigation (2)

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 (view raw or flat)
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

pgsql-performance by date

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

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group