Re: Effect of the WindowAgg on the Nested Loop

On Tue, Jan 22, 2013 at 3:57 PM, Виктор Егоров <vyegorov(at)gmail(dot)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