Re: [HACKERS] [PATCH] Incremental sort

On Wed, Mar 21, 2018 at 2:32 PM, Alexander Korotkov <
a(dot)korotkov(at)postgrespro(dot)ru> wrote:

> On Wed, Mar 21, 2018 at 2:30 PM, Darafei "Komяpa" Praliaskouski <
> me(at)komzpa(dot)net> wrote:
>
>> on a PostGIS system tuned for preferring parallelism heavily (
>> min_parallel_table_scan_size=10kB) we experience issues with QGIS table
>> discovery query with this patch:
>>
>> Failing query is:
>> [local] gis(at)gis=# SELECT l.f_table_name,l.f_table_schem
>> a,l.f_geometry_column,upper(l.type),l.srid,l.coord_dimension
>> ,c.relkind,obj_description(c.oid) FROM geometry_columns l,pg_class
>> c,pg_namespace n WHERE c.relname=l.f_table_name AND l.f_table_schema=n.
>> nspname AND n.oid=c.relnamespace AND has_schema_privilege(n.nspname,'usage')
>> AND has_table_privilege('"'||n.nspname||'"."'||c.relname||'"','select')
>> AND l.f_table_schema='public' ORDER BY n.nspname,c.relname,l.f_geometry_column;
>>
>> ERROR: XX000: badly formatted node string "INCREMENTALSORT :startup_cost
>> 37"...
>> CONTEXT: parallel worker
>> LOCATION: parseNodeString, readfuncs.c:2693
>> Time: 42,052 ms
>>
>>
>> Query plan:
>>
>>
>> QUERY PLAN
>>
>>
>> ────────────────────────────────────────────────────────────
>> ────────────────────────────────────────────────────────────
>> ────────────────────────────────────────────────────────────
>> ────────────────────────────────────────────────────────────
>> ──────────────────
>> Sort (cost=38717.21..38717.22 rows=1 width=393)
>> Sort Key: c_1.relname, a.attname
>> -> Nested Loop (cost=36059.35..38717.20 rows=1 width=393)
>> -> Index Scan using pg_namespace_nspname_index on pg_namespace
>> n (cost=0.28..2.30 rows=1 width=68)
>> Index Cond: (nspname = 'public'::name)
>> Filter: has_schema_privilege((nspname)::text,
>> 'usage'::text)
>> -> Nested Loop (cost=36059.08..38714.59 rows=1 width=407)
>> -> Nested Loop Left Join (cost=36058.65..38712.12 rows=1
>> width=334)
>> Join Filter: ((s_2.connamespace = n_1.oid) AND
>> (a.attnum = ANY (s_2.conkey)))
>> -> Nested Loop Left Join (cost=36058.51..38711.94
>> rows=1 width=298)
>> Join Filter: ((s_1.connamespace = n_1.oid) AND
>> (a.attnum = ANY (s_1.conkey)))
>> -> Nested Loop (cost=36058.38..38711.75
>> rows=1 width=252)
>> Join Filter: (a.atttypid = t.oid)
>> -> Gather Merge
>> (cost=36057.95..38702.65 rows=444 width=256)
>> Workers Planned: 10
>> -> Merge Left Join
>> (cost=35057.76..37689.01 rows=44 width=256)
>> Merge Cond: ((n_1.oid =
>> s.connamespace) AND (c_1.oid = s.conrelid))
>> Join Filter: (a.attnum = ANY
>> (s.conkey))
>> -> Incremental Sort
>> (cost=37687.19..37687.30 rows=44 width=210)
>> Sort Key: n_1.oid,
>> c_1.oid
>> Presorted Key: n_1.oid
>> -> Nested Loop
>> (cost=34837.25..37685.99 rows=44 width=210)
>> -> Merge Join
>> (cost=34836.82..34865.99 rows=9 width=136)
>> Merge Cond:
>> (c_1.relnamespace = n_1.oid)
>> -> Sort
>> (cost=34834.52..34849.05 rows=5814 width=72)
>> Sort
>> Key: c_1.relnamespace
>> ->
>> Parallel Seq Scan on pg_class c_1 (cost=0.00..34470.99 rows=5814 width=72)
>>
>> Filter: ((relname <> 'raster_columns'::name) AND (NOT
>> pg_is_other_temp_schema(relnamespace)) AND has_table_privilege(oid,
>> 'SELECT'::text) AND (relkind = ANY ('{r,v,m,f,p}'::"char"[])))
>> -> Sort
>> (cost=2.30..2.31 rows=1 width=68)
>> Sort
>> Key: n_1.oid
>> ->
>> Index Scan using pg_namespace_nspname_index on pg_namespace n_1
>> (cost=0.28..2.29 rows=1 width=68)
>>
>> Index Cond: (nspname = 'public'::name)
>> -> Index Scan
>> using pg_attribute_relid_attnum_index on pg_attribute a
>> (cost=0.43..200.52 rows=11281 width=78)
>> Index Cond:
>> (attrelid = c_1.oid)
>> Filter:
>> (NOT attisdropped)
>> -> Sort (cost=1.35..1.35
>> rows=1 width=77)
>> Sort Key:
>> s.connamespace, s.conrelid
>> -> Seq Scan on
>> pg_constraint s (cost=0.00..1.34 rows=1 width=77)
>> Filter: (consrc
>> ~~* '%geometrytype(% = %'::text)
>> -> Materialize (cost=0.42..2.45 rows=1
>> width=4)
>> -> Index Scan using
>> pg_type_typname_nsp_index on pg_type t (cost=0.42..2.44 rows=1 width=4)
>> Index Cond: (typname =
>> 'geometry'::name)
>> -> Index Scan using
>> pg_constraint_conrelid_index on pg_constraint s_1 (cost=0.14..0.16 rows=1
>> width=77)
>> Index Cond: (conrelid = c_1.oid)
>> Filter: (consrc ~~* '%ndims(% = %'::text)
>> -> Index Scan using pg_constraint_conrelid_index on
>> pg_constraint s_2 (cost=0.14..0.16 rows=1 width=77)
>> Index Cond: (conrelid = c_1.oid)
>> Filter: (consrc ~~* '%srid(% = %'::text)
>> -> Index Scan using pg_class_relname_nsp_index on pg_class
>> c (cost=0.42..2.46 rows=1 width=73)
>> Index Cond: ((relname = c_1.relname) AND
>> (relnamespace = n.oid))
>> Filter: has_table_privilege((((('"'::text ||
>> (n.nspname)::text) || '"."'::text) || (relname)::text) || '"'::text),
>> 'select'::text)
>> (51 rows)
>>
>
> Thank you for pointing. I'll try to reproduce this issue and fix it.
>

I found that Darafei used build made using incremental-sort-7.patch. That
version contained bug in incremental sort node deserialization.
Modern patch versions doesn't contain that bug.

I've checked that it works.

create table t (i int, value float8);
insert into t select i%1000, random() from generate_series(1,1000000) i;
set force_parallel_mode = on;

# explain select count(*) from (select * from (select * from t order by i)
x order by i, value) y;
QUERY PLAN
------------------------------------------------------------------------------------
Gather (cost=254804.94..254805.05 rows=1 width=8)
Workers Planned: 1
Single Copy: true
-> Aggregate (cost=253804.94..253804.95 rows=1 width=8)
-> Incremental Sort (cost=132245.97..241304.94 rows=1000000
width=12)
Sort Key: t.i, t.value
Presorted Key: t.i
-> Sort (cost=132154.34..134654.34 rows=1000000 width=12)
Sort Key: t.i
-> Seq Scan on t (cost=0.00..15406.00 rows=1000000
width=12)
(10 rows)

# select count(*) from (select * from (select * from t order by i) x order
by i, value) y;
count
---------
1000000
(1 row)

BTW, patch had conflicts with master. Please, find rebased version
attached.

------
Alexander Korotkov
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company