Re: [HACKERS] [PATCH] Incremental sort

Hi,

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@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)

Darafei Praliaskouski,
GIS Engineer / Juno Minsk