pg.ddx.io pgsql-committers@postgresql.org mailing list archivehelp / color / mirror / Atom feed
pgsql: Fix index-only scan plans when not all index columns can be retu 6+ messages / 1 participants [nested] [flat]
* pgsql: Fix index-only scan plans when not all index columns can be retu @ 2022-01-01 21:12 Tom Lane <tgl@sss.pgh.pa.us> 0 siblings, 0 replies; 6+ messages in thread From: Tom Lane @ 2022-01-01 21:12 UTC (permalink / raw) To: pgsql-committers@lists.postgresql.org Fix index-only scan plans when not all index columns can be returned. If an index has both returnable and non-returnable columns, and one of the non-returnable columns is an expression using a Var that is in a returnable column, then a query returning that expression could result in an index-only scan plan that attempts to read the non-returnable column, instead of recomputing the expression from the returnable column as intended. To fix, redefine the "indextlist" list of an IndexOnlyScan plan node as containing null Consts in place of any non-returnable columns. This solves the problem by preventing setrefs.c from falsely matching to such entries. The executor is happy since it only cares about the exposed types of the entries, and ruleutils.c doesn't care because a correct plan won't reference those entries. I considered some other ways to prevent setrefs.c from doing the wrong thing, but this way seems good since (a) it allows a very localized fix, (b) it makes the indextlist structure more compact in many cases, and (c) the indextlist is now a more faithful representation of what the index AM will actually produce, viz. nulls for any non-returnable columns. This is easier to hit since we introduced included columns, but it's possible to construct failing examples without that, as per the added regression test. Hence, back-patch to all supported branches. Per bug #17350 from Louis Jachiet. Discussion: https://postgr.es/m/17350-b5bdcf476e5badbb@postgresql.org Branch ------ REL_13_STABLE Details ------- https://git.postgresql.org/pg/commitdiff/45ae4271410bfba0daba8e71eeb631c6f91088bb Modified Files -------------- src/backend/optimizer/plan/createplan.c | 53 +++++++++++++++++++++++++++++++-- src/include/nodes/plannodes.h | 3 +- src/test/regress/expected/gist.out | 28 +++++++++++++++++ src/test/regress/sql/gist.sql | 11 +++++++ 4 files changed, 92 insertions(+), 3 deletions(-) ^ permalink raw reply [nested|flat] 6+ messages in thread
* pgsql: Fix index-only scan plans when not all index columns can be retu @ 2022-01-01 21:12 Tom Lane <tgl@sss.pgh.pa.us> 0 siblings, 0 replies; 6+ messages in thread From: Tom Lane @ 2022-01-01 21:12 UTC (permalink / raw) To: pgsql-committers@lists.postgresql.org Fix index-only scan plans when not all index columns can be returned. If an index has both returnable and non-returnable columns, and one of the non-returnable columns is an expression using a Var that is in a returnable column, then a query returning that expression could result in an index-only scan plan that attempts to read the non-returnable column, instead of recomputing the expression from the returnable column as intended. To fix, redefine the "indextlist" list of an IndexOnlyScan plan node as containing null Consts in place of any non-returnable columns. This solves the problem by preventing setrefs.c from falsely matching to such entries. The executor is happy since it only cares about the exposed types of the entries, and ruleutils.c doesn't care because a correct plan won't reference those entries. I considered some other ways to prevent setrefs.c from doing the wrong thing, but this way seems good since (a) it allows a very localized fix, (b) it makes the indextlist structure more compact in many cases, and (c) the indextlist is now a more faithful representation of what the index AM will actually produce, viz. nulls for any non-returnable columns. This is easier to hit since we introduced included columns, but it's possible to construct failing examples without that, as per the added regression test. Hence, back-patch to all supported branches. Per bug #17350 from Louis Jachiet. Discussion: https://postgr.es/m/17350-b5bdcf476e5badbb@postgresql.org Branch ------ REL_14_STABLE Details ------- https://git.postgresql.org/pg/commitdiff/cabea571d11e020c8c72dfb8ddced441745d7238 Modified Files -------------- src/backend/optimizer/plan/createplan.c | 53 +++++++++++++++++++++++++++++++-- src/include/nodes/plannodes.h | 3 +- src/test/regress/expected/gist.out | 28 +++++++++++++++++ src/test/regress/sql/gist.sql | 11 +++++++ 4 files changed, 92 insertions(+), 3 deletions(-) ^ permalink raw reply [nested|flat] 6+ messages in thread
* pgsql: Fix index-only scan plans when not all index columns can be retu @ 2022-01-01 21:12 Tom Lane <tgl@sss.pgh.pa.us> 0 siblings, 0 replies; 6+ messages in thread From: Tom Lane @ 2022-01-01 21:12 UTC (permalink / raw) To: pgsql-committers@lists.postgresql.org Fix index-only scan plans when not all index columns can be returned. If an index has both returnable and non-returnable columns, and one of the non-returnable columns is an expression using a Var that is in a returnable column, then a query returning that expression could result in an index-only scan plan that attempts to read the non-returnable column, instead of recomputing the expression from the returnable column as intended. To fix, redefine the "indextlist" list of an IndexOnlyScan plan node as containing null Consts in place of any non-returnable columns. This solves the problem by preventing setrefs.c from falsely matching to such entries. The executor is happy since it only cares about the exposed types of the entries, and ruleutils.c doesn't care because a correct plan won't reference those entries. I considered some other ways to prevent setrefs.c from doing the wrong thing, but this way seems good since (a) it allows a very localized fix, (b) it makes the indextlist structure more compact in many cases, and (c) the indextlist is now a more faithful representation of what the index AM will actually produce, viz. nulls for any non-returnable columns. This is easier to hit since we introduced included columns, but it's possible to construct failing examples without that, as per the added regression test. Hence, back-patch to all supported branches. Per bug #17350 from Louis Jachiet. Discussion: https://postgr.es/m/17350-b5bdcf476e5badbb@postgresql.org Branch ------ REL_12_STABLE Details ------- https://git.postgresql.org/pg/commitdiff/f789b7732e0b535fe2588879a60f536abfb6c6e0 Modified Files -------------- src/backend/optimizer/plan/createplan.c | 53 +++++++++++++++++++++++++++++++-- src/include/nodes/plannodes.h | 3 +- src/test/regress/expected/gist.out | 28 +++++++++++++++++ src/test/regress/sql/gist.sql | 11 +++++++ 4 files changed, 92 insertions(+), 3 deletions(-) ^ permalink raw reply [nested|flat] 6+ messages in thread
* pgsql: Fix index-only scan plans when not all index columns can be retu @ 2022-01-01 21:12 Tom Lane <tgl@sss.pgh.pa.us> 0 siblings, 0 replies; 6+ messages in thread From: Tom Lane @ 2022-01-01 21:12 UTC (permalink / raw) To: pgsql-committers@lists.postgresql.org Fix index-only scan plans when not all index columns can be returned. If an index has both returnable and non-returnable columns, and one of the non-returnable columns is an expression using a Var that is in a returnable column, then a query returning that expression could result in an index-only scan plan that attempts to read the non-returnable column, instead of recomputing the expression from the returnable column as intended. To fix, redefine the "indextlist" list of an IndexOnlyScan plan node as containing null Consts in place of any non-returnable columns. This solves the problem by preventing setrefs.c from falsely matching to such entries. The executor is happy since it only cares about the exposed types of the entries, and ruleutils.c doesn't care because a correct plan won't reference those entries. I considered some other ways to prevent setrefs.c from doing the wrong thing, but this way seems good since (a) it allows a very localized fix, (b) it makes the indextlist structure more compact in many cases, and (c) the indextlist is now a more faithful representation of what the index AM will actually produce, viz. nulls for any non-returnable columns. This is easier to hit since we introduced included columns, but it's possible to construct failing examples without that, as per the added regression test. Hence, back-patch to all supported branches. Per bug #17350 from Louis Jachiet. Discussion: https://postgr.es/m/17350-b5bdcf476e5badbb@postgresql.org Branch ------ REL_11_STABLE Details ------- https://git.postgresql.org/pg/commitdiff/e3a4c798166797a055fc83413b182953cd76a951 Modified Files -------------- src/backend/optimizer/plan/createplan.c | 53 +++++++++++++++++++++++++++++++-- src/include/nodes/plannodes.h | 3 +- src/test/regress/expected/gist.out | 28 +++++++++++++++++ src/test/regress/sql/gist.sql | 11 +++++++ 4 files changed, 92 insertions(+), 3 deletions(-) ^ permalink raw reply [nested|flat] 6+ messages in thread
* pgsql: Fix index-only scan plans when not all index columns can be retu @ 2022-01-01 21:12 Tom Lane <tgl@sss.pgh.pa.us> 0 siblings, 0 replies; 6+ messages in thread From: Tom Lane @ 2022-01-01 21:12 UTC (permalink / raw) To: pgsql-committers@lists.postgresql.org Fix index-only scan plans when not all index columns can be returned. If an index has both returnable and non-returnable columns, and one of the non-returnable columns is an expression using a Var that is in a returnable column, then a query returning that expression could result in an index-only scan plan that attempts to read the non-returnable column, instead of recomputing the expression from the returnable column as intended. To fix, redefine the "indextlist" list of an IndexOnlyScan plan node as containing null Consts in place of any non-returnable columns. This solves the problem by preventing setrefs.c from falsely matching to such entries. The executor is happy since it only cares about the exposed types of the entries, and ruleutils.c doesn't care because a correct plan won't reference those entries. I considered some other ways to prevent setrefs.c from doing the wrong thing, but this way seems good since (a) it allows a very localized fix, (b) it makes the indextlist structure more compact in many cases, and (c) the indextlist is now a more faithful representation of what the index AM will actually produce, viz. nulls for any non-returnable columns. This is easier to hit since we introduced included columns, but it's possible to construct failing examples without that, as per the added regression test. Hence, back-patch to all supported branches. Per bug #17350 from Louis Jachiet. Discussion: https://postgr.es/m/17350-b5bdcf476e5badbb@postgresql.org Branch ------ master Details ------- https://git.postgresql.org/pg/commitdiff/4ace456776524839ef3279ab0bad8a2c9f6cc2a7 Modified Files -------------- src/backend/optimizer/plan/createplan.c | 53 +++++++++++++++++++++++++++++++-- src/include/nodes/plannodes.h | 3 +- src/test/regress/expected/gist.out | 28 +++++++++++++++++ src/test/regress/sql/gist.sql | 11 +++++++ 4 files changed, 92 insertions(+), 3 deletions(-) ^ permalink raw reply [nested|flat] 6+ messages in thread
* pgsql: Fix index-only scan plans when not all index columns can be retu @ 2022-01-01 21:12 Tom Lane <tgl@sss.pgh.pa.us> 0 siblings, 0 replies; 6+ messages in thread From: Tom Lane @ 2022-01-01 21:12 UTC (permalink / raw) To: pgsql-committers@lists.postgresql.org Fix index-only scan plans when not all index columns can be returned. If an index has both returnable and non-returnable columns, and one of the non-returnable columns is an expression using a Var that is in a returnable column, then a query returning that expression could result in an index-only scan plan that attempts to read the non-returnable column, instead of recomputing the expression from the returnable column as intended. To fix, redefine the "indextlist" list of an IndexOnlyScan plan node as containing null Consts in place of any non-returnable columns. This solves the problem by preventing setrefs.c from falsely matching to such entries. The executor is happy since it only cares about the exposed types of the entries, and ruleutils.c doesn't care because a correct plan won't reference those entries. I considered some other ways to prevent setrefs.c from doing the wrong thing, but this way seems good since (a) it allows a very localized fix, (b) it makes the indextlist structure more compact in many cases, and (c) the indextlist is now a more faithful representation of what the index AM will actually produce, viz. nulls for any non-returnable columns. This is easier to hit since we introduced included columns, but it's possible to construct failing examples without that, as per the added regression test. Hence, back-patch to all supported branches. Per bug #17350 from Louis Jachiet. Discussion: https://postgr.es/m/17350-b5bdcf476e5badbb@postgresql.org Branch ------ REL_10_STABLE Details ------- https://git.postgresql.org/pg/commitdiff/70a31a0e3445be8113f45309f5f53a0241c3714a Modified Files -------------- src/backend/optimizer/plan/createplan.c | 53 +++++++++++++++++++++++++++++++-- src/include/nodes/plannodes.h | 3 +- src/test/regress/expected/gist.out | 28 +++++++++++++++++ src/test/regress/sql/gist.sql | 11 +++++++ 4 files changed, 92 insertions(+), 3 deletions(-) ^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2022-01-01 21:12 UTC | newest] Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2022-01-01 21:12 pgsql: Fix index-only scan plans when not all index columns can be retu Tom Lane <tgl@sss.pgh.pa.us> 2022-01-01 21:12 pgsql: Fix index-only scan plans when not all index columns can be retu Tom Lane <tgl@sss.pgh.pa.us> 2022-01-01 21:12 pgsql: Fix index-only scan plans when not all index columns can be retu Tom Lane <tgl@sss.pgh.pa.us> 2022-01-01 21:12 pgsql: Fix index-only scan plans when not all index columns can be retu Tom Lane <tgl@sss.pgh.pa.us> 2022-01-01 21:12 pgsql: Fix index-only scan plans when not all index columns can be retu Tom Lane <tgl@sss.pgh.pa.us> 2022-01-01 21:12 pgsql: Fix index-only scan plans when not all index columns can be retu Tom Lane <tgl@sss.pgh.pa.us>
This inbox is served by DDX for PostgreSQL; see mirroring instructions for how to clone and mirror all data and code used for this inbox