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

On Tue, Jan 22, 2013 at 3:57 PM, Виктор Егоров <vyegorov@gmail.com> wrote:
> 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?

Apologies for resurrecting an old thread, but I just came across this
post while doing some research and I don't see any responses.

This seems like a mighty interesting example.  I'm not sure what's
going on here, but let me guess.  I think that the WindowAgg is
forcing some operation - detoasting, maybe? - to happen under the
materialize node.  As a result, it only gets done once.  But in the
other plan, the detoast happens at the nested loop level, above the
materialize node, and therefore it happens 10x instead of 1x.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company