Index Skip Scan - attempting to evalutate patch - Mailing list pgsql-hackers

Hello

This is my first posting to hackers so sorry if I'm taking up valuable time.

I'm currently migrating a packaged application which supported oracle and sql server to PostgreSQL.

Something that I've identified as hurting the performance a lot is loose index scanning. I don't have access to the application SQL , so all I can try and do is mitigate through indexes. There are ~4000 tables in the application schema, and ~6000 indices.

Some plans are clearly worse than I would expect - because there are lots of index(a,b,c) and select where a= and c=. 

In an attempt to see if the putative skip scan changes will be beneficial on our real world data sets, I've been attempting to build and run pgsql from github with the v20- patch applied.

If I build without the patch, I get a running server, and can execute whatever queries I want.

If I apply the latest patch (which says 1 of 2? - maybe I'm missing a part of the patch?), I apply with

$ patch -p1 <../v20-0001-Index-skip-scan.patch

patching file contrib/bloom/blutils.c

patching file doc/src/sgml/config.sgml

patching file doc/src/sgml/indexam.sgml

patching file doc/src/sgml/indices.sgml

patching file src/backend/access/brin/brin.c

patching file src/backend/access/gin/ginutil.c

patching file src/backend/access/gist/gist.c

patching file src/backend/access/hash/hash.c

patching file src/backend/access/index/indexam.c

patching file src/backend/access/nbtree/nbtree.c

patching file src/backend/access/nbtree/nbtsearch.c

patching file src/backend/access/spgist/spgutils.c

patching file src/backend/commands/explain.c

patching file src/backend/executor/nodeIndexonlyscan.c

patching file src/backend/executor/nodeIndexscan.c

patching file src/backend/nodes/copyfuncs.c

patching file src/backend/nodes/outfuncs.c

patching file src/backend/nodes/readfuncs.c

patching file src/backend/optimizer/path/costsize.c

patching file src/backend/optimizer/path/pathkeys.c

patching file src/backend/optimizer/plan/createplan.c

patching file src/backend/optimizer/plan/planagg.c

patching file src/backend/optimizer/plan/planner.c

patching file src/backend/optimizer/util/pathnode.c

patching file src/backend/optimizer/util/plancat.c

patching file src/backend/utils/misc/guc.c

patching file src/backend/utils/misc/postgresql.conf.sample

patching file src/include/access/amapi.h

patching file src/include/access/genam.h

patching file src/include/access/nbtree.h

patching file src/include/nodes/execnodes.h

patching file src/include/nodes/pathnodes.h

patching file src/include/nodes/plannodes.h

patching file src/include/optimizer/cost.h

patching file src/include/optimizer/pathnode.h

patching file src/include/optimizer/paths.h

patching file src/test/regress/expected/create_index.out

patching file src/test/regress/expected/select_distinct.out

patching file src/test/regress/expected/sysviews.out

patching file src/test/regress/sql/create_index.sql

patching file src/test/regress/sql/select_distinct.sql

This will 'make' and 'make install' cleanly.

When I run the server, I can log in but the postgres processes associated with my psql session crashes SIGSEGV in many cases, for example when using \d:

psql (12beta2)

Type "help" for help.

db1=> show enable_indexskipscan;

enable_indexskipscan

----------------------

on

(1 row)

db1=> \d

psql: server closed the connection unexpectedly

        This probably means the server terminated abnormally

        before or while processing the request.

The connection to the server was lost. Attempting reset: Failed.

!> \q

I got a backtrace out of the process:

(gdb) backtrace

#0  MemoryContextAllocZeroAligned (context=0x0, size=size@entry=80) at mcxt.c:864

#1  0x000000000067d2d4 in get_eclass_for_sort_expr (root=root@entry=0x22ecb10, expr=expr@entry=0x22ee280, nullable_relids=nullable_relids@entry=0x0, opfamilies=0x22ff530,

    opcintype=opcintype@entry=19, collation=collation@entry=950, sortref=<optimized out>, rel=0x0, create_it=true) at equivclass.c:704

