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 |
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 |