Skip to content

2.25.2.0-b164

2.25.2.0-b164: [#26266] YSQL: Add BOOL_LSM_FAM_OID to boolean family
@pao214 pao214 tagged this 14 Mar 17:30 
Summary:
### Issue

Consider the setup

```
CREATE TABLE tbl (b BOOLEAN, PRIMARY KEY(b ASC));
```

The planner never picks an index scan for the below query

```
EXPLAIN SELECT b FROM tbl WHERE b;
                       QUERY PLAN
--------------------------------------------------------
 Seq Scan on tbl  (cost=0.00..100.00 rows=1000 width=1)
   Storage Filter: b
(2 rows)
```

### Cause

YugabyteDB uses LSM index and hence the boolean operator family is BOOL_LSM_FAM_OID.
Moreover, postgres planner skips catalog reads for builtin boolean operators since it already has a list of operator families it considers boolean.

The list of boolean operator families is defined by the following code

```
#define IsBooleanOpfamily(opfamily) \
	((opfamily) == BOOL_BTREE_FAM_OID || (opfamily) == BOOL_HASH_FAM_OID)
```

and used in several places.

### Issue Impact

Since BOOL_LSM_FAM_OID is not part of IsBooleanOpfamily, it causes two inefficiencies:

1. Boolean conditions are NOT considered for index predicates as shown in the example above.

```
match_clause_to_indexcol(...)
...
	/* First check for boolean-index cases. */
	opfamily = index->opfamily[indexcol];
	if (IsBooleanOpfamily(opfamily))
	{
```

2. Boolean conditions are NOT considered for partition pruning.

Code usage:

```
match_boolean_partition_clause(...)
...
	if (!IsBooleanOpfamily(partopfamily))
		return PARTCLAUSE_UNSUPPORTED;
```

Example:

```
CREATE TABLE boolpart (a bool) PARTITION BY LIST (a);
CREATE TABLE boolpart_default PARTITION OF boolpart DEFAULT;
CREATE TABLE boolpart_t PARTITION OF boolpart FOR VALUES IN ('true');
CREATE TABLE boolpart_f PARTITION OF boolpart FOR VALUES IN ('false');

EXPLAIN (COSTS OFF) SELECT * FROM boolpart WHERE a = false;
                  QUERY PLAN
-----------------------------------------------
 Append
   ->  Seq Scan on boolpart_f boolpart_1
         Storage Filter: (NOT a)
   ->  Seq Scan on boolpart_t boolpart_2
         Storage Filter: (NOT a)
   ->  Seq Scan on boolpart_default boolpart_3
         Storage Filter: (NOT a)
(7 rows)
```

Supposed to scan only boolpart_f.

### Fix

Add BOOL_LSM_FAM_OID to the list of boolean op families.

```
#define IsBooleanOpfamily(opfamily) \
	((opfamily) == BOOL_BTREE_FAM_OID || (opfamily) == BOOL_HASH_FAM_OID || (opfamily) == BOOL_LSM_FAM_OID)
```

Fixes #26266
Jira: DB-15610

Test Plan:
Jenkins

Backport-through: 2.20

Reviewers: jason, telgersma, tnayak, smishra

Reviewed By: jason

Subscribers: yql

Differential Revision: https://phorge.dev.yugabyte.com/D42529
Assets 2
Loading