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
--
Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