#2  0x0000000000686d9e in make_pathkey_from_sortinfo (root=root@entry=0x22ecb10, expr=expr@entry=0x22ee280, nullable_relids=nullable_relids@entry=0x0, opfamily=1994, opcintype=19,

    collation=950, reverse_sort=false, nulls_first=false, sortref=1, rel=0x0, create_it=true) at pathkeys.c:228

#3  0x0000000000686eb7 in make_pathkey_from_sortop (root=root@entry=0x22ecb10, expr=0x22ee280, nullable_relids=0x0, ordering_op=660, nulls_first=<optimized out>, sortref=1,

    create_it=true) at pathkeys.c:271

#4  0x0000000000687a4a in make_pathkeys_for_sortclauses (root=root@entry=0x22ecb10, sortclauses=<optimized out>, tlist=tlist@entry=0x22ee2f0) at pathkeys.c:1099

#5  0x0000000000694588 in standard_qp_callback (root=0x22ecb10, extra=<optimized out>) at planner.c:3635

#6  0x0000000000693024 in query_planner (root=root@entry=0x22ecb10, qp_callback=qp_callback@entry=0x6944e0 <standard_qp_callback>, qp_extra=qp_extra@entry=0x7ffe6fe2b8e0)

    at planmain.c:207

#7  0x00000000006970e0 in grouping_planner (root=root@entry=0x22ecb10, inheritance_update=inheritance_update@entry=false, tuple_fraction=<optimized out>, tuple_fraction@entry=0)

    at planner.c:2048

#8  0x000000000069978d in subquery_planner (glob=glob@entry=0x22e43c0, parse=parse@entry=0x22e3f30, parent_root=parent_root@entry=0x0, hasRecursion=hasRecursion@entry=false,

    tuple_fraction=tuple_fraction@entry=0) at planner.c:1012

#9  0x000000000069a7b6 in standard_planner (parse=0x22e3f30, cursorOptions=256, boundParams=<optimized out>) at planner.c:406

#10 0x000000000073ceac in pg_plan_query (querytree=querytree@entry=0x22e3f30, cursorOptions=cursorOptions@entry=256, boundParams=boundParams@entry=0x0) at postgres.c:878

#11 0x000000000073cf86 in pg_plan_queries (querytrees=<optimized out>, cursorOptions=cursorOptions@entry=256, boundParams=boundParams@entry=0x0) at postgres.c:968

#12 0x000000000073d399 in exec_simple_query (

    query_string=0x222a9a0 "SELECT n.nspname as \"Schema\",\n  c.relname as \"Name\",\n  CASE c.relkind WHEN 'r' THEN 'table' WHEN 'v' THEN 'view' WHEN 'm' THEN 'materialized view' WHEN 'i' THEN 'index' WHEN 'S' THEN 'sequence' WHEN '"...) at postgres.c:1143

#13 0x000000000073ef5a in PostgresMain (argc=<optimized out>, argv=argv@entry=0x2255440, dbname=<optimized out>, username=<optimized out>) at postgres.c:4249

#14 0x00000000006cfaf6 in BackendRun (port=0x224e220, port=0x224e220) at postmaster.c:4431

#15 BackendStartup (port=0x224e220) at postmaster.c:4122

#16 ServerLoop () at postmaster.c:1704

#17 0x00000000006d09d0 in PostmasterMain (argc=argc@entry=3, argv=argv@entry=0x2224c50) at postmaster.c:1377

#18 0x00000000004820c4 in main (argc=3, argv=0x2224c50) at main.c:228

With the skip scan v20 patch installed, I tried to set enable_indexskipscan=off, but this did not resolve the crash.

Also, if I try to explain a select:

db1=> explain analyze select cmpy,bal,fncl_val from t1 where cmpy='04' and bal='CO';

psql: ERROR:  variable not found in subplan target list

I'm quite prepared to be told I've miscompiled or missed something really obvious and for that I apologize in advance.  I'm really keen to get involved with testing this patch/feature ahead of release.