Re: Windowing Function Patch Review -> Standard Conformance

2008/12/2 Hitoshi Harada <umi(dot)tanuki(at)gmail(dot)com>:
> 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