On 7/19/23 16:44, Jacob Champion wrote:
> This patch pushes down any
> forced-null and not-null Vars as ScanKeys. It doesn't remove the
> redundant quals after turning them into ScanKeys, so it's needlessly
> inefficient, but there's still a decent speedup for some of the basic
> benchmarks in 0003.
>
> Plans look something like this:
>
> # EXPLAIN SELECT * FROM t WHERE i IS NULL;
> QUERY PLAN
> ------------------------------------------------------------
> Seq Scan on t (cost=0.00..1393.00 rows=49530 width=4)
> Scan Cond: (i IS NULL)
> Filter: (i IS NULL)
> (3 rows)
Redundant clauses are now filtered out in v3. So the new plans look more
like what you'd expect:
=# EXPLAIN SELECT * FROM table1 WHERE a IS NOT NULL AND b = 2;
QUERY PLAN
---------------------------------------------------------
Seq Scan on table1 (cost=0.00..3344.00 rows=1 width=4)
Scan Cond: (a IS NOT NULL)
Filter: (b = 2)
(3 rows)
> The non-nullable case worries me a bit because so many things imply IS
> NOT NULL. I think I need to do some sort of cost analysis using the
> null_frac statistics -- it probably only makes sense to push an
> implicit SK_SEARCHNOTNULL down to the AM layer if some fraction of
> rows would actually be filtered out -- but I'm not really sure how to
> choose a threshold.
v3 also uses the nullfrac and the expected cost of the filter clauses to
decide whether to promote a derived IS NOT NULL condition to a ScanKey.
(Explicit IS [NOT] NULL clauses are always promoted.) I'm still not sure
how to fine-tune the expected cost of the ScanKey, but the number I've
chosen for now (`0.1 * cpu_operator_cost`) doesn't seem to regress my
benchmarks, for whatever that's worth.
I recorded several of the changes to the regression EXPLAIN output, but
I've left a few broken because I'm not sure if they're useful or if I
should just disable the optimization. For example:
explain (analyze, costs off, summary off, timing off)
select * from list_part where a = list_part_fn(1) + a;
QUERY PLAN
------------------------------------------------------------------
Append (actual rows=0 loops=1)
-> Seq Scan on list_part1 list_part_1 (actual rows=0 loops=1)
+ Scan Cond: (a IS NOT NULL)
Filter: (a = (list_part_fn(1) + a))
Rows Removed by Filter: 1
-> Seq Scan on list_part2 list_part_2 (actual rows=0 loops=1)
+ Scan Cond: (a IS NOT NULL)
Filter: (a = (list_part_fn(1) + a))
Rows Removed by Filter: 1
-> Seq Scan on list_part3 list_part_3 (actual rows=0 loops=1)
+ Scan Cond: (a IS NOT NULL)
Filter: (a = (list_part_fn(1) + a))
Rows Removed by Filter: 1
-> Seq Scan on list_part4 list_part_4 (actual rows=0 loops=1)
+ Scan Cond: (a IS NOT NULL)
Filter: (a = (list_part_fn(1) + a))
Rows Removed by Filter: 1
These new conditions are due to a lack of statistics for the tiny
partitions; the filters are considered expensive enough that the savings
against a DEFAULT_UNK_SEL proportion of NULLs would hypothetically be
worth it. Best I can tell, this is a non-issue, since autovacuum will
fix the problem by the time the table grows to the point where the
pointless ScanKey would cause a slowdown. But it sure _looks_ like a
mistake, especially since these particular partitions can't even contain
NULL. Do I need to do something about this short-lived case?
There's still other work to do -- for instance, I think my modifications
to the filter clauses have probably broken some isolation levels until I
fix up SeqRecheck(), and better benchmarks would be good -- but I think
this is ready for early CF feedback.
Thanks,
--JacobFrom 12794631ee790d637a664f81679c9cdd7d00e2c4 Mon Sep 17 00:00:00 2001
From: Aleksander Alekseev <aleksan...@timescale.com>
Date: Mon, 13 Feb 2023 16:15:45 +0300
Subject: [PATCH v3 1/3] Support SK_SEARCHNULL / SK_SEARCHNOTNULL for heap-only
scans
Previously it was not supported which could be of inconvenience for the
extension authors.
Author: Aleksander Alekseev
Reviewed-by: Andres Freund
Discussion: https://postgr.es/m/caj7c6tpkeh7uwen9orqp_dmr8uxifhxt8pecq01zw1hkptb...@mail.gmail.com
---
src/include/access/skey.h | 7 +--
src/include/access/valid.h | 41 ++++++++++-----
src/test/regress/expected/heapscan.out | 33 ++++++++++++
src/test/regress/expected/test_setup.out | 13 +++++
src/test/regress/parallel_schedule | 2 +-
src/test/regress/regress.c | 67 ++++++++++++++++++++++++
src/test/regress/sql/heapscan.sql | 12 +++++
src/test/regress/sql/test_setup.sql | 16 ++++++
8 files changed, 175 insertions(+), 16 deletions(-)
create mode 100644 src/test/regress/expected/heapscan.out
create mode 100644 src/test/regress/sql/heapscan.sql
diff --git a/src/include/access/skey.h b/src/include/access/skey.h
index fbdb23c5c7..81b0530277 100644
--- a/src/include/access/skey.h
+++ b/src/include/access/skey.h
@@ -46,9 +46,10 @@
* and the sk_strategy, sk_subtype, sk_collation, and sk_func fields are
* not used (unless set by the index AM).
*
- * SK_SEARCHARRAY, SK_SEARCHNULL and SK_SEARCHNOTNULL are supported only
- * for index scans, not heap scans; and not all index AMs support them,
- * only those that set amsearcharray or amsearchnulls respectively.
+ * SK_SEARCHARRAY is supported only for index scans, not heap scans; and not all
+ * index AMs support it, only those that set amsearcharray. SK_SEARCHNULL and
+ * SK_SEARCHNOTNULL are supported for heap and index scans but similarly not all
+ * index AMs support them, only those that set amsearchnulls.
*
* A ScanKey can also represent an ordering operator invocation, that is
* an ordering requirement "ORDER BY indexedcol op constant". This looks
diff --git a/src/include/access/valid.h b/src/include/access/valid.h
index 85d476aab5..477ac75242 100644
--- a/src/include/access/valid.h
+++ b/src/include/access/valid.h
@@ -32,24 +32,41 @@ HeapKeyTest(HeapTuple tuple, TupleDesc tupdesc, int nkeys, ScanKey keys)
for (; cur_nkeys--; cur_key++)
{
- Datum atp;
bool isnull;
- Datum test;
- if (cur_key->sk_flags & SK_ISNULL)
- return false;
+ if (cur_key->sk_flags & (SK_SEARCHNULL | SK_SEARCHNOTNULL))
+ {
+ /* special case: looking for NULL / NOT NULL values */
+ Assert(cur_key->sk_flags & SK_ISNULL);
- atp = heap_getattr(tuple, cur_key->sk_attno, tupdesc, &isnull);
+ isnull = heap_attisnull(tuple, cur_key->sk_attno, tupdesc);
- if (isnull)
- return false;
+ if (isnull && (cur_key->sk_flags & SK_SEARCHNOTNULL))
+ return false;
- test = FunctionCall2Coll(&cur_key->sk_func,
- cur_key->sk_collation,
- atp, cur_key->sk_argument);
+ if (!isnull && (cur_key->sk_flags & SK_SEARCHNULL))
+ return false;
+ }
+ else
+ {
+ Datum atp;
+ Datum test;
- if (!DatumGetBool(test))
- return false;
+ if (cur_key->sk_flags & SK_ISNULL)
+ return false;
+
+ atp = heap_getattr(tuple, cur_key->sk_attno, tupdesc, &isnull);
+
+ if (isnull)
+ return false;
+
+ test = FunctionCall2Coll(&cur_key->sk_func,
+ cur_key->sk_collation,
+ atp, cur_key->sk_argument);
+
+ if (!DatumGetBool(test))
+ return false;
+ }
}
return true;
diff --git a/src/test/regress/expected/heapscan.out b/src/test/regress/expected/heapscan.out
new file mode 100644
index 0000000000..055bf9bc62
--- /dev/null
+++ b/src/test/regress/expected/heapscan.out
@@ -0,0 +1,33 @@
+-- make sure that initially the table is empty
+SELECT * FROM phonebook;
+ id | name | phone
+----+------+-------
+(0 rows)
+
+SELECT phonebook_find_first_phone(isnull => false);
+ phonebook_find_first_phone
+----------------------------
+ -1
+(1 row)
+
+SELECT phonebook_find_first_phone(isnull => true);
+ phonebook_find_first_phone
+----------------------------
+ -1
+(1 row)
+
+INSERT INTO phonebook (id, name, phone) VALUES
+(1, 'Alice', 123456),
+(2, 'Bob', NULL);
+SELECT phonebook_find_first_phone(isnull => false);
+ phonebook_find_first_phone
+----------------------------
+ 1
+(1 row)
+
+SELECT phonebook_find_first_phone(isnull => true);
+ phonebook_find_first_phone
+----------------------------
+ 2
+(1 row)
+
diff --git a/src/test/regress/expected/test_setup.out b/src/test/regress/expected/test_setup.out
index 5d9e6bf12b..70db759697 100644
--- a/src/test/regress/expected/test_setup.out
+++ b/src/test/regress/expected/test_setup.out
@@ -213,6 +213,19 @@ CREATE FUNCTION get_columns_length(oid[])
RETURNS int
AS :'regresslib'
LANGUAGE C STRICT STABLE PARALLEL SAFE;
+--
+-- These table and function are used for testing the support of SK_SEARCHNULL
+-- and SK_SEARCHNOTNULL flags for heap-only scans.
+--
+CREATE TABLE phonebook(
+ id INT PRIMARY KEY NOT NULL,
+ name NAME NOT NULL,
+ phone INT /* nullable */
+);
+CREATE FUNCTION phonebook_find_first_phone(isnull bool)
+ RETURNS int
+ AS :'regresslib', 'phonebook_find_first_phone'
+ LANGUAGE C;
-- Use hand-rolled hash functions and operator classes to get predictable
-- result on different machines. The hash function for int4 simply returns
-- the sum of the values passed to it and the one for text returns the length
diff --git a/src/test/regress/parallel_schedule b/src/test/regress/parallel_schedule
index 4df9d8503b..d0eb5433eb 100644
--- a/src/test/regress/parallel_schedule
+++ b/src/test/regress/parallel_schedule
@@ -119,7 +119,7 @@ test: plancache limit plpgsql copy2 temp domain rangefuncs prepare conversion tr
# The stats test resets stats, so nothing else needing stats access can be in
# this group.
# ----------
-test: partition_join partition_prune reloptions hash_part indexing partition_aggregate partition_info tuplesort explain compression memoize stats
+test: partition_join partition_prune reloptions hash_part indexing partition_aggregate partition_info tuplesort explain compression memoize stats heapscan
# event_trigger depends on create_am and cannot run concurrently with
# any test that runs DDL
diff --git a/src/test/regress/regress.c b/src/test/regress/regress.c
index bcbc6d910f..c7f2eed699 100644
--- a/src/test/regress/regress.c
+++ b/src/test/regress/regress.c
@@ -20,6 +20,7 @@
#include <signal.h>
#include "access/detoast.h"
+#include "access/heapam.h"
#include "access/htup_details.h"
#include "access/transam.h"
#include "access/xact.h"
@@ -45,6 +46,7 @@
#include "utils/lsyscache.h"
#include "utils/memutils.h"
#include "utils/rel.h"
+#include "utils/snapmgr.h"
#include "utils/typcache.h"
#define EXPECT_TRUE(expr) \
@@ -1262,3 +1264,68 @@ get_columns_length(PG_FUNCTION_ARGS)
PG_RETURN_INT32(column_offset);
}
+
+/*
+ * Used for testing SK_SEARCHNULL/SK_SEARCHNOTNULL support for heap-only scans.
+ *
+ * Returns the primary key of the first row found in the "phonebook" table for
+ * which "phone" is NULL or NOT NULL, depending on the "isnull" argument.
+ *
+ * Returns -1 if nothing was found.
+ */
+PG_FUNCTION_INFO_V1(phonebook_find_first_phone);
+
+typedef enum Anum_phonebook
+{
+ Anum_phonebook_id = 1,
+ Anum_phonebook_name,
+ Anum_phonebook_phone,
+ _Anum_phonebook_max,
+} Anum_phonebook;
+
+#define Natts_phonebook (_Anum_phonebook_max - 1)
+
+Datum
+phonebook_find_first_phone(PG_FUNCTION_ARGS)
+{
+ bool isnull = PG_GETARG_BOOL(0);
+ int flags = SK_ISNULL;
+ int32 found_id = -1;
+ Relation rel;
+ HeapTuple tup;
+ TableScanDesc scan;
+ Datum tbl_oid_datum;
+ ScanKeyData scanKey;
+
+ flags |= isnull ? SK_SEARCHNULL : SK_SEARCHNOTNULL;
+ ScanKeyEntryInitialize(
+ &scanKey,
+ flags,
+ Anum_phonebook_phone,
+ InvalidStrategy, /* no strategy */
+ InvalidOid, /* no strategy subtype */
+ InvalidOid, /* no collation */
+ InvalidOid, /* no reg proc for this */
+ (Datum) 0); /* constant */
+
+ tbl_oid_datum = DirectFunctionCall1(to_regclass,
+ CStringGetTextDatum("phonebook"));
+
+ rel = table_open(DatumGetObjectId(tbl_oid_datum), AccessShareLock);
+ scan = table_beginscan(rel, GetTransactionSnapshot(), 1, &scanKey);
+
+ while ((tup = heap_getnext(scan, ForwardScanDirection)) != NULL)
+ {
+ Datum values[Natts_phonebook];
+ bool isnull[Natts_phonebook];
+
+ heap_deform_tuple(tup, RelationGetDescr(rel), values, isnull);
+ found_id = DatumGetInt32(values[Anum_phonebook_id - 1]);
+ break;
+ }
+
+ table_endscan(scan);
+ table_close(rel, AccessShareLock);
+
+ PG_RETURN_INT32(found_id);
+}
diff --git a/src/test/regress/sql/heapscan.sql b/src/test/regress/sql/heapscan.sql
new file mode 100644
index 0000000000..de6528f3dd
--- /dev/null
+++ b/src/test/regress/sql/heapscan.sql
@@ -0,0 +1,12 @@
+-- make sure that initially the table is empty
+SELECT * FROM phonebook;
+
+SELECT phonebook_find_first_phone(isnull => false);
+SELECT phonebook_find_first_phone(isnull => true);
+
+INSERT INTO phonebook (id, name, phone) VALUES
+(1, 'Alice', 123456),
+(2, 'Bob', NULL);
+
+SELECT phonebook_find_first_phone(isnull => false);
+SELECT phonebook_find_first_phone(isnull => true);
\ No newline at end of file
diff --git a/src/test/regress/sql/test_setup.sql b/src/test/regress/sql/test_setup.sql
index 1b2d434683..25b16e75d6 100644
--- a/src/test/regress/sql/test_setup.sql
+++ b/src/test/regress/sql/test_setup.sql
@@ -262,6 +262,22 @@ CREATE FUNCTION get_columns_length(oid[])
AS :'regresslib'
LANGUAGE C STRICT STABLE PARALLEL SAFE;
+--
+-- These table and function are used for testing the support of SK_SEARCHNULL
+-- and SK_SEARCHNOTNULL flags for heap-only scans.
+--
+
+CREATE TABLE phonebook(
+ id INT PRIMARY KEY NOT NULL,
+ name NAME NOT NULL,
+ phone INT /* nullable */
+);
+
+CREATE FUNCTION phonebook_find_first_phone(isnull bool)
+ RETURNS int
+ AS :'regresslib', 'phonebook_find_first_phone'
+ LANGUAGE C;
+
-- Use hand-rolled hash functions and operator classes to get predictable
-- result on different machines. The hash function for int4 simply returns
-- the sum of the values passed to it and the one for text returns the length
--
2.34.1
From 634e80d00f7ac4f6d9b7b72ede5e3bb8ae5dfce6 Mon Sep 17 00:00:00 2001
From: Jacob Champion <jchamp...@timescale.com>
Date: Wed, 7 Jun 2023 16:42:17 -0700
Subject: [PATCH v3 2/3] WIP: create ScanKeys from derived null tests
...for pushdown to the TableAM layer. The enable_seqscan_conds GUC
controls whether this work is done.
Explicit IS [NOT] NULL clauses are always pushed down, since the
ScanKeys created will replace the clauses altogether. Derived IS NOT
NULL conditions (e.g. for strict function calls) are only added if the
expected savings outweighs the per-row cost of the check.
Several regression tests have been updated with the new plans, but some
remain unfixed because I'm not sure whether it would be more helpful to
disable this optimization for them.
TODO:
- decide how to tune the per-row cost of a ScanKey
- fix the last regression tests
- look into key clause generation against partition leaves
- fix SeqRecheck()
---
src/backend/commands/explain.c | 3 +
src/backend/executor/nodeSeqscan.c | 53 ++++-
src/backend/optimizer/path/costsize.c | 1 +
src/backend/optimizer/plan/createplan.c | 165 ++++++++++++++-
src/backend/optimizer/plan/setrefs.c | 3 +
src/backend/utils/misc/guc_tables.c | 12 ++
src/backend/utils/misc/postgresql.conf.sample | 1 +
src/include/nodes/execnodes.h | 2 +
src/include/nodes/plannodes.h | 1 +
src/include/optimizer/cost.h | 1 +
src/test/regress/expected/equivclass.out | 6 +-
src/test/regress/expected/inherit.out | 39 ++--
src/test/regress/expected/partition_join.out | 24 ++-
src/test/regress/expected/partition_prune.out | 188 ++++++++++--------
src/test/regress/expected/sysviews.out | 3 +-
src/test/regress/expected/union.out | 8 +-
16 files changed, 387 insertions(+), 123 deletions(-)
diff --git a/src/backend/commands/explain.c b/src/backend/commands/explain.c
index 8570b14f62..df3882e6b1 100644
--- a/src/backend/commands/explain.c
+++ b/src/backend/commands/explain.c
@@ -1832,6 +1832,9 @@ ExplainNode(PlanState *planstate, List *ancestors,
case T_NamedTuplestoreScan:
case T_WorkTableScan:
case T_SubqueryScan:
+ if (IsA(plan, SeqScan) && ((SeqScan *) plan)->keyexprs)
+ show_scan_qual(((SeqScan *) plan)->keyexprs, "Scan Cond",
+ planstate, ancestors, es);
show_scan_qual(plan->qual, "Filter", planstate, ancestors, es);
if (plan->qual)
show_instrumentation_count("Rows Removed by Filter", 1,
diff --git a/src/backend/executor/nodeSeqscan.c b/src/backend/executor/nodeSeqscan.c
index 4da0f28f7b..67ff9d1482 100644
--- a/src/backend/executor/nodeSeqscan.c
+++ b/src/backend/executor/nodeSeqscan.c
@@ -28,6 +28,7 @@
#include "postgres.h"
#include "access/relscan.h"
+#include "access/skey.h"
#include "access/tableam.h"
#include "executor/execdebug.h"
#include "executor/nodeSeqscan.h"
@@ -70,7 +71,7 @@ SeqNext(SeqScanState *node)
*/
scandesc = table_beginscan(node->ss.ss_currentRelation,
estate->es_snapshot,
- 0, NULL);
+ node->nkeys, node->scankeys);
node->ss.ss_currentScanDesc = scandesc;
}
@@ -91,6 +92,8 @@ SeqRecheck(SeqScanState *node, TupleTableSlot *slot)
/*
* Note that unlike IndexScan, SeqScan never use keys in heap_beginscan
* (and this is very bad) - so, here we do not check are keys ok or not.
+ *
+ * TODO: so I guess this isn't true anymore.
*/
return true;
}
@@ -114,6 +117,48 @@ ExecSeqScan(PlanState *pstate)
(ExecScanRecheckMtd) SeqRecheck);
}
+static void
+create_scankeys(SeqScanState *scanstate, List *keyexprs)
+{
+ struct ScanKeyData *keys;
+ int nkeys;
+ ListCell *l;
+
+ nkeys = list_length(keyexprs);
+ keys = palloc(sizeof(struct ScanKeyData) * nkeys);
+
+ foreach(l, keyexprs)
+ {
+ NullTest *n;
+ Var *var;
+ int typeflag;
+ int i = foreach_current_index(l);
+
+ /*
+ * create_seqscan_plan() only puts NullTests of Vars into the SeqScan's
+ * key clauses, so that's all we handle here.
+ */
+ n = lfirst_node(NullTest, l);
+ var = (Var *) n->arg;
+ Assert(IsA(var, Var));
+
+ typeflag = (n->nulltesttype == IS_NULL) ? SK_SEARCHNULL
+ : SK_SEARCHNOTNULL;
+
+ Assert(i < nkeys);
+ ScanKeyEntryInitialize(&keys[i],
+ SK_ISNULL | typeflag,
+ var->varattno,
+ InvalidStrategy,
+ InvalidOid,
+ InvalidOid,
+ InvalidOid,
+ (Datum) 0);
+ }
+
+ scanstate->nkeys = nkeys;
+ scanstate->scankeys = keys;
+}
/* ----------------------------------------------------------------
* ExecInitSeqScan
@@ -171,6 +216,12 @@ ExecInitSeqScan(SeqScan *node, EState *estate, int eflags)
scanstate->ss.ps.qual =
ExecInitQual(node->scan.plan.qual, (PlanState *) scanstate);
+ /*
+ * Populate scankeys, if necessary.
+ */
+ if (node->keyexprs)
+ create_scankeys(scanstate, node->keyexprs);
+
return scanstate;
}
diff --git a/src/backend/optimizer/path/costsize.c b/src/backend/optimizer/path/costsize.c
index d6ceafd51c..0f49d5a90f 100644
--- a/src/backend/optimizer/path/costsize.c
+++ b/src/backend/optimizer/path/costsize.c
@@ -133,6 +133,7 @@ Cost disable_cost = 1.0e10;
int max_parallel_workers_per_gather = 2;
bool enable_seqscan = true;
+bool enable_seqscan_conds = true;
bool enable_indexscan = true;
bool enable_indexonlyscan = true;
bool enable_bitmapscan = true;
diff --git a/src/backend/optimizer/plan/createplan.c b/src/backend/optimizer/plan/createplan.c
index 34ca6d4ac2..5d65576497 100644
--- a/src/backend/optimizer/plan/createplan.c
+++ b/src/backend/optimizer/plan/createplan.c
@@ -24,6 +24,7 @@
#include "miscadmin.h"
#include "nodes/extensible.h"
#include "nodes/makefuncs.h"
+#include "nodes/multibitmapset.h"
#include "nodes/nodeFuncs.h"
#include "optimizer/clauses.h"
#include "optimizer/cost.h"
@@ -177,7 +178,8 @@ static void copy_generic_path_info(Plan *dest, Path *src);
static void copy_plan_costsize(Plan *dest, Plan *src);
static void label_sort_with_costsize(PlannerInfo *root, Sort *plan,
double limit_tuples);
-static SeqScan *make_seqscan(List *qptlist, List *qpqual, Index scanrelid);
+static SeqScan *make_seqscan(List *qptlist, List *qpqual, List *scankeys,
+ Index scanrelid);
static SampleScan *make_samplescan(List *qptlist, List *qpqual, Index scanrelid,
TableSampleClause *tsc);
static IndexScan *make_indexscan(List *qptlist, List *qpqual, Index scanrelid,
@@ -2904,6 +2906,160 @@ create_limit_plan(PlannerInfo *root, LimitPath *best_path, int flags)
*****************************************************************************/
+/*
+ * reconstruct_null_tests
+ * Creates a list of NullTests, one for each Var in varset, which is a
+ * multibitmapset of varno/varattno. Whole-row Vars are omitted.
+ */
+static List *
+reconstruct_null_tests(PlannerInfo *root, List *tests, NullTestType type, List *varset)
+{
+ ListCell *lc;
+
+ foreach(lc, varset)
+ {
+ Bitmapset *varattnos = lfirst_node(Bitmapset, lc);
+ int i = -1;
+
+ while ((i = bms_next_member(varattnos, i)) >= 0)
+ {
+ int varno = foreach_current_index(lc);
+ AttrNumber varattno = i + FirstLowInvalidHeapAttributeNumber;
+ RangeTblEntry *rte;
+ Oid atttypid;
+ int32 atttypmod;
+ Oid attcollation;
+ Var *var;
+ NullTest *n;
+
+ if (varattno == 0)
+ continue; /* skip whole-row vars */
+
+ rte = planner_rt_fetch(varno, root);
+ get_atttypetypmodcoll(rte->relid, varattno,
+ &atttypid, &atttypmod, &attcollation);
+
+ var = makeVar(varno,
+ varattno,
+ atttypid,
+ atttypmod,
+ attcollation,
+ 0);
+
+ n = makeNode(NullTest);
+ n->arg = (Expr *) var;
+ n->nulltesttype = type;
+ n->argisrow = false;
+ n->location = -1;
+
+ tests = lappend(tests, n);
+ }
+ }
+
+ return tests;
+}
+
+
+static List *
+extract_key_clauses(PlannerInfo *root, List *scan_clauses, List **key_clauses)
+{
+ const Cost scankey_cost = 0.05 * cpu_operator_cost; /* TODO tunable? */
+ ListCell *lc;
+ QualCost qcost;
+ Cost skippable_cost;
+ List *null_vars;
+ List *nonnull_vars = NIL;
+
+ cost_qual_eval(&qcost, scan_clauses, root);
+ skippable_cost = qcost.per_tuple;
+
+ /*
+ * Because find_forced_null_vars() only finds literal IS NULL tests, we
+ * assume they're always worth promoting to a key clause.
+ */
+ null_vars = mbms_add_members(NIL,
+ find_forced_null_vars((Node *) scan_clauses));
+
+ /*
+ * The non-NULL case is trickier. In addition to IS NOT NULL tests, which we
+ * always promote, other clauses like strict function calls are only worth
+ * generating a key clause for if the expected cost savings outweigh the
+ * additional scankey_cost.
+ */
+ foreach(lc, scan_clauses)
+ {
+ Node *clause = (Node *) lfirst(lc);
+ List *tests;
+ ListCell *lc2;
+
+ tests = reconstruct_null_tests(root, NIL, IS_NOT_NULL, find_nonnullable_vars(clause));
+ foreach(lc2, tests)
+ {
+ NullTest *test = lfirst_node(NullTest, lc2);
+ Var *var = (Var *) test->arg; /* see reconstruct_null_tests() */
+
+ if (equal(clause, test))
+ {
+ /*
+ * The clause is an identical IS NOT NULL test. We know we'll be
+ * able to save the entire cost of the redundant clause even if
+ * the key clause doesn't end up filtering anything, so promote
+ * it.
+ */
+ }
+ else
+ {
+ /*
+ * The more selective the null check to be pushed down, the more
+ * we'll save. Only create a key clause when that savings
+ * outweighs the cost of the new check.
+ *
+ * XXX nulltestsel() doesn't use the JoinType or the
+ * SpecialJoinInfo, but relying on that detail at this level
+ * seems wrong
+ */
+ Selectivity sel = clause_selectivity(root, (Node *) test, 0, -1, NULL);
+ Cost savings = skippable_cost * (1.0 - sel);
+
+ if (savings < scankey_cost)
+ continue; /* not worth it */
+ }
+
+ nonnull_vars = mbms_add_member(nonnull_vars,
+ var->varno,
+ var->varattno - FirstLowInvalidHeapAttributeNumber);
+ }
+
+ /*
+ * TODO: if we know the clause is being removed, it might be more honest
+ * to subtract it from the skippable_cost, but that seems like a lot of
+ * complexity for unclear benefit.
+ */
+ }
+
+ /*
+ * Reconstitute our key clauses from our deduplicated multibitmapsets.
+ *
+ * TODO: would it be worth it to sort by descending selectivity?
+ */
+ *key_clauses = reconstruct_null_tests(root, *key_clauses, IS_NULL, null_vars);
+ *key_clauses = reconstruct_null_tests(root, *key_clauses, IS_NOT_NULL, nonnull_vars);
+
+ /*
+ * Now remove redundant scan clauses.
+ */
+ foreach(lc, scan_clauses)
+ {
+ Node *clause = (Node *) lfirst(lc);
+
+ if (predicate_implied_by(list_make1(clause), *key_clauses, false))
+ scan_clauses = foreach_delete_current(scan_clauses, lc);
+ }
+
+ return scan_clauses;
+}
+
+
/*
* create_seqscan_plan
* Returns a seqscan plan for the base relation scanned by 'best_path'
@@ -2915,6 +3071,7 @@ create_seqscan_plan(PlannerInfo *root, Path *best_path,
{
SeqScan *scan_plan;
Index scan_relid = best_path->parent->relid;
+ List *keyexprs = NIL;
/* it should be a base rel... */
Assert(scan_relid > 0);
@@ -2933,8 +3090,12 @@ create_seqscan_plan(PlannerInfo *root, Path *best_path,
replace_nestloop_params(root, (Node *) scan_clauses);
}
+ if (enable_seqscan_conds)
+ scan_clauses = extract_key_clauses(root, scan_clauses, &keyexprs);
+
scan_plan = make_seqscan(tlist,
scan_clauses,
+ keyexprs,
scan_relid);
copy_generic_path_info(&scan_plan->scan.plan, best_path);
@@ -5479,6 +5640,7 @@ bitmap_subplan_mark_shared(Plan *plan)
static SeqScan *
make_seqscan(List *qptlist,
List *qpqual,
+ List *keyexprs,
Index scanrelid)
{
SeqScan *node = makeNode(SeqScan);
@@ -5489,6 +5651,7 @@ make_seqscan(List *qptlist,
plan->lefttree = NULL;
plan->righttree = NULL;
node->scan.scanrelid = scanrelid;
+ node->keyexprs = keyexprs;
return node;
}
diff --git a/src/backend/optimizer/plan/setrefs.c b/src/backend/optimizer/plan/setrefs.c
index 97fa561e4e..2b58b6c2b3 100644
--- a/src/backend/optimizer/plan/setrefs.c
+++ b/src/backend/optimizer/plan/setrefs.c
@@ -632,6 +632,9 @@ set_plan_refs(PlannerInfo *root, Plan *plan, int rtoffset)
splan->scan.plan.qual =
fix_scan_list(root, splan->scan.plan.qual,
rtoffset, NUM_EXEC_QUAL(plan));
+ splan->keyexprs =
+ fix_scan_list(root, splan->keyexprs,
+ rtoffset, NUM_EXEC_QUAL(plan));
}
break;
case T_SampleScan:
diff --git a/src/backend/utils/misc/guc_tables.c b/src/backend/utils/misc/guc_tables.c
index e565a3092f..89bc712068 100644
--- a/src/backend/utils/misc/guc_tables.c
+++ b/src/backend/utils/misc/guc_tables.c
@@ -818,6 +818,18 @@ struct config_bool ConfigureNamesBool[] =
true,
NULL, NULL, NULL
},
+ {
+ {"enable_seqscan_conds", PGC_USERSET, QUERY_TUNING_METHOD,
+ gettext_noop("Enables the planner's ability to push down scan conditions "
+ "during sequential scans."),
+ gettext_noop("Allows the query planner to build sequential-scan plans that "
+ "prefilter row attributes before expression evaluation."),
+ GUC_EXPLAIN
+ },
+ &enable_seqscan_conds,
+ true,
+ NULL, NULL, NULL
+ },
{
{"enable_indexscan", PGC_USERSET, QUERY_TUNING_METHOD,
gettext_noop("Enables the planner's use of index-scan plans."),
diff --git a/src/backend/utils/misc/postgresql.conf.sample b/src/backend/utils/misc/postgresql.conf.sample
index c768af9a73..28255fe438 100644
--- a/src/backend/utils/misc/postgresql.conf.sample
+++ b/src/backend/utils/misc/postgresql.conf.sample
@@ -391,6 +391,7 @@
#enable_partitionwise_aggregate = off
#enable_presorted_aggregate = on
#enable_seqscan = on
+#enable_seqscan_conds = on
#enable_sort = on
#enable_tidscan = on
diff --git a/src/include/nodes/execnodes.h b/src/include/nodes/execnodes.h
index cb714f4a19..d015a3a127 100644
--- a/src/include/nodes/execnodes.h
+++ b/src/include/nodes/execnodes.h
@@ -1485,6 +1485,8 @@ typedef struct SeqScanState
{
ScanState ss; /* its first field is NodeTag */
Size pscan_len; /* size of parallel heap scan descriptor */
+ struct ScanKeyData *scankeys;
+ int nkeys;
} SeqScanState;
/* ----------------
diff --git a/src/include/nodes/plannodes.h b/src/include/nodes/plannodes.h
index 1b787fe031..b6dbf64d1e 100644
--- a/src/include/nodes/plannodes.h
+++ b/src/include/nodes/plannodes.h
@@ -394,6 +394,7 @@ typedef struct Scan
typedef struct SeqScan
{
Scan scan;
+ List *keyexprs; /* expressions to push down as ScanKeys */
} SeqScan;
/* ----------------
diff --git a/src/include/optimizer/cost.h b/src/include/optimizer/cost.h
index bee090ffc2..c7bffd1093 100644
--- a/src/include/optimizer/cost.h
+++ b/src/include/optimizer/cost.h
@@ -50,6 +50,7 @@ typedef enum
extern PGDLLIMPORT Cost disable_cost;
extern PGDLLIMPORT int max_parallel_workers_per_gather;
extern PGDLLIMPORT bool enable_seqscan;
+extern PGDLLIMPORT bool enable_seqscan_conds;
extern PGDLLIMPORT bool enable_indexscan;
extern PGDLLIMPORT bool enable_indexonlyscan;
extern PGDLLIMPORT bool enable_bitmapscan;
diff --git a/src/test/regress/expected/equivclass.out b/src/test/regress/expected/equivclass.out
index 126f7047fe..314ffe0fee 100644
--- a/src/test/regress/expected/equivclass.out
+++ b/src/test/regress/expected/equivclass.out
@@ -424,10 +424,10 @@ drop user regress_user_ectest;
-- check that X=X is converted to X IS NOT NULL when appropriate
explain (costs off)
select * from tenk1 where unique1 = unique1 and unique2 = unique2;
- QUERY PLAN
--------------------------------------------------------------
+ QUERY PLAN
+----------------------------------------------------------------
Seq Scan on tenk1
- Filter: ((unique1 IS NOT NULL) AND (unique2 IS NOT NULL))
+ Scan Cond: ((unique1 IS NOT NULL) AND (unique2 IS NOT NULL))
(2 rows)
-- this could be converted, but isn't at present
diff --git a/src/test/regress/expected/inherit.out b/src/test/regress/expected/inherit.out
index dae61b9a0b..7f68c01a53 100644
--- a/src/test/regress/expected/inherit.out
+++ b/src/test/regress/expected/inherit.out
@@ -2460,7 +2460,7 @@ explain (costs off) select * from list_parted where a is null;
QUERY PLAN
--------------------------------------
Seq Scan on part_null_xy list_parted
- Filter: (a IS NULL)
+ Scan Cond: (a IS NULL)
(2 rows)
explain (costs off) select * from list_parted where a is not null;
@@ -2468,11 +2468,11 @@ explain (costs off) select * from list_parted where a is not null;
----------------------------------------------
Append
-> Seq Scan on part_ab_cd list_parted_1
- Filter: (a IS NOT NULL)
+ Scan Cond: (a IS NOT NULL)
-> Seq Scan on part_ef_gh list_parted_2
- Filter: (a IS NOT NULL)
+ Scan Cond: (a IS NOT NULL)
-> Seq Scan on part_null_xy list_parted_3
- Filter: (a IS NOT NULL)
+ Scan Cond: (a IS NOT NULL)
(7 rows)
explain (costs off) select * from list_parted where a in ('ab', 'cd', 'ef');
@@ -2580,7 +2580,7 @@ explain (costs off) select * from range_list_parted where b is null;
QUERY PLAN
------------------------------------------------
Seq Scan on part_40_inf_null range_list_parted
- Filter: (b IS NULL)
+ Scan Cond: (b IS NULL)
(2 rows)
explain (costs off) select * from range_list_parted where a is not null and a < 67;
@@ -2588,24 +2588,33 @@ explain (costs off) select * from range_list_parted where a is not null and a <
--------------------------------------------------------
Append
-> Seq Scan on part_1_10_ab range_list_parted_1
- Filter: ((a IS NOT NULL) AND (a < 67))
+ Scan Cond: (a IS NOT NULL)
+ Filter: (a < 67)
-> Seq Scan on part_1_10_cd range_list_parted_2
- Filter: ((a IS NOT NULL) AND (a < 67))
+ Scan Cond: (a IS NOT NULL)
+ Filter: (a < 67)
-> Seq Scan on part_10_20_ab range_list_parted_3
- Filter: ((a IS NOT NULL) AND (a < 67))
+ Scan Cond: (a IS NOT NULL)
+ Filter: (a < 67)
-> Seq Scan on part_10_20_cd range_list_parted_4
- Filter: ((a IS NOT NULL) AND (a < 67))
+ Scan Cond: (a IS NOT NULL)
+ Filter: (a < 67)
-> Seq Scan on part_21_30_ab range_list_parted_5
- Filter: ((a IS NOT NULL) AND (a < 67))
+ Scan Cond: (a IS NOT NULL)
+ Filter: (a < 67)
-> Seq Scan on part_21_30_cd range_list_parted_6
- Filter: ((a IS NOT NULL) AND (a < 67))
+ Scan Cond: (a IS NOT NULL)
+ Filter: (a < 67)
-> Seq Scan on part_40_inf_ab range_list_parted_7
- Filter: ((a IS NOT NULL) AND (a < 67))
+ Scan Cond: (a IS NOT NULL)
+ Filter: (a < 67)
-> Seq Scan on part_40_inf_cd range_list_parted_8
- Filter: ((a IS NOT NULL) AND (a < 67))
+ Scan Cond: (a IS NOT NULL)
+ Filter: (a < 67)
-> Seq Scan on part_40_inf_null range_list_parted_9
- Filter: ((a IS NOT NULL) AND (a < 67))
-(19 rows)
+ Scan Cond: (a IS NOT NULL)
+ Filter: (a < 67)
+(28 rows)
explain (costs off) select * from range_list_parted where a >= 30;
QUERY PLAN
diff --git a/src/test/regress/expected/partition_join.out b/src/test/regress/expected/partition_join.out
index 6560fe2416..4e1dda926c 100644
--- a/src/test/regress/expected/partition_join.out
+++ b/src/test/regress/expected/partition_join.out
@@ -1982,11 +1982,11 @@ WHERE EXISTS (
-> Nested Loop Semi Join
-> Append
-> Seq Scan on prt1_l_p1 prt1_l_1
- Filter: (c IS NULL)
+ Scan Cond: (c IS NULL)
-> Seq Scan on prt1_l_p3_p1 prt1_l_2
- Filter: (c IS NULL)
+ Scan Cond: (c IS NULL)
-> Seq Scan on prt1_l_p3_p2 prt1_l_3
- Filter: (c IS NULL)
+ Scan Cond: (c IS NULL)
-> Materialize
-> Nested Loop
-> Seq Scan on int4_tbl
@@ -4520,8 +4520,8 @@ SELECT t1.a, t1.c, t2.a, t2.c FROM plt1_adv t1 INNER JOIN plt2_adv t2 ON (t1.a =
EXPLAIN (COSTS OFF)
SELECT t1.a, t1.c, t2.a, t2.c FROM plt1_adv t1 LEFT JOIN plt2_adv t2 ON (t1.a = t2.a AND t1.c = t2.c) WHERE t1.c IS NULL AND t1.b < 10 ORDER BY t1.a;
- QUERY PLAN
---------------------------------------------------------
+ QUERY PLAN
+------------------------------------------------------
Sort
Sort Key: t1.a
-> Hash Right Join
@@ -4529,8 +4529,9 @@ SELECT t1.a, t1.c, t2.a, t2.c FROM plt1_adv t1 LEFT JOIN plt2_adv t2 ON (t1.a =
-> Seq Scan on plt2_adv_p4 t2
-> Hash
-> Seq Scan on plt1_adv_p4 t1
- Filter: ((c IS NULL) AND (b < 10))
-(8 rows)
+ Scan Cond: (c IS NULL)
+ Filter: (b < 10)
+(9 rows)
SELECT t1.a, t1.c, t2.a, t2.c FROM plt1_adv t1 LEFT JOIN plt2_adv t2 ON (t1.a = t2.a AND t1.c = t2.c) WHERE t1.c IS NULL AND t1.b < 10 ORDER BY t1.a;
a | c | a | c
@@ -4573,8 +4574,8 @@ SELECT t1.a, t1.c, t2.a, t2.c FROM plt1_adv t1 INNER JOIN plt2_adv t2 ON (t1.a =
EXPLAIN (COSTS OFF)
SELECT t1.a, t1.c, t2.a, t2.c FROM plt1_adv t1 LEFT JOIN plt2_adv t2 ON (t1.a = t2.a AND t1.c = t2.c) WHERE t1.c IS NULL AND t1.b < 10 ORDER BY t1.a;
- QUERY PLAN
---------------------------------------------------------
+ QUERY PLAN
+------------------------------------------------------
Sort
Sort Key: t1.a
-> Hash Right Join
@@ -4582,8 +4583,9 @@ SELECT t1.a, t1.c, t2.a, t2.c FROM plt1_adv t1 LEFT JOIN plt2_adv t2 ON (t1.a =
-> Seq Scan on plt2_adv_p4 t2
-> Hash
-> Seq Scan on plt1_adv_p4 t1
- Filter: ((c IS NULL) AND (b < 10))
-(8 rows)
+ Scan Cond: (c IS NULL)
+ Filter: (b < 10)
+(9 rows)
SELECT t1.a, t1.c, t2.a, t2.c FROM plt1_adv t1 LEFT JOIN plt2_adv t2 ON (t1.a = t2.a AND t1.c = t2.c) WHERE t1.c IS NULL AND t1.b < 10 ORDER BY t1.a;
a | c | a | c
diff --git a/src/test/regress/expected/partition_prune.out b/src/test/regress/expected/partition_prune.out
index 1eb347503a..119793f79e 100644
--- a/src/test/regress/expected/partition_prune.out
+++ b/src/test/regress/expected/partition_prune.out
@@ -59,26 +59,26 @@ explain (costs off) select * from lp where 'a' = a; /* commuted */
(2 rows)
explain (costs off) select * from lp where a is not null;
- QUERY PLAN
------------------------------------
+ QUERY PLAN
+------------------------------------
Append
-> Seq Scan on lp_ad lp_1
- Filter: (a IS NOT NULL)
+ Scan Cond: (a IS NOT NULL)
-> Seq Scan on lp_bc lp_2
- Filter: (a IS NOT NULL)
+ Scan Cond: (a IS NOT NULL)
-> Seq Scan on lp_ef lp_3
- Filter: (a IS NOT NULL)
+ Scan Cond: (a IS NOT NULL)
-> Seq Scan on lp_g lp_4
- Filter: (a IS NOT NULL)
+ Scan Cond: (a IS NOT NULL)
-> Seq Scan on lp_default lp_5
- Filter: (a IS NOT NULL)
+ Scan Cond: (a IS NOT NULL)
(11 rows)
explain (costs off) select * from lp where a is null;
- QUERY PLAN
-------------------------
+ QUERY PLAN
+--------------------------
Seq Scan on lp_null lp
- Filter: (a IS NULL)
+ Scan Cond: (a IS NULL)
(2 rows)
explain (costs off) select * from lp where a = 'a' or a = 'c';
@@ -92,14 +92,16 @@ explain (costs off) select * from lp where a = 'a' or a = 'c';
(5 rows)
explain (costs off) select * from lp where a is not null and (a = 'a' or a = 'c');
- QUERY PLAN
---------------------------------------------------------------------------------
+ QUERY PLAN
+----------------------------------------------------------
Append
-> Seq Scan on lp_ad lp_1
- Filter: ((a IS NOT NULL) AND ((a = 'a'::bpchar) OR (a = 'c'::bpchar)))
+ Scan Cond: (a IS NOT NULL)
+ Filter: ((a = 'a'::bpchar) OR (a = 'c'::bpchar))
-> Seq Scan on lp_bc lp_2
- Filter: ((a IS NOT NULL) AND ((a = 'a'::bpchar) OR (a = 'c'::bpchar)))
-(5 rows)
+ Scan Cond: (a IS NOT NULL)
+ Filter: ((a = 'a'::bpchar) OR (a = 'c'::bpchar))
+(7 rows)
explain (costs off) select * from lp where a <> 'g';
QUERY PLAN
@@ -402,31 +404,36 @@ explain (costs off) select * from rlp where a = 16 and b <= 'ab';
(5 rows)
explain (costs off) select * from rlp where a = 16 and b is null;
- QUERY PLAN
---------------------------------------
+ QUERY PLAN
+----------------------------
Seq Scan on rlp3nullxy rlp
- Filter: ((b IS NULL) AND (a = 16))
-(2 rows)
+ Scan Cond: (b IS NULL)
+ Filter: (a = 16)
+(3 rows)
explain (costs off) select * from rlp where a = 16 and b is not null;
- QUERY PLAN
-------------------------------------------------
+ QUERY PLAN
+--------------------------------------
Append
-> Seq Scan on rlp3abcd rlp_1
- Filter: ((b IS NOT NULL) AND (a = 16))
+ Scan Cond: (b IS NOT NULL)
+ Filter: (a = 16)
-> Seq Scan on rlp3efgh rlp_2
- Filter: ((b IS NOT NULL) AND (a = 16))
+ Scan Cond: (b IS NOT NULL)
+ Filter: (a = 16)
-> Seq Scan on rlp3nullxy rlp_3
- Filter: ((b IS NOT NULL) AND (a = 16))
+ Scan Cond: (b IS NOT NULL)
+ Filter: (a = 16)
-> Seq Scan on rlp3_default rlp_4
- Filter: ((b IS NOT NULL) AND (a = 16))
-(9 rows)
+ Scan Cond: (b IS NOT NULL)
+ Filter: (a = 16)
+(13 rows)
explain (costs off) select * from rlp where a is null;
QUERY PLAN
----------------------------------
Seq Scan on rlp_default_null rlp
- Filter: (a IS NULL)
+ Scan Cond: (a IS NULL)
(2 rows)
explain (costs off) select * from rlp where a is not null;
@@ -434,33 +441,33 @@ explain (costs off) select * from rlp where a is not null;
----------------------------------------------
Append
-> Seq Scan on rlp1 rlp_1
- Filter: (a IS NOT NULL)
+ Scan Cond: (a IS NOT NULL)
-> Seq Scan on rlp2 rlp_2
- Filter: (a IS NOT NULL)
+ Scan Cond: (a IS NOT NULL)
-> Seq Scan on rlp3abcd rlp_3
- Filter: (a IS NOT NULL)
+ Scan Cond: (a IS NOT NULL)
-> Seq Scan on rlp3efgh rlp_4
- Filter: (a IS NOT NULL)
+ Scan Cond: (a IS NOT NULL)
-> Seq Scan on rlp3nullxy rlp_5
- Filter: (a IS NOT NULL)
+ Scan Cond: (a IS NOT NULL)
-> Seq Scan on rlp3_default rlp_6
- Filter: (a IS NOT NULL)
+ Scan Cond: (a IS NOT NULL)
-> Seq Scan on rlp4_1 rlp_7
- Filter: (a IS NOT NULL)
+ Scan Cond: (a IS NOT NULL)
-> Seq Scan on rlp4_2 rlp_8
- Filter: (a IS NOT NULL)
+ Scan Cond: (a IS NOT NULL)
-> Seq Scan on rlp4_default rlp_9
- Filter: (a IS NOT NULL)
+ Scan Cond: (a IS NOT NULL)
-> Seq Scan on rlp5_1 rlp_10
- Filter: (a IS NOT NULL)
+ Scan Cond: (a IS NOT NULL)
-> Seq Scan on rlp5_default rlp_11
- Filter: (a IS NOT NULL)
+ Scan Cond: (a IS NOT NULL)
-> Seq Scan on rlp_default_10 rlp_12
- Filter: (a IS NOT NULL)
+ Scan Cond: (a IS NOT NULL)
-> Seq Scan on rlp_default_30 rlp_13
- Filter: (a IS NOT NULL)
+ Scan Cond: (a IS NOT NULL)
-> Seq Scan on rlp_default_default rlp_14
- Filter: (a IS NOT NULL)
+ Scan Cond: (a IS NOT NULL)
(29 rows)
explain (costs off) select * from rlp where a > 30;
@@ -999,38 +1006,40 @@ explain (costs off) select * from mc2p where a = 1 and b > 1;
-- all partitions but the default one should be pruned
explain (costs off) select * from mc2p where a = 1 and b is null;
- QUERY PLAN
--------------------------------------
+ QUERY PLAN
+-------------------------------
Seq Scan on mc2p_default mc2p
- Filter: ((b IS NULL) AND (a = 1))
-(2 rows)
+ Scan Cond: (b IS NULL)
+ Filter: (a = 1)
+(3 rows)
explain (costs off) select * from mc2p where a is null and b is null;
- QUERY PLAN
------------------------------------------
+ QUERY PLAN
+--------------------------------------------
Seq Scan on mc2p_default mc2p
- Filter: ((a IS NULL) AND (b IS NULL))
+ Scan Cond: ((a IS NULL) AND (b IS NULL))
(2 rows)
explain (costs off) select * from mc2p where a is null and b = 1;
- QUERY PLAN
--------------------------------------
+ QUERY PLAN
+-------------------------------
Seq Scan on mc2p_default mc2p
- Filter: ((a IS NULL) AND (b = 1))
-(2 rows)
+ Scan Cond: (a IS NULL)
+ Filter: (b = 1)
+(3 rows)
explain (costs off) select * from mc2p where a is null;
QUERY PLAN
-------------------------------
Seq Scan on mc2p_default mc2p
- Filter: (a IS NULL)
+ Scan Cond: (a IS NULL)
(2 rows)
explain (costs off) select * from mc2p where b is null;
QUERY PLAN
-------------------------------
Seq Scan on mc2p_default mc2p
- Filter: (b IS NULL)
+ Scan Cond: (b IS NULL)
(2 rows)
-- boolean partitioning
@@ -1463,7 +1472,7 @@ EXPLAIN (COSTS OFF) SELECT tableoid::regclass as part, a, b FROM part WHERE a IS
Sort
Sort Key: ((part.tableoid)::regclass), part.a, part.b
-> Seq Scan on part_p2_p1 part
- Filter: (a IS NULL)
+ Scan Cond: (a IS NULL)
(4 rows)
EXPLAIN (VERBOSE, COSTS OFF) SELECT * FROM part p(x) ORDER BY x;
@@ -1638,14 +1647,16 @@ explain (costs off) select * from lp where (a <> 'a' and a <> 'd') or a is null;
-- which in this case are partitions of rlp that are themselves
-- list-partitioned on b
explain (costs off) select * from rlp where a = 15 and b <> 'ab' and b <> 'cd' and b <> 'xy' and b is not null;
- QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------
+ QUERY PLAN
+----------------------------------------------------------------------------------------------------------------------
Append
-> Seq Scan on rlp3efgh rlp_1
- Filter: ((b IS NOT NULL) AND ((b)::text <> 'ab'::text) AND ((b)::text <> 'cd'::text) AND ((b)::text <> 'xy'::text) AND (a = 15))
+ Scan Cond: (b IS NOT NULL)
+ Filter: (((b)::text <> 'ab'::text) AND ((b)::text <> 'cd'::text) AND ((b)::text <> 'xy'::text) AND (a = 15))
-> Seq Scan on rlp3_default rlp_2
- Filter: ((b IS NOT NULL) AND ((b)::text <> 'ab'::text) AND ((b)::text <> 'cd'::text) AND ((b)::text <> 'xy'::text) AND (a = 15))
-(5 rows)
+ Scan Cond: (b IS NOT NULL)
+ Filter: (((b)::text <> 'ab'::text) AND ((b)::text <> 'cd'::text) AND ((b)::text <> 'xy'::text) AND (a = 15))
+(7 rows)
--
-- different collations for different keys with same expression
@@ -1796,31 +1807,31 @@ explain (costs off) select * from hp where b = 'xxx';
(9 rows)
explain (costs off) select * from hp where a is null;
- QUERY PLAN
------------------------------
+ QUERY PLAN
+--------------------------------
Append
-> Seq Scan on hp0 hp_1
- Filter: (a IS NULL)
+ Scan Cond: (a IS NULL)
-> Seq Scan on hp1 hp_2
- Filter: (a IS NULL)
+ Scan Cond: (a IS NULL)
-> Seq Scan on hp2 hp_3
- Filter: (a IS NULL)
+ Scan Cond: (a IS NULL)
-> Seq Scan on hp3 hp_4
- Filter: (a IS NULL)
+ Scan Cond: (a IS NULL)
(9 rows)
explain (costs off) select * from hp where b is null;
- QUERY PLAN
------------------------------
+ QUERY PLAN
+--------------------------------
Append
-> Seq Scan on hp0 hp_1
- Filter: (b IS NULL)
+ Scan Cond: (b IS NULL)
-> Seq Scan on hp1 hp_2
- Filter: (b IS NULL)
+ Scan Cond: (b IS NULL)
-> Seq Scan on hp2 hp_3
- Filter: (b IS NULL)
+ Scan Cond: (b IS NULL)
-> Seq Scan on hp3 hp_4
- Filter: (b IS NULL)
+ Scan Cond: (b IS NULL)
(9 rows)
explain (costs off) select * from hp where a < 1 and b = 'xxx';
@@ -1868,18 +1879,19 @@ explain (costs off) select * from hp where a <> 1 and b <> 'xxx';
-- pruning should work if either a value or a IS NULL clause is provided for
-- each of the keys
explain (costs off) select * from hp where a is null and b is null;
- QUERY PLAN
------------------------------------------
+ QUERY PLAN
+--------------------------------------------
Seq Scan on hp0 hp
- Filter: ((a IS NULL) AND (b IS NULL))
+ Scan Cond: ((a IS NULL) AND (b IS NULL))
(2 rows)
explain (costs off) select * from hp where a = 1 and b is null;
- QUERY PLAN
--------------------------------------
+ QUERY PLAN
+--------------------------
Seq Scan on hp1 hp
- Filter: ((b IS NULL) AND (a = 1))
-(2 rows)
+ Scan Cond: (b IS NULL)
+ Filter: (a = 1)
+(3 rows)
explain (costs off) select * from hp where a = 1 and b = 'xxx';
QUERY PLAN
@@ -1889,11 +1901,12 @@ explain (costs off) select * from hp where a = 1 and b = 'xxx';
(2 rows)
explain (costs off) select * from hp where a is null and b = 'xxx';
- QUERY PLAN
------------------------------------------------
+ QUERY PLAN
+-----------------------------
Seq Scan on hp2 hp
- Filter: ((a IS NULL) AND (b = 'xxx'::text))
-(2 rows)
+ Scan Cond: (a IS NULL)
+ Filter: (b = 'xxx'::text)
+(3 rows)
explain (costs off) select * from hp where a = 2 and b = 'xxx';
QUERY PLAN
@@ -4016,11 +4029,12 @@ create table hp_prefix_test_p1 partition of hp_prefix_test for values with (modu
create table hp_prefix_test_p2 partition of hp_prefix_test for values with (modulus 2, remainder 1);
-- Test that get_steps_using_prefix() handles non-NULL step_nullkeys
explain (costs off) select * from hp_prefix_test where a = 1 and b is null and c = 1 and d = 1;
- QUERY PLAN
--------------------------------------------------------------
+ QUERY PLAN
+----------------------------------------------
Seq Scan on hp_prefix_test_p1 hp_prefix_test
- Filter: ((b IS NULL) AND (a = 1) AND (c = 1) AND (d = 1))
-(2 rows)
+ Scan Cond: (b IS NULL)
+ Filter: ((a = 1) AND (c = 1) AND (d = 1))
+(3 rows)
drop table rp_prefix_test1;
drop table rp_prefix_test2;
diff --git a/src/test/regress/expected/sysviews.out b/src/test/regress/expected/sysviews.out
index aae5d51e1c..c00debff81 100644
--- a/src/test/regress/expected/sysviews.out
+++ b/src/test/regress/expected/sysviews.out
@@ -130,9 +130,10 @@ select name, setting from pg_settings where name like 'enable%';
enable_partitionwise_join | off
enable_presorted_aggregate | on
enable_seqscan | on
+ enable_seqscan_conds | on
enable_sort | on
enable_tidscan | on
-(21 rows)
+(22 rows)
-- There are always wait event descriptions for various types.
select type, count(*) > 0 as ok FROM pg_wait_events
diff --git a/src/test/regress/expected/union.out b/src/test/regress/expected/union.out
index e2613d6777..aa1be124b2 100644
--- a/src/test/regress/expected/union.out
+++ b/src/test/regress/expected/union.out
@@ -1303,8 +1303,8 @@ select distinct q1 from
union all
select distinct * from int8_tbl i82) ss
where q2 = q2;
- QUERY PLAN
-----------------------------------------------------------
+ QUERY PLAN
+-------------------------------------------------------------
Unique
-> Merge Append
Sort Key: "*SELECT* 1".q1
@@ -1313,13 +1313,13 @@ where q2 = q2;
-> Sort
Sort Key: i81.q1, i81.q2
-> Seq Scan on int8_tbl i81
- Filter: (q2 IS NOT NULL)
+ Scan Cond: (q2 IS NOT NULL)
-> Subquery Scan on "*SELECT* 2"
-> Unique
-> Sort
Sort Key: i82.q1, i82.q2
-> Seq Scan on int8_tbl i82
- Filter: (q2 IS NOT NULL)
+ Scan Cond: (q2 IS NOT NULL)
(15 rows)
select distinct q1 from
--
2.34.1
From 707bedf6638eab4a532961fc53c3cd28aa883cb1 Mon Sep 17 00:00:00 2001
From: Jacob Champion <jchamp...@timescale.com>
Date: Tue, 6 Jun 2023 15:57:19 -0700
Subject: [PATCH v3 3/3] WIP: naive benchmarks
Four benchmarks (bench1-3, 5) for varying degrees of selectivity
depending on the table used. Two benchmarks (bench4, wide) for 1.0
selectivity, on both a single column and a larger number of columns.
$ psql -f ./init postgres
$ ./run
---
bench1 | 1 +
bench2 | 1 +
bench3 | 1 +
bench4 | 1 +
bench5 | 1 +
benchwide | 4 ++++
init | 57 +++++++++++++++++++++++++++++++++++++++++++++++++++++++
run | 26 +++++++++++++++++++++++++
8 files changed, 92 insertions(+)
create mode 100644 bench1
create mode 100644 bench2
create mode 100644 bench3
create mode 100644 bench4
create mode 100644 bench5
create mode 100644 benchwide
create mode 100755 init
create mode 100755 run
diff --git a/bench1 b/bench1
new file mode 100644
index 0000000000..9cb32d4fcb
--- /dev/null
+++ b/bench1
@@ -0,0 +1 @@
+SELECT COUNT(i) FROM :table;
diff --git a/bench2 b/bench2
new file mode 100644
index 0000000000..1377bae0f5
--- /dev/null
+++ b/bench2
@@ -0,0 +1 @@
+SELECT COUNT(*) FROM :table WHERE i IS NOT NULL;
diff --git a/bench3 b/bench3
new file mode 100644
index 0000000000..524a140c0a
--- /dev/null
+++ b/bench3
@@ -0,0 +1 @@
+SELECT COUNT(*) FROM :table WHERE i IS NULL;
diff --git a/bench4 b/bench4
new file mode 100644
index 0000000000..c8216537db
--- /dev/null
+++ b/bench4
@@ -0,0 +1 @@
+SELECT COUNT(*) FROM :table WHERE i > 0;
diff --git a/bench5 b/bench5
new file mode 100644
index 0000000000..e075a0566f
--- /dev/null
+++ b/bench5
@@ -0,0 +1 @@
+SELECT COUNT(*) FROM :table WHERE abs(i) % 2 = 0;
diff --git a/benchwide b/benchwide
new file mode 100644
index 0000000000..90a77741f6
--- /dev/null
+++ b/benchwide
@@ -0,0 +1,4 @@
+SELECT COUNT(*) FROM wide
+ WHERE i <> 0 AND j <> 0 AND k <> 0 AND l <> 0 AND m <> 0 AND n <> 0 AND o <> 0
+ AND p <> 0 AND q <> 0 AND r <> 0 AND s <> 0 AND t <> 0 AND u <> 0 AND v <> 0
+ AND w <> 0 AND x <> 0 AND y <> 0 AND z <> 0;
diff --git a/init b/init
new file mode 100755
index 0000000000..1a18d67b44
--- /dev/null
+++ b/init
@@ -0,0 +1,57 @@
+-- Tables are named after the percentage of values that are non-NULL.
+DROP TABLE IF EXISTS zero;
+DROP TABLE IF EXISTS five;
+DROP TABLE IF EXISTS ten;
+DROP TABLE IF EXISTS twenty;
+DROP TABLE IF EXISTS fifty;
+DROP TABLE IF EXISTS eighty;
+DROP TABLE IF EXISTS ninety;
+DROP TABLE IF EXISTS ninetyfive;
+DROP TABLE IF EXISTS hundred;
+
+DROP TABLE IF EXISTS wide;
+
+\if :{?scale}
+\else
+ \set scale 1
+\endif
+
+CREATE TABLE zero AS
+ SELECT NULL::int AS i FROM generate_series(1, 100000 * :scale);
+
+CREATE TABLE five AS
+ SELECT CASE WHEN random() < 0.05 THEN i ELSE NULL END AS i
+ FROM generate_series(1, 100000 * :scale) i;
+
+CREATE TABLE ten AS
+ SELECT CASE WHEN random() < 0.1 THEN i ELSE NULL END AS i
+ FROM generate_series(1, 100000 * :scale) i;
+
+CREATE TABLE twenty AS
+ SELECT CASE WHEN random() < 0.2 THEN i ELSE NULL END AS i
+ FROM generate_series(1, 100000 * :scale) i;
+
+CREATE TABLE fifty AS
+ SELECT CASE WHEN random() < 0.5 THEN i ELSE NULL END AS i
+ FROM generate_series(1, 100000 * :scale) i;
+
+CREATE TABLE eighty AS
+ SELECT CASE WHEN random() < 0.8 THEN i ELSE NULL END AS i
+ FROM generate_series(1, 100000 * :scale) i;
+
+CREATE TABLE ninety AS
+ SELECT CASE WHEN random() < 0.9 THEN i ELSE NULL END AS i
+ FROM generate_series(1, 100000 * :scale) i;
+
+CREATE TABLE ninetyfive AS
+ SELECT CASE WHEN random() < 0.95 THEN i ELSE NULL END AS i
+ FROM generate_series(1, 100000 * :scale) i;
+
+CREATE TABLE hundred AS
+ SELECT i FROM generate_series(1, 100000 * :scale) i;
+
+CREATE TABLE wide AS
+ SELECT i, 2 AS j, 3 AS k, 4 AS l, 5 AS m, 6 AS n, 7 AS o, 8 AS p, 9 AS q,
+ 10 AS r, 11 AS s, 12 AS t, 13 AS u, 14 AS v, 15 AS w, 16 AS x, 17 AS y,
+ 18 AS z
+ FROM generate_series(1, 100000 * :scale) i;
diff --git a/run b/run
new file mode 100755
index 0000000000..14563b92cf
--- /dev/null
+++ b/run
@@ -0,0 +1,26 @@
+#! /bin/bash
+
+function bench()
+{
+ local index=$1
+
+ for t in zero five ten twenty fifty eighty ninety ninetyfive hundred; do
+ echo "= $index: $t ="
+ pgbench -n -f ./bench$index -T5 -c1 -j1 -Dtable=$t postgres
+ done;
+}
+
+function all()
+{
+ for i in 1 2 3 4 5; do
+ bench $i
+ echo
+ done
+
+ echo "= wide ="
+ pgbench -n -f ./benchwide -T5 -c1 -j1 postgres
+}
+
+for typ in on off; do
+ PGOPTIONS="-c enable_seqscan_conds=$typ" all > $typ.perf
+done
--
2.34.1
