Re: [HACKERS] [PATCH] Incremental sort

Hi!

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_
> schema,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.

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