pg.ddx.io pgsql-committers@postgresql.org mailing list archive
help / color / mirror / Atom feedpgsql: Fix planner to restore its previous level of intelligence about
3+ messages / 2 participants
[nested] [flat]
* pgsql: Fix planner to restore its previous level of intelligence about
@ 2009-04-16 20:42 Tom Lane <tgl@postgresql.org>
0 siblings, 0 replies; 3+ messages in thread
From: tgl@postgresql.org @ 2009-04-16 20:42 UTC (permalink / raw)
To: pgsql-committers
Log Message:
-----------
Fix planner to restore its previous level of intelligence about pushing
constants through full joins, as in
select * from tenk1 a full join tenk1 b using (unique1)
where unique1 = 42;
which should generate a fairly cheap plan where we apply the constraint
unique1 = 42 in each relation scan. This had been broken by my patch of
2008-06-27, which is now reverted in favor of a more invasive but hopefully
less incorrect approach. That patch was meant to prevent incorrect extraction
of OR'd indexclauses from OR conditions above an outer join. To do that
correctly we need more information than the outerjoin_delay flag can provide,
so add a nullable_relids field to RestrictInfo that records exactly which
relations are nulled by outer joins that are underneath a particular qual
clause. A side benefit is that we can make the test in create_or_index_quals
more specific: it is now smart enough to extract an OR'd indexclause into the
outer side of an outer join, even though it must not do so in the inner side.
The old coding couldn't distinguish these cases so it could not do either.
Modified Files:
--------------
pgsql/src/backend/nodes:
copyfuncs.c (r1.429 -> r1.430)
(http://anoncvs.postgresql.org/cvsweb.cgi/pgsql/src/backend/nodes/copyfuncs.c?r1=1.429&r2=1.430)
equalfuncs.c (r1.352 -> r1.353)
(http://anoncvs.postgresql.org/cvsweb.cgi/pgsql/src/backend/nodes/equalfuncs.c?r1=1.352&r2=1.353)
outfuncs.c (r1.358 -> r1.359)
(http://anoncvs.postgresql.org/cvsweb.cgi/pgsql/src/backend/nodes/outfuncs.c?r1=1.358&r2=1.359)
pgsql/src/backend/optimizer/path:
indxpath.c (r1.238 -> r1.239)
(http://anoncvs.postgresql.org/cvsweb.cgi/pgsql/src/backend/optimizer/path/indxpath.c?r1=1.238&r2...)
orindxpath.c (r1.88 -> r1.89)
(http://anoncvs.postgresql.org/cvsweb.cgi/pgsql/src/backend/optimizer/path/orindxpath.c?r1=1.88&r...)
pgsql/src/backend/optimizer/plan:
initsplan.c (r1.149 -> r1.150)
(http://anoncvs.postgresql.org/cvsweb.cgi/pgsql/src/backend/optimizer/plan/initsplan.c?r1=1.149&r...)
pgsql/src/backend/optimizer/util:
restrictinfo.c (r1.57 -> r1.58)
(http://anoncvs.postgresql.org/cvsweb.cgi/pgsql/src/backend/optimizer/util/restrictinfo.c?r1=1.57&...)
pgsql/src/include/nodes:
relation.h (r1.171 -> r1.172)
(http://anoncvs.postgresql.org/cvsweb.cgi/pgsql/src/include/nodes/relation.h?r1=1.171&r2=1.172)
pgsql/src/include/optimizer:
restrictinfo.h (r1.42 -> r1.43)
(http://anoncvs.postgresql.org/cvsweb.cgi/pgsql/src/include/optimizer/restrictinfo.h?r1=1.42&r2=1...)
^ permalink raw reply [nested|flat] 3+ messages in thread
* pgsql: Fix planner to restore its previous level of intelligence about
@ 2009-04-16 20:42 Tom Lane <tgl@postgresql.org>
0 siblings, 0 replies; 3+ messages in thread
From: tgl@postgresql.org @ 2009-04-16 20:42 UTC (permalink / raw)
To: pgsql-committers
Log Message:
-----------
Fix planner to restore its previous level of intelligence about pushing
constants through full joins, as in
select * from tenk1 a full join tenk1 b using (unique1)
where unique1 = 42;
which should generate a fairly cheap plan where we apply the constraint
unique1 = 42 in each relation scan. This had been broken by my patch of
2008-06-27, which is now reverted in favor of a more invasive but hopefully
less incorrect approach. That patch was meant to prevent incorrect extraction
of OR'd indexclauses from OR conditions above an outer join. To do that
correctly we need more information than the outerjoin_delay flag can provide,
so add a nullable_relids field to RestrictInfo that records exactly which
relations are nulled by outer joins that are underneath a particular qual
clause. A side benefit is that we can make the test in create_or_index_quals
more specific: it is now smart enough to extract an OR'd indexclause into the
outer side of an outer join, even though it must not do so in the inner side.
The old coding couldn't distinguish these cases so it could not do either.
Tags:
----
REL8_3_STABLE
Modified Files:
--------------
pgsql/src/backend/nodes:
copyfuncs.c (r1.388 -> r1.388.2.1)
(http://anoncvs.postgresql.org/cvsweb.cgi/pgsql/src/backend/nodes/copyfuncs.c?r1=1.388&r2=1.388.2...)
equalfuncs.c (r1.318 -> r1.318.2.1)
(http://anoncvs.postgresql.org/cvsweb.cgi/pgsql/src/backend/nodes/equalfuncs.c?r1=1.318&r2=1.318....)
outfuncs.c (r1.322.2.1 -> r1.322.2.2)
(http://anoncvs.postgresql.org/cvsweb.cgi/pgsql/src/backend/nodes/outfuncs.c?r1=1.322.2.1&r2=1.32...)
pgsql/src/backend/optimizer/path:
indxpath.c (r1.227.2.1 -> r1.227.2.2)
(http://anoncvs.postgresql.org/cvsweb.cgi/pgsql/src/backend/optimizer/path/indxpath.c?r1=1.227.2.1&am...)
orindxpath.c (r1.84 -> r1.84.2.1)
(http://anoncvs.postgresql.org/cvsweb.cgi/pgsql/src/backend/optimizer/path/orindxpath.c?r1=1.84&r...)
pgsql/src/backend/optimizer/plan:
initsplan.c (r1.138.2.2 -> r1.138.2.3)
(http://anoncvs.postgresql.org/cvsweb.cgi/pgsql/src/backend/optimizer/plan/initsplan.c?r1=1.138.2.2&a...)
pgsql/src/backend/optimizer/util:
restrictinfo.c (r1.55 -> r1.55.2.1)
(http://anoncvs.postgresql.org/cvsweb.cgi/pgsql/src/backend/optimizer/util/restrictinfo.c?r1=1.55&...)
pgsql/src/include/nodes:
relation.h (r1.154.2.3 -> r1.154.2.4)
(http://anoncvs.postgresql.org/cvsweb.cgi/pgsql/src/include/nodes/relation.h?r1=1.154.2.3&r2=1.15...)
pgsql/src/include/optimizer:
restrictinfo.h (r1.41 -> r1.41.2.1)
(http://anoncvs.postgresql.org/cvsweb.cgi/pgsql/src/include/optimizer/restrictinfo.h?r1=1.41&r2=1...)
^ permalink raw reply [nested|flat] 3+ messages in thread
* pgsql: Add support for MERGE ... WHEN NOT MATCHED BY SOURCE.
@ 2024-03-30 10:04 Dean Rasheed <dean.a.rasheed@gmail.com>
0 siblings, 0 replies; 3+ messages in thread
From: Dean Rasheed @ 2024-03-30 10:04 UTC (permalink / raw)
To: pgsql-committers@lists.postgresql.org
Add support for MERGE ... WHEN NOT MATCHED BY SOURCE.
This allows MERGE commands to include WHEN NOT MATCHED BY SOURCE
actions, which operate on rows that exist in the target relation, but
not in the data source. These actions can execute UPDATE, DELETE, or
DO NOTHING sub-commands.
This is in contrast to already-supported WHEN NOT MATCHED actions,
which operate on rows that exist in the data source, but not in the
target relation. To make this distinction clearer, such actions may
now be written as WHEN NOT MATCHED BY TARGET.
Writing WHEN NOT MATCHED without specifying BY SOURCE or BY TARGET is
equivalent to writing WHEN NOT MATCHED BY TARGET.
Dean Rasheed, reviewed by Alvaro Herrera, Ted Yu and Vik Fearing.
Discussion: https://postgr.es/m/CAEZATCWqnKGc57Y_JanUBHQXNKcXd7r=0R4NEZUVwP+syRkWbA@mail.gmail.com
Branch
------
master
Details
-------
https://git.postgresql.org/pg/commitdiff/0294df2f1f842dfb0eed79007b21016f486a3c6c
Modified Files
--------------
doc/src/sgml/mvcc.sgml | 12 +-
doc/src/sgml/ref/merge.sgml | 100 +++++--
src/backend/executor/execMain.c | 6 +-
src/backend/executor/execPartition.c | 21 +-
src/backend/executor/nodeModifyTable.c | 387 ++++++++++++++++----------
src/backend/nodes/nodeFuncs.c | 3 +
src/backend/optimizer/plan/createplan.c | 8 +-
src/backend/optimizer/plan/planner.c | 22 ++
src/backend/optimizer/plan/setrefs.c | 20 +-
src/backend/optimizer/prep/prepjointree.c | 51 +++-
src/backend/optimizer/prep/preptlist.c | 26 +-
src/backend/optimizer/util/pathnode.c | 5 +-
src/backend/parser/gram.y | 62 +++--
src/backend/parser/parse_merge.c | 56 ++--
src/backend/utils/adt/ruleutils.c | 41 ++-
src/bin/psql/tab-complete.c | 30 +-
src/include/catalog/catversion.h | 2 +-
src/include/nodes/execnodes.h | 15 +-
src/include/nodes/parsenodes.h | 7 +-
src/include/nodes/pathnodes.h | 2 +
src/include/nodes/plannodes.h | 2 +
src/include/nodes/primnodes.h | 10 +-
src/include/optimizer/pathnode.h | 3 +-
src/include/parser/kwlist.h | 2 +
src/test/isolation/expected/merge-update.out | 88 ++++--
src/test/isolation/specs/merge-update.spec | 10 +-
src/test/regress/expected/merge.out | 299 +++++++++++++++-----
src/test/regress/expected/rules.out | 32 +++
src/test/regress/expected/updatable_views.out | 90 ++++++
src/test/regress/sql/merge.sql | 122 ++++++--
src/test/regress/sql/rules.sql | 19 ++
src/test/regress/sql/updatable_views.sql | 32 +++
src/tools/pgindent/typedefs.list | 1 +
33 files changed, 1226 insertions(+), 360 deletions(-)
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2024-03-30 10:04 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2009-04-16 20:42 pgsql: Fix planner to restore its previous level of intelligence about Tom Lane <tgl@postgresql.org>
2009-04-16 20:42 pgsql: Fix planner to restore its previous level of intelligence about Tom Lane <tgl@postgresql.org>
2024-03-30 10:04 pgsql: Add support for MERGE ... WHEN NOT MATCHED BY SOURCE. Dean Rasheed <dean.a.rasheed@gmail.com>
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