pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedtidscan not work ? Pg 8.4.5 + WinXP
4+ messages / 3 participants
[nested] [flat]
* tidscan not work ? Pg 8.4.5 + WinXP
@ 2010-11-30 11:43 pasman pasmański <pasman.p@gmail.com>
2010-11-30 16:44 ` Re: tidscan not work ? Pg 8.4.5 + WinXP Kevin Grittner <Kevin.Grittner@wicourts.gov>
0 siblings, 1 reply; 4+ messages in thread
From: pasman pasmański @ 2010-11-30 11:43 UTC (permalink / raw)
To: 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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: tidscan not work ? Pg 8.4.5 + WinXP
2010-11-30 11:43 tidscan not work ? Pg 8.4.5 + WinXP pasman pasmański <pasman.p@gmail.com>
@ 2010-11-30 16:44 ` Kevin Grittner <Kevin.Grittner@wicourts.gov>
2010-11-30 16:49 ` Re: tidscan not work ? Pg 8.4.5 + WinXP Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 4+ messages in thread
From: Kevin Grittner @ 2010-11-30 16:44 UTC (permalink / raw)
To: pasman pasma*ski <pasman.p@gmail.com>; pgsql-performance
pasman pasma*ski<pasman.p@gmail.com> wrote:
> How to use tid scans?
Write a query where they are the fastest way to retrieve the data,
and make sure your PostgreSQL installation is properly configured.
> This below not works :-( Always is used merge join.
> SELECT * FROM test1 join test2 on(test1.ctid=test2.ct)
You're reading through the entirety of two tables matching rows
between them. What makes you think random access would be faster
than sequential? If all this data is cached, then maybe random
access could win, but you would need to configure your PostgreSQL to
expect that.
Have you read this page:
http://wiki.postgresql.org/wiki/Tuning_Your_PostgreSQL_Server
-Kevin
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: tidscan not work ? Pg 8.4.5 + WinXP
2010-11-30 11:43 tidscan not work ? Pg 8.4.5 + WinXP pasman pasmański <pasman.p@gmail.com>
2010-11-30 16:44 ` Re: tidscan not work ? Pg 8.4.5 + WinXP Kevin Grittner <Kevin.Grittner@wicourts.gov>
@ 2010-11-30 16:49 ` Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 4+ messages in thread
From: Tom Lane @ 2010-11-30 16:49 UTC (permalink / raw)
To: Kevin Grittner <Kevin.Grittner@wicourts.gov>; +Cc: pasman pasma*ski <pasman.p@gmail.com>; pgsql-performance
"Kevin Grittner" <Kevin.Grittner@wicourts.gov> writes:
> pasman pasma*ski<pasman.p@gmail.com> wrote:
>> This below not works :-( Always is used merge join.
>> SELECT * FROM test1 join test2 on(test1.ctid=test2.ct)
> You're reading through the entirety of two tables matching rows
> between them. What makes you think random access would be faster
> than sequential?
FWIW, it isn't going to happen anyway, because the TID scan mechanism
doesn't support scanning based on a join condition. That hasn't gotten
to the top of the to-do list because the use case is almost vanishingly
small. ctids generally aren't stable enough for it to be useful to
store references to one table's ctids in another table.
regards, tom lane
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: tidscan not work ? Pg 8.4.5 + WinXP
@ 2010-12-01 11:38 pasman pasmański <pasman.p@gmail.com>
0 siblings, 0 replies; 4+ messages in thread
From: pasman pasmański @ 2010-12-01 11:38 UTC (permalink / raw)
To: pgsql-performance
>FWIW, it isn't going to happen anyway, because the TID scan mechanism
>doesn't support scanning based on a join condition. That hasn't gotten
>to the top of the to-do list because the use case is almost vanishingly
>small. ctids generally aren't stable enough for it to be useful to
>store references to one table's ctids in another table.
Thanks for explanation.
--
------------
pasman
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2010-12-01 11:38 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2010-11-30 11:43 tidscan not work ? Pg 8.4.5 + WinXP pasman pasmański <pasman.p@gmail.com>
2010-11-30 16:44 ` Kevin Grittner <Kevin.Grittner@wicourts.gov>
2010-11-30 16:49 ` Tom Lane <tgl@sss.pgh.pa.us>
2010-12-01 11:38 Re: tidscan not work ? Pg 8.4.5 + WinXP pasman pasmański <pasman.p@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox