Horton

Replace IN VALUES with ANY in WHERE clauses during optimization

ivan.kush@tantorlabs.com

View thread
From: Ivan Kush <ivan.kush@tantorlabs.com>
To: pgsql-hackers <pgsql-hackers@postgresql.org>
Date: 2024-10-03T19:52:48Z
Lists: pgsql-hackers

Commits

Same data as JSON: GET /api/v1/messages/:b64id/commits the thread's linked commits as JSON, with link sources. API reference →

  1. Stabilize regression test from c0962a113.

  2. Convert 'x IN (VALUES ...)' to 'x = ANY ...' then appropriate

  3. Extract make_SAOP_expr() function from match_orclause_to_indexcol()

Attachments

Hello, hackers! I with my friends propose the patch to replace IN VALUES 
to ANY in WHERE clauses.

# Intro

The `VALUES` in the `IN VALUES` construct is replaced with with an array 
of values when `VALUES` contains 1 column. In the end it will be 
replaced with ANY by the existing function makeA_Expr 
(src/backend/nodes/makefuncs.c)

This improves performance, especially if the values are small.

# Patch

v1-in_values_to_array.patch

# How realized

`VALUES` statement corresponds to `values_clause` nonterminal symbol in 
gram.y, where it's parsed to `SelectStmt` node.

`IN` is parsed in `a_expr` symbol. When it contains `VALUES` with 1 
column, parser extracts data from `SelectStmt` and passes it

to function call `makeSimpleA_Expr` where simple `A_Expr` is created.

Later during optimizations of parser tree this `A_Expr` will be 
transformed to `ArrayExpr` (already realized in Postgres)


# Authors.
Author: Ivan Kush <ivan.kush@tantorlabs.com>
Author: Vadim Yacenko <vadim.yacenko@tantorlabs.com>
Author: Alexander Simonov <alexander.simonov@tantorlabs.com>

# Tests
Implementation contains many regression tests of varying complexity, 
which check supported features.

# Platform
This patch was checkouted from tag REL_17_STABLE. Code is developed in 
Linux, doesn't contain platfrom-specific code, only Postgres internal 
data structures and functions.

# Documentation
Regression tests contain many examples

# Performance
It increases performance

# Example
Let's compare result. With path the execution time is significantly lower.

We have a table table1 with 10000 rows.

postgres=# \d table1;
                          Table "public.table1"
  Column |            Type             | Collation | Nullable | Default
--------+-----------------------------+-----------+----------+---------
  fld1   | timestamp without time zone |           | not null |
  fld2   | bytea                       |           | not null |
Indexes:
     "table1index" btree (fld2)

Let's execute several commands
see commands.sql

Plan no patch
see plan_no_patch.txt


Plan with patch
see plan_with_patch.txt


--
Best wishes,
Ivan Kush
Tantor Labs LLC