Re: [HACKERS] [PATCH] Incremental sort - Mailing list pgsql-hackers
From | Alexander Korotkov |
---|---|
Subject | Re: [HACKERS] [PATCH] Incremental sort |
Date | |
Msg-id | CAPpHfdvQJPG0_4POaNiWi-SWXtzY5aDe03+WB-0HDgyzaTjhSA@mail.gmail.com Whole thread Raw |
In response to | Re: [HACKERS] [PATCH] Incremental sort (Darafei "Komяpa" Praliaskouski <me@komzpa.net>) |
Responses |
Re: [HACKERS] [PATCH] Incremental sort
(Alexander Korotkov <a.korotkov@postgrespro.ru>)
|
List | pgsql-hackers |
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: [local] gis@gis=# SELECT l.f_table_name,l.f_table_
Failing query is: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.oidPresorted 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
Alexander Korotkov
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company
pgsql-hackers by date: