2008/12/2 Hitoshi Harada <[EMAIL PROTECTED]>: > sample=# EXPLAIN ANALYZE SELECT LEAD(timestamp) OVER (ORDER BY id) > FROM bigtable LIMIT 1; > > QUERY PLAN > > ---------------------------------------------------------------------------------------------- > --------------------------------------------------- > Limit (cost=0.00..0.04 rows=1 width=12) (actual time=0.038..0.039 > rows=1 loops=1) > -> Window (cost=0.00..386612.13 rows=10000000 width=12) (actual > time=0.036..0.036 rows=1 > loops=1) > -> Index Scan using bigtable_pkey on bigtable > (cost=0.00..286612.13 rows=10000000 w > idth=12) (actual time=0.018..0.021 rows=2 loops=1) > Total runtime: 0.071 ms > (4 rows) > > > shows quite good result. Great work. >
After more playing with the new patch, I found worse results. sample=# explain analyze select id, row_number() OVER (order by id) from bigtable order by id; QUERY PLAN ---------------------------------------------------------------------------------------------- ------------------------------------------------------- Window (cost=0.00..361612.13 rows=10000000 width=4) (actual time=0.064..105414.522 rows=1000 0000 loops=1) -> Index Scan using bigtable_pkey on bigtable (cost=0.00..286612.13 rows=10000000 width=4 ) (actual time=0.056..16836.341 rows=10000000 loops=1) Total runtime: 114650.074 ms (3 rows) sample=# explain analyze select id,LAG(timestamp,1) over (order by id) from bigtable order by id; QUERY PLAN ---------------------------------------------------------------------------------------------- -------------------------------------------------------- Window (cost=0.00..411612.13 rows=10000000 width=12) (actual time=0.065..122583.331 rows=100 00000 loops=1) -> Index Scan using bigtable_pkey on bigtable (cost=0.00..286612.13 rows=10000000 width=1 2) (actual time=0.056..18066.829 rows=10000000 loops=1) Total runtime: 132770.399 ms (3 rows) The earlier patch results are here: http://archives.postgresql.org/pgsql-hackers/2008-11/msg01121.php row_number(): 44s/114s lag(): 79s/132s I don't understand the new patch totally, and I know the row_number() optimization is in progress, but even lag() is quite worse. Maybe tuplestore read pointer's heavy uses cause these. Regards, -- Hitoshi Harada -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
