Effect of the WindowAgg on the Nested Loop - Mailing list pgsql-performance

Greetings.

I've been playing with a small query that I've been asked to optimize
and noticed a strange (for me) effect.
Query uses this table:

                                   Table "clc06_tiles"
   Column   |         Type          |
Modifiers
------------+-----------------------+-----------------------------------------------------------
 geometry   | geometry              |
 code_06    | character varying(3)  |
 gid        | bigint                | not null default
nextval('clc06_tiles_gid_seq'::regclass)
Indexes:
    "clc06_tiles_pkey" PRIMARY KEY, btree (gid)
    "i_clc06_tiles_geometry" gist (geometry)
Check constraints:
    "enforce_dims_geom" CHECK (st_ndims(geometry) = 2)
    "enforce_geotype_geom" CHECK (geometrytype(geometry) =
'MULTIPOLYGON'::text OR geometrytype(geometry) = 'POLYGON'::text OR
geometry IS NULL)
    "enforce_srid_geom" CHECK (st_srid(geometry) = 3035)

and this function:
CREATE OR REPLACE FUNCTION my_trans(x1 float8, y1 float8, x2 float8,
y2 float8) RETURNS geometry AS $my_trans$
    SELECT st_Transform(
        st_GeomFromText('LINESTRING('||x1::text||' '||y1::text||
                         ', '||x2::text||' '||y2::text||')',4326),3035);
$my_trans$ LANGUAGE sql IMMUTABLE STRICT;

and these constants:
\set x1 4.56
\set y1 52.54
\set x2 5.08
\set y2 53.34


Original query looks like this ( http://explain.depesz.com/s/pzv ):

SELECT n, i.*, st_NumGeometries(i.geom)
  FROM (
    SELECT a.code_06 as code_06,
           st_Multi(st_Intersection(a.geometry,
my_trans(:x1,:y1,:x2,:y2))) as geom
      FROM clc06_tiles a
     WHERE st_Intersects(a.geometry, my_trans(:x1,:y1,:x2,:y2))) i
  JOIN generate_series(1,10) n ON n <= st_NumGeometries(i.geom);


After a while I added row_number() to the inner part (
http://explain.depesz.com/s/hfs ):

SELECT n, i.*, st_NumGeometries(i.geom)
  FROM (
    SELECT row_number() OVER () AS rn, a.code_06 as code_06,
           st_Multi(st_Intersection(a.geometry,
my_trans(:x1,:y1,:x2,:y2))) as geom
      FROM clc06_tiles a
     WHERE st_Intersects(a.geometry, my_trans(:x1,:y1,:x2,:y2))) i
  JOIN generate_series(1,10) n ON n <= st_NumGeometries(i.geom);


It was really surprising to see a "side" effect of 8x performance boost.
The only difference I can see is an extra WindowAgg step in the second variant.



Could you kindly explain how WindowAgg node affects the overall
performance, please?



PostgreSQL 9.2.1 on x86_64-unknown-linux-gnu, compiled by gcc
(Ubuntu/Linaro 4.6.1-9ubuntu3) 4.6.1, 64-bit
 archive_command            | (disabled)             | configuration file
 bgwriter_delay             | 100ms                  | configuration file
 bgwriter_lru_maxpages      | 200                    | configuration file
 checkpoint_segments        | 30                     | configuration file
 default_text_search_config | pg_catalog.english     | configuration file
 effective_cache_size       | 3GB                    | configuration file
 listen_addresses           | *                      | configuration file
 log_checkpoints            | on                     | configuration file
 log_connections            | on                     | configuration file
 log_destination            | csvlog                 | configuration file
 log_disconnections         | on                     | configuration file
 log_lock_waits             | on                     | configuration file
 log_min_duration_statement | 100ms                  | configuration file
 log_rotation_age           | 1d                     | configuration file
 log_temp_files             | 20MB                   | configuration file
 log_timezone               | UTC                    | configuration file
 logging_collector          | on                     | configuration file
 maintenance_work_mem       | 64MB                   | configuration file
 max_connections            | 100                    | configuration file
 max_stack_depth            | 2MB                    | environment variable
 max_wal_senders            | 2                      | configuration file
 port                       | 5432                   | configuration file
 shared_buffers             | 768MB                  | configuration file
 temp_buffers               | 32MB                   | configuration file
 TimeZone                   | UTC                    | configuration file
 wal_level                  | hot_standby            | configuration file
 work_mem                   | 8MB                    | configuration file


--
Victor Y. Yegorov