Replace IN VALUES with ANY in WHERE clauses during optimization

From: Ivan Kush <ivan(dot)kush(at)tantorlabs(dot)com>
To: pgsql-hackers <pgsql-hackers(at)postgresql(dot)org>
Subject: Replace IN VALUES with ANY in WHERE clauses during optimization
Date: 2024-10-03 19:52:48
Message-ID: 0184212d-1248-4f1f-a42d-f5cb1c1976d2@tantorlabs.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers


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(dot)kush(at)tantorlabs(dot)com>
Author: Vadim Yacenko <vadim(dot)yacenko(at)tantorlabs(dot)com>
Author: Alexander Simonov <alexander(dot)simonov(at)tantorlabs(dot)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

Attachment Content-Type Size
commands.sql application/sql 2.0 KB
plan_no_patch.txt text/plain 4.1 KB
plan_with_patch.txt text/plain 4.0 KB
v1-in_values_to_any.patch text/x-patch 28.7 KB

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Laurenz Albe 2024-10-03 20:10:14 Re: On disable_cost
Previous Message Andres Freund 2024-10-03 19:40:26 Re: bgwrite process is too lazy