public inbox for [email protected]
help / color / mirror / Atom feed[PATCH v45 5/7] Doc part of shared-memory based stats collector.
3+ messages / 2 participants
[nested] [flat]
* [PATCH v45 5/7] Doc part of shared-memory based stats collector.
@ 2020-03-19 06:11 Kyotaro Horiguchi <[email protected]>
0 siblings, 0 replies; 3+ messages in thread
From: Kyotaro Horiguchi @ 2020-03-19 06:11 UTC (permalink / raw)
---
doc/src/sgml/catalogs.sgml | 6 +-
doc/src/sgml/config.sgml | 34 ++++----
doc/src/sgml/high-availability.sgml | 13 +--
doc/src/sgml/monitoring.sgml | 127 +++++++++++++---------------
doc/src/sgml/ref/pg_dump.sgml | 9 +-
5 files changed, 90 insertions(+), 99 deletions(-)
diff --git a/doc/src/sgml/catalogs.sgml b/doc/src/sgml/catalogs.sgml
index 3a2266526c..4d8b92df72 100644
--- a/doc/src/sgml/catalogs.sgml
+++ b/doc/src/sgml/catalogs.sgml
@@ -9234,9 +9234,9 @@ SCRAM-SHA-256$<replaceable><iteration count></replaceable>:<replaceable>&l
<para>
<xref linkend="view-table"/> lists the system views described here.
More detailed documentation of each view follows below.
- There are some additional views that provide access to the results of
- the statistics collector; they are described in <xref
- linkend="monitoring-stats-views-table"/>.
+ There are some additional views that provide access to the activity
+ statistics; they are described in
+ <xref linkend="monitoring-stats-views-table"/>.
</para>
<para>
diff --git a/doc/src/sgml/config.sgml b/doc/src/sgml/config.sgml
index 7c0a673a8d..f6c80df988 100644
--- a/doc/src/sgml/config.sgml
+++ b/doc/src/sgml/config.sgml
@@ -7327,11 +7327,11 @@ COPY postgres_log FROM '/full/path/to/logfile.csv' WITH csv;
<title>Run-time Statistics</title>
<sect2 id="runtime-config-statistics-collector">
- <title>Query and Index Statistics Collector</title>
+ <title>Query and Index Activity Statistics</title>
<para>
- These parameters control server-wide statistics collection features.
- When statistics collection is enabled, the data that is produced can be
+ These parameters control server-wide activity statistics features.
+ When activity statistics is enabled, the data that is produced can be
accessed via the <structname>pg_stat</structname> and
<structname>pg_statio</structname> family of system views.
Refer to <xref linkend="monitoring"/> for more information.
@@ -7347,14 +7347,13 @@ COPY postgres_log FROM '/full/path/to/logfile.csv' WITH csv;
</term>
<listitem>
<para>
- Enables the collection of information on the currently
- executing command of each session, along with the time when
- that command began execution. This parameter is on by
- default. Note that even when enabled, this information is not
- visible to all users, only to superusers and the user owning
- the session being reported on, so it should not represent a
- security risk.
- Only superusers can change this setting.
+ Enables activity tracking on the currently executing command of
+ each session, along with the time when that command began
+ execution. This parameter is on by default. Note that even when
+ enabled, this information is not visible to all users, only to
+ superusers and the user owning the session being reported on, so it
+ should not represent a security risk. Only superusers can change this
+ setting.
</para>
</listitem>
</varlistentry>
@@ -7385,9 +7384,9 @@ COPY postgres_log FROM '/full/path/to/logfile.csv' WITH csv;
</term>
<listitem>
<para>
- Enables collection of statistics on database activity.
+ Enables tracking of database activity.
This parameter is on by default, because the autovacuum
- daemon needs the collected information.
+ daemon needs the activity information.
Only superusers can change this setting.
</para>
</listitem>
@@ -8485,7 +8484,7 @@ COPY postgres_log FROM '/full/path/to/logfile.csv' WITH csv;
<listitem>
<para>
Specifies the fraction of the total number of heap tuples counted in
- the previous statistics collection that can be inserted without
+ the previously collected statistics that can be inserted without
incurring an index scan at the <command>VACUUM</command> cleanup stage.
This setting currently applies to B-tree indexes only.
</para>
@@ -8497,9 +8496,10 @@ COPY postgres_log FROM '/full/path/to/logfile.csv' WITH csv;
the index contains deleted pages that can be recycled during cleanup.
Index statistics are considered to be stale if the number of newly
inserted tuples exceeds the <varname>vacuum_cleanup_index_scale_factor</varname>
- fraction of the total number of heap tuples detected by the previous
- statistics collection. The total number of heap tuples is stored in
- the index meta-page. Note that the meta-page does not include this data
+
+ fraction of the total number of heap tuples in the previously
+ collected statistics. The total number of heap tuples is stored in the
+ index meta-page. Note that the meta-page does not include this data
until <command>VACUUM</command> finds no dead tuples, so B-tree index
scan at the cleanup stage can only be skipped if the second and
subsequent <command>VACUUM</command> cycles detect no dead tuples.
diff --git a/doc/src/sgml/high-availability.sgml b/doc/src/sgml/high-availability.sgml
index efc382cb8d..6c620469eb 100644
--- a/doc/src/sgml/high-availability.sgml
+++ b/doc/src/sgml/high-availability.sgml
@@ -2338,12 +2338,13 @@ LOG: database system is ready to accept read only connections
</para>
<para>
- The statistics collector is active during recovery. All scans, reads, blocks,
- index usage, etc., will be recorded normally on the standby. Replayed
- actions will not duplicate their effects on primary, so replaying an
- insert will not increment the Inserts column of pg_stat_user_tables.
- The stats file is deleted at the start of recovery, so stats from primary
- and standby will differ; this is considered a feature, not a bug.
+ The activity statistics is collected during recovery. All scans, reads,
+ blocks, index usage, etc., will be recorded normally on the
+ standby. Replayed actions will not duplicate their effects on primary, so
+ replaying an insert will not increment the Inserts column of
+ pg_stat_user_tables. The activity statistics is reset at the start of
+ recovery, so stats from primary and standby will differ; this is
+ considered a feature, not a bug.
</para>
<para>
diff --git a/doc/src/sgml/monitoring.sgml b/doc/src/sgml/monitoring.sgml
index 3cdb1aff3c..afa8c35127 100644
--- a/doc/src/sgml/monitoring.sgml
+++ b/doc/src/sgml/monitoring.sgml
@@ -22,7 +22,7 @@
<para>
Several tools are available for monitoring database activity and
analyzing performance. Most of this chapter is devoted to describing
- <productname>PostgreSQL</productname>'s statistics collector,
+ <productname>PostgreSQL</productname>'s activity statistics,
but one should not neglect regular Unix monitoring programs such as
<command>ps</command>, <command>top</command>, <command>iostat</command>, and <command>vmstat</command>.
Also, once one has identified a
@@ -53,7 +53,6 @@ postgres 15554 0.0 0.0 57536 1184 ? Ss 18:02 0:00 postgres: back
postgres 15555 0.0 0.0 57536 916 ? Ss 18:02 0:00 postgres: checkpointer
postgres 15556 0.0 0.0 57536 916 ? Ss 18:02 0:00 postgres: walwriter
postgres 15557 0.0 0.0 58504 2244 ? Ss 18:02 0:00 postgres: autovacuum launcher
-postgres 15558 0.0 0.0 17512 1068 ? Ss 18:02 0:00 postgres: stats collector
postgres 15582 0.0 0.0 58772 3080 ? Ss 18:04 0:00 postgres: joe runbug 127.0.0.1 idle
postgres 15606 0.0 0.0 58772 3052 ? Ss 18:07 0:00 postgres: tgl regression [local] SELECT waiting
postgres 15610 0.0 0.0 58772 3056 ? Ss 18:07 0:00 postgres: tgl regression [local] idle in transaction
@@ -65,9 +64,8 @@ postgres 15610 0.0 0.0 58772 3056 ? Ss 18:07 0:00 postgres: tgl
primary server process. The command arguments
shown for it are the same ones used when it was launched. The next five
processes are background worker processes automatically launched by the
- primary process. (The <quote>stats collector</quote> process will not be present
- if you have set the system not to start the statistics collector; likewise
- the <quote>autovacuum launcher</quote> process can be disabled.)
+ primary process. (The <quote>autovacuum launcher</quote> process will not
+ be present if you have set the system not to start it.)
Each of the remaining
processes is a server process handling one client connection. Each such
process sets its command line display in the form
@@ -130,20 +128,21 @@ postgres 27093 0.0 0.0 30096 2752 ? Ss 11:34 0:00 postgres: ser
</sect1>
<sect1 id="monitoring-stats">
- <title>The Statistics Collector</title>
+ <title>The Activity Statistics</title>
<indexterm zone="monitoring-stats">
<primary>statistics</primary>
</indexterm>
<para>
- <productname>PostgreSQL</productname>'s <firstterm>statistics collector</firstterm>
- is a subsystem that supports collection and reporting of information about
- server activity. Presently, the collector can count accesses to tables
- and indexes in both disk-block and individual-row terms. It also tracks
- the total number of rows in each table, and information about vacuum and
- analyze actions for each table. It can also count calls to user-defined
- functions and the total time spent in each one.
+ <productname>PostgreSQL</productname>'s <firstterm>activity
+ statistics</firstterm> is a subsystem that supports tracking and reporting
+ of information about server activity. Presently, the activity statistics
+ tracks the count of accesses to tables and indexes in both disk-block and
+ individual-row terms. It also tracks the total number of rows in each
+ table, and information about vacuum and analyze actions for each table. It
+ can also track calls to user-defined functions and the total time spent in
+ each one.
</para>
<para>
@@ -151,15 +150,15 @@ postgres 27093 0.0 0.0 30096 2752 ? Ss 11:34 0:00 postgres: ser
information about exactly what is going on in the system right now, such as
the exact command currently being executed by other server processes, and
which other connections exist in the system. This facility is independent
- of the collector process.
+ of the activity statistics.
</para>
<sect2 id="monitoring-stats-setup">
- <title>Statistics Collection Configuration</title>
+ <title>Activity Statistics Configuration</title>
<para>
- Since collection of statistics adds some overhead to query execution,
- the system can be configured to collect or not collect information.
+ Since tracking for the activity statistics adds some overhead to query
+ execution, the system can be configured to track or not track activity.
This is controlled by configuration parameters that are normally set in
<filename>postgresql.conf</filename>. (See <xref linkend="runtime-config"/> for
details about setting configuration parameters.)
@@ -172,7 +171,7 @@ postgres 27093 0.0 0.0 30096 2752 ? Ss 11:34 0:00 postgres: ser
<para>
The parameter <xref linkend="guc-track-counts"/> controls whether
- statistics are collected about table and index accesses.
+ to track activity about table and index accesses.
</para>
<para>
@@ -196,18 +195,11 @@ postgres 27093 0.0 0.0 30096 2752 ? Ss 11:34 0:00 postgres: ser
</para>
<para>
- The statistics collector transmits the collected information to other
- <productname>PostgreSQL</productname> processes through temporary files.
- These files are stored in the directory named by the
- <xref linkend="guc-stats-temp-directory"/> parameter,
- <filename>pg_stat_tmp</filename> by default.
- For better performance, <varname>stats_temp_directory</varname> can be
- pointed at a RAM-based file system, decreasing physical I/O requirements.
- When the server shuts down cleanly, a permanent copy of the statistics
- data is stored in the <filename>pg_stat</filename> subdirectory, so that
- statistics can be retained across server restarts. When recovery is
- performed at server start (e.g., after immediate shutdown, server crash,
- and point-in-time recovery), all statistics counters are reset.
+ down cleanly, a permanent copy of the statistics data is stored in
+ the <filename>pg_stat</filename> subdirectory, so that statistics can be
+ retained across server restarts. When recovery is performed at server
+ start (e.g. after immediate shutdown, server crash, and point-in-time
+ recovery), all statistics counters are reset.
</para>
</sect2>
@@ -220,48 +212,46 @@ postgres 27093 0.0 0.0 30096 2752 ? Ss 11:34 0:00 postgres: ser
linkend="monitoring-stats-dynamic-views-table"/>, are available to show
the current state of the system. There are also several other
views, listed in <xref
- linkend="monitoring-stats-views-table"/>, available to show the results
- of statistics collection. Alternatively, one can
- build custom views using the underlying statistics functions, as discussed
- in <xref linkend="monitoring-stats-functions"/>.
+ linkend="monitoring-stats-views-table"/>, available to show the activity
+ statistics. Alternatively, one can build custom views using the underlying
+ statistics functions, as discussed in
+ <xref linkend="monitoring-stats-functions"/>.
</para>
<para>
- When using the statistics to monitor collected data, it is important
- to realize that the information does not update instantaneously.
- Each individual server process transmits new statistical counts to
- the collector just before going idle; so a query or transaction still in
- progress does not affect the displayed totals. Also, the collector itself
- emits a new report at most once per <varname>PGSTAT_STAT_INTERVAL</varname>
- milliseconds (500 ms unless altered while building the server). So the
- displayed information lags behind actual activity. However, current-query
- information collected by <varname>track_activities</varname> is
- always up-to-date.
+ When using the activity statistics, it is important to realize that the
+ information does not update instantaneously. Each individual server writes
+ out new statistical counts just before going idle, not frequent than once
+ per <varname>PGSTAT_STAT_INTERVAL</varname> milliseconds (1 second unless
+ altered while building the server); so a query or transaction still in
+ progress does not affect the displayed totals. However, current-query
+ information tracked by <varname>track_activities</varname> is always
+ up-to-date.
</para>
<para>
Another important point is that when a server process is asked to display
- any of these statistics, it first fetches the most recent report emitted by
- the collector process and then continues to use this snapshot for all
- statistical views and functions until the end of its current transaction.
- So the statistics will show static information as long as you continue the
- current transaction. Similarly, information about the current queries of
- all sessions is collected when any such information is first requested
- within a transaction, and the same information will be displayed throughout
- the transaction.
- This is a feature, not a bug, because it allows you to perform several
- queries on the statistics and correlate the results without worrying that
- the numbers are changing underneath you. But if you want to see new
- results with each query, be sure to do the queries outside any transaction
- block. Alternatively, you can invoke
+ any of these statistics, it first reads the current statistics and then
+ continues to use this snapshot for all statistical views and functions
+ until the end of its current transaction. So the statistics will show
+ static information as long as you continue the current transaction.
+ Similarly, information about the current queries of all sessions is tracked
+ when any such information is first requested within a transaction, and the
+ same information will be displayed throughout the transaction. This is a
+ feature, not a bug, because it allows you to perform several queries on the
+ statistics and correlate the results without worrying that the numbers are
+ changing underneath you. But if you want to see new results with each
+ query, be sure to do the queries outside any transaction block.
+ Alternatively, you can invoke
<function>pg_stat_clear_snapshot</function>(), which will discard the
current transaction's statistics snapshot (if any). The next use of
statistical information will cause a new snapshot to be fetched.
</para>
-
+
<para>
- A transaction can also see its own statistics (as yet untransmitted to the
- collector) in the views <structname>pg_stat_xact_all_tables</structname>,
+ A transaction can also see its own statistics (as yet unwritten to the
+ server-wide activity statistics) in the
+ views <structname>pg_stat_xact_all_tables</structname>,
<structname>pg_stat_xact_sys_tables</structname>,
<structname>pg_stat_xact_user_tables</structname>, and
<structname>pg_stat_xact_user_functions</structname>. These numbers do not act as
@@ -643,7 +633,7 @@ postgres 27093 0.0 0.0 30096 2752 ? Ss 11:34 0:00 postgres: ser
kernel's I/O cache, and might therefore still be fetched without
requiring a physical read. Users interested in obtaining more
detailed information on <productname>PostgreSQL</productname> I/O behavior are
- advised to use the <productname>PostgreSQL</productname> statistics collector
+ advised to use the <productname>PostgreSQL</productname> activity statistics
in combination with operating system utilities that allow insight
into the kernel's handling of I/O.
</para>
@@ -1080,10 +1070,6 @@ postgres 27093 0.0 0.0 30096 2752 ? Ss 11:34 0:00 postgres: ser
<entry><literal>LogicalLauncherMain</literal></entry>
<entry>Waiting in main loop of logical replication launcher process.</entry>
</row>
- <row>
- <entry><literal>PgStatMain</literal></entry>
- <entry>Waiting in main loop of statistics collector process.</entry>
- </row>
<row>
<entry><literal>RecoveryWalStream</literal></entry>
<entry>Waiting in main loop of startup process for WAL to arrive, during
@@ -1838,6 +1824,10 @@ postgres 27093 0.0 0.0 30096 2752 ? Ss 11:34 0:00 postgres: ser
</thead>
<tbody>
+ <row>
+ <entry><literal>ActivityStatistics</literal></entry>
+ <entry>Waiting to write out activity statistics to shared memory.</entry>
+ </row>
<row>
<entry><literal>AddinShmemInit</literal></entry>
<entry>Waiting to manage an extension's space allocation in shared
@@ -5996,9 +5986,10 @@ SELECT pg_stat_get_backend_pid(s.backendid) AS pid,
<entry><literal>performing final cleanup</literal></entry>
<entry>
<command>VACUUM</command> is performing final cleanup. During this phase,
- <command>VACUUM</command> will vacuum the free space map, update statistics
- in <literal>pg_class</literal>, and report statistics to the statistics
- collector. When this phase is completed, <command>VACUUM</command> will end.
+ <command>VACUUM</command> will vacuum the free space map, update
+ statistics in <literal>pg_class</literal>, and system-wide activity
+ statistics. When this phase is completed, <command>VACUUM</command>
+ will end.
</entry>
</row>
</tbody>
diff --git a/doc/src/sgml/ref/pg_dump.sgml b/doc/src/sgml/ref/pg_dump.sgml
index dcb25dc3cd..7507783eaa 100644
--- a/doc/src/sgml/ref/pg_dump.sgml
+++ b/doc/src/sgml/ref/pg_dump.sgml
@@ -1280,11 +1280,10 @@ PostgreSQL documentation
</para>
<para>
- The database activity of <application>pg_dump</application> is
- normally collected by the statistics collector. If this is
- undesirable, you can set parameter <varname>track_counts</varname>
- to false via <envar>PGOPTIONS</envar> or the <literal>ALTER
- USER</literal> command.
+ The database activity of <application>pg_dump</application> is normally
+ collected. If this is undesirable, you can set
+ parameter <varname>track_counts</varname> to false
+ via <envar>PGOPTIONS</envar> or the <literal>ALTER USER</literal> command.
</para>
</refsect1>
--
2.27.0
----Next_Part(Fri_Jan__8_10_24_34_2021_185)--
Content-Type: Text/X-Patch; charset=us-ascii
Content-Transfer-Encoding: 7bit
Content-Disposition: inline;
filename="v45-0006-Remove-the-GUC-stats_temp_directory.patch"
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: POC, WIP: OR-clause support for indexes
@ 2023-06-27 13:19 Alena Rybakina <[email protected]>
2023-06-27 15:49 ` Re: POC, WIP: OR-clause support for indexes Alena Rybakina <[email protected]>
0 siblings, 1 reply; 3+ messages in thread
From: Alena Rybakina @ 2023-06-27 13:19 UTC (permalink / raw)
To: Peter Geoghegan <[email protected]>; +Cc: Marcos Pegoraro <[email protected]>; Alena Rybakina <[email protected]>; Andrey Lepikhov <[email protected]>; pgsql-hackers; [email protected]
On 26.06.2023 06:18, Peter Geoghegan wrote:
> On Sun, Jun 25, 2023 at 6:48 PM Alena Rybakina<[email protected]> wrote:
>> I finished writing the code patch for transformation "Or" expressions to "Any" expressions.
> This seems interesting to me. I'm currently working on improving
> nbtree's "native execution of ScalarArrayOpExpr quals" (see commit
> 9e8da0f7 for background information). That is relevant to what you're
> trying to do here.
>
> Right now nbtree's handling of ScalarArrayOpExpr is rather
> inefficient. The executor does pass the index scan an array of
> constants, so the whole structure already allows the nbtree code to
> execute the ScalarArrayOpExpr in whatever way would be most efficient.
> There is only one problem: it doesn't really try to do so. It more or
> less just breaks down the large ScalarArrayOpExpr into "mini" queries
> -- one per constant. Internally, query execution isn't significantly
> different to executing many of these "mini" queries independently. We
> just sort and deduplicate the arrays. We don't intelligently decide
> which pages dynamically. This is related to skip scan.
>
> Attached is an example query that shows the problem. Right now the
> query needs to access a buffer containing an index page a total of 24
> times. It's actually accessing the same 2 pages 12 times. My draft
> patch only requires 2 buffer accesses -- because it "coalesces the
> array constants together" dynamically at run time. That is a little
> extreme, but it's certainly possible.
>
> BTW, this project is related to skip scan. It's part of the same
> family of techniques -- MDAM techniques. (I suppose that that's
> already true for ScalarArrayOpExpr execution by nbtree, but without
> dynamic behavior it's not nearly as valuable as it could be.)
>
> If executing ScalarArrayOpExprs was less inefficient in these cases
> then the planner could be a lot more aggressive about using them.
> Seems like these executor improvements might go well together with
> what you're doing in the planner. Note that I have to "set
> random_page_cost=0.1" to get the planner to use all of the quals from
> the query as index quals. It thinks (correctly) that the query plan is
> very inefficient. That happens to match reality right now, but the
> underlying reality could change significantly. Something to think
> about.
>
> --
> Peter Geoghegan
Thank you for your feedback, your work is also very interesting and
important, and I will be happy to review it. I learned something new
from your letter, thank you very much for that!
I analyzed the buffer consumption when I ran control regression tests
using my patch. diff shows me that there is no difference between the
number of buffer block scans without and using my patch, as far as I
have seen. (regression.diffs)
In addition, I analyzed the scheduling and duration of the execution
time of the source code and with my applied patch. I generated 20
billion data from pgbench and plotted the scheduling and execution time
depending on the number of "or" expressions.
By runtime, I noticed a clear acceleration for queries when using the
index, but I can't say the same when the index is disabled.
At first I turned it off in this way:
1)enable_seqscan='off'
2)enable_indexonlyscan='off'
enable_indexscan='off'
Unfortunately, it is not yet clear which constant needs to be set when
the transformation needs to be done, I will still study in detail. (the
graph for all this is presented in graph1.svg)
\\
--
Regards,
Alena Rybakina
diff -U3 /home/alena/postgrespro7/src/test/regress/expected/create_index.out /home/alena/postgrespro7/src/test/regress/results/create_index.out
--- /home/alena/postgrespro7/src/test/regress/expected/create_index.out 2023-06-27 10:20:52.287769054 +0300
+++ /home/alena/postgrespro7/src/test/regress/results/create_index.out 2023-06-27 10:32:20.701220051 +0300
@@ -1838,26 +1838,14 @@
EXPLAIN (ANALYZE, COSTS OFF, BUFFERS, TIMING OFF, SUMMARY OFF)
SELECT * FROM tenk1
WHERE thousand = 42 AND (tenthous = 1 OR tenthous = 3 OR tenthous = 42);
- QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------
- Bitmap Heap Scan on tenk1 (actual rows=1 loops=1)
- Recheck Cond: (((thousand = 42) AND (tenthous = 1)) OR ((thousand = 42) AND (tenthous = 3)) OR ((thousand = 42) AND (tenthous = 42)))
- Heap Blocks: exact=1
+ QUERY PLAN
+------------------------------------------------------------------------------
+ Index Scan using tenk1_thous_tenthous on tenk1 (actual rows=1 loops=1)
+ Index Cond: ((thousand = 42) AND (tenthous = ANY ('{1,3,42}'::integer[])))
Buffers: shared hit=5 read=2
- -> BitmapOr (actual rows=0 loops=1)
- Buffers: shared hit=4 read=2
- -> Bitmap Index Scan on tenk1_thous_tenthous (actual rows=0 loops=1)
- Index Cond: ((thousand = 42) AND (tenthous = 1))
- Buffers: shared read=2
- -> Bitmap Index Scan on tenk1_thous_tenthous (actual rows=0 loops=1)
- Index Cond: ((thousand = 42) AND (tenthous = 3))
- Buffers: shared hit=2
- -> Bitmap Index Scan on tenk1_thous_tenthous (actual rows=1 loops=1)
- Index Cond: ((thousand = 42) AND (tenthous = 42))
- Buffers: shared hit=2
Planning:
Buffers: shared hit=51
-(17 rows)
+(5 rows)
SELECT * FROM tenk1
WHERE thousand = 42 AND (tenthous = 1 OR tenthous = 3 OR tenthous = 42);
@@ -1869,12 +1857,12 @@
EXPLAIN (ANALYZE, COSTS OFF, BUFFERS, TIMING OFF, SUMMARY OFF)
SELECT count(*) FROM tenk1
WHERE hundred = 42 AND (thousand = 42 OR thousand = 99);
- QUERY PLAN
---------------------------------------------------------------------------------------------
+ QUERY PLAN
+--------------------------------------------------------------------------------------
Aggregate (actual rows=1 loops=1)
Buffers: shared hit=5 read=3
-> Bitmap Heap Scan on tenk1 (actual rows=10 loops=1)
- Recheck Cond: ((hundred = 42) AND ((thousand = 42) OR (thousand = 99)))
+ Recheck Cond: ((hundred = 42) AND (thousand = ANY ('{42,99}'::integer[])))
Heap Blocks: exact=10
Buffers: shared hit=5 read=3
-> BitmapAnd (actual rows=0 loops=1)
@@ -1882,15 +1870,10 @@
-> Bitmap Index Scan on tenk1_hundred (actual rows=100 loops=1)
Index Cond: (hundred = 42)
Buffers: shared read=2
- -> BitmapOr (actual rows=0 loops=1)
+ -> Bitmap Index Scan on tenk1_thous_tenthous (actual rows=20 loops=1)
+ Index Cond: (thousand = ANY ('{42,99}'::integer[]))
Buffers: shared hit=3 read=1
- -> Bitmap Index Scan on tenk1_thous_tenthous (actual rows=10 loops=1)
- Index Cond: (thousand = 42)
- Buffers: shared hit=2
- -> Bitmap Index Scan on tenk1_thous_tenthous (actual rows=10 loops=1)
- Index Cond: (thousand = 99)
- Buffers: shared hit=1 read=1
-(19 rows)
+(14 rows)
SELECT count(*) FROM tenk1
WHERE hundred = 42 AND (thousand = 42 OR thousand = 99);
diff -U3 /home/alena/postgrespro7/src/test/regress/expected/join.out /home/alena/postgrespro7/src/test/regress/results/join.out
--- /home/alena/postgrespro7/src/test/regress/expected/join.out 2023-06-27 10:21:22.096306419 +0300
+++ /home/alena/postgrespro7/src/test/regress/results/join.out 2023-06-27 10:32:26.049288441 +0300
@@ -4207,10 +4207,10 @@
select * from tenk1 a join tenk1 b on
(a.unique1 = 1 and b.unique1 = 2) or
((a.unique2 = 3 or a.unique2 = 7) and b.hundred = 4);
- QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------
+ QUERY PLAN
+------------------------------------------------------------------------------------------------------------------------
Nested Loop (actual rows=201 loops=1)
- Join Filter: (((a.unique1 = 1) AND (b.unique1 = 2)) OR (((a.unique2 = 3) OR (a.unique2 = 7)) AND (b.hundred = 4)))
+ Join Filter: (((a.unique1 = 1) AND (b.unique1 = 2)) OR ((a.unique2 = ANY ('{3,7}'::integer[])) AND (b.hundred = 4)))
Rows Removed by Join Filter: 102
Buffers: shared hit=98
-> Bitmap Heap Scan on tenk1 b (actual rows=101 loops=1)
@@ -4228,7 +4228,7 @@
-> Materialize (actual rows=3 loops=101)
Buffers: shared hit=8
-> Bitmap Heap Scan on tenk1 a (actual rows=3 loops=1)
- Recheck Cond: ((unique1 = 1) OR (unique2 = 3) OR (unique2 = 7))
+ Recheck Cond: ((unique1 = 1) OR (unique2 = ANY ('{3,7}'::integer[])))
Heap Blocks: exact=2
Buffers: shared hit=8
-> BitmapOr (actual rows=0 loops=1)
@@ -4236,13 +4236,10 @@
-> Bitmap Index Scan on tenk1_unique1 (actual rows=1 loops=1)
Index Cond: (unique1 = 1)
Buffers: shared hit=2
- -> Bitmap Index Scan on tenk1_unique2 (actual rows=1 loops=1)
- Index Cond: (unique2 = 3)
- Buffers: shared hit=2
- -> Bitmap Index Scan on tenk1_unique2 (actual rows=1 loops=1)
- Index Cond: (unique2 = 7)
- Buffers: shared hit=2
-(33 rows)
+ -> Bitmap Index Scan on tenk1_unique2 (actual rows=2 loops=1)
+ Index Cond: (unique2 = ANY ('{3,7}'::integer[]))
+ Buffers: shared hit=4
+(30 rows)
--
-- test placement of movable quals in a parameterized join tree
diff -U3 /home/alena/postgrespro7/src/test/regress/expected/stats_ext.out /home/alena/postgrespro7/src/test/regress/results/stats_ext.out
--- /home/alena/postgrespro7/src/test/regress/expected/stats_ext.out 2023-06-27 00:35:33.761010034 +0300
+++ /home/alena/postgrespro7/src/test/regress/results/stats_ext.out 2023-06-27 10:32:37.537436241 +0300
@@ -1322,19 +1322,19 @@
SELECT * FROM check_estimated_rows('SELECT * FROM functional_dependencies WHERE (a = 1 OR a = 51) AND b = ''1''');
estimated | actual
-----------+--------
- 99 | 100
+ 100 | 100
(1 row)
SELECT * FROM check_estimated_rows('SELECT * FROM functional_dependencies WHERE (a = 1 OR a = 51) AND (b = ''1'' OR b = ''2'')');
estimated | actual
-----------+--------
- 99 | 100
+ 100 | 100
(1 row)
SELECT * FROM check_estimated_rows('SELECT * FROM functional_dependencies WHERE (a = 1 OR a = 2 OR a = 51 OR a = 52) AND (b = ''1'' OR b = ''2'')');
estimated | actual
-----------+--------
- 197 | 200
+ 200 | 200
(1 row)
-- OR clauses referencing different attributes are incompatible
@@ -1664,19 +1664,19 @@
SELECT * FROM check_estimated_rows('SELECT * FROM functional_dependencies WHERE ((a * 2) = 2 OR (a * 2) = 102) AND upper(b) = ''1''');
estimated | actual
-----------+--------
- 99 | 100
+ 100 | 100
(1 row)
SELECT * FROM check_estimated_rows('SELECT * FROM functional_dependencies WHERE ((a * 2) = 2 OR (a * 2) = 102) AND (upper(b) = ''1'' OR upper(b) = ''2'')');
estimated | actual
-----------+--------
- 99 | 100
+ 100 | 100
(1 row)
SELECT * FROM check_estimated_rows('SELECT * FROM functional_dependencies WHERE ((a * 2) = 2 OR (a * 2) = 4 OR (a * 2) = 102 OR (a * 2) = 104) AND (upper(b) = ''1'' OR upper(b) = ''2'')');
estimated | actual
-----------+--------
- 197 | 200
+ 200 | 200
(1 row)
-- OR clauses referencing different attributes
diff -U3 /home/alena/postgrespro7/src/test/regress/expected/partition_prune.out /home/alena/postgrespro7/src/test/regress/results/partition_prune.out
--- /home/alena/postgrespro7/src/test/regress/expected/partition_prune.out 2023-06-27 10:31:39.596703531 +0300
+++ /home/alena/postgrespro7/src/test/regress/results/partition_prune.out 2023-06-27 10:32:48.285575599 +0300
@@ -82,23 +82,25 @@
(2 rows)
EXPLAIN (ANALYZE, COSTS OFF, BUFFERS, TIMING OFF, SUMMARY OFF) select * from lp where a = 'a' or a = 'c';
- QUERY PLAN
-----------------------------------------------------------
+ QUERY PLAN
+------------------------------------------------------
Append (actual rows=0 loops=1)
-> Seq Scan on lp_ad lp_1 (actual rows=0 loops=1)
- Filter: ((a = 'a'::bpchar) OR (a = 'c'::bpchar))
+ Filter: (a = ANY ('{a,c}'::bpchar[]))
-> Seq Scan on lp_bc lp_2 (actual rows=0 loops=1)
- Filter: ((a = 'a'::bpchar) OR (a = 'c'::bpchar))
-(5 rows)
+ Filter: (a = ANY ('{a,c}'::bpchar[]))
+ Planning:
+ Buffers: shared hit=16
+(7 rows)
EXPLAIN (ANALYZE, COSTS OFF, BUFFERS, TIMING OFF, SUMMARY OFF) select * from lp where a is not null and (a = 'a' or a = 'c');
- QUERY PLAN
---------------------------------------------------------------------------------
+ QUERY PLAN
+---------------------------------------------------------------------
Append (actual rows=0 loops=1)
-> Seq Scan on lp_ad lp_1 (actual rows=0 loops=1)
- Filter: ((a IS NOT NULL) AND ((a = 'a'::bpchar) OR (a = 'c'::bpchar)))
+ Filter: ((a IS NOT NULL) AND (a = ANY ('{a,c}'::bpchar[])))
-> Seq Scan on lp_bc lp_2 (actual rows=0 loops=1)
- Filter: ((a IS NOT NULL) AND ((a = 'a'::bpchar) OR (a = 'c'::bpchar)))
+ Filter: ((a IS NOT NULL) AND (a = ANY ('{a,c}'::bpchar[])))
(5 rows)
explain (costs off) select * from lp where a <> 'g';
@@ -518,8 +520,10 @@
QUERY PLAN
----------------------------------------------
Seq Scan on rlp2 rlp (actual rows=0 loops=1)
- Filter: ((a = 1) OR (a = 7))
-(2 rows)
+ Filter: (a = ANY ('{1,7}'::integer[]))
+ Planning:
+ Buffers: shared hit=16
+(4 rows)
explain (costs off) select * from rlp where a = 1 or b = 'ab';
QUERY PLAN
@@ -600,9 +604,9 @@
--------------------------------------------------------------
Append (actual rows=0 loops=1)
-> Seq Scan on rlp4_1 rlp_1 (actual rows=0 loops=1)
- Filter: ((a = 20) OR (a = 40))
+ Filter: (a = ANY ('{20,40}'::integer[]))
-> Seq Scan on rlp5_default rlp_2 (actual rows=0 loops=1)
- Filter: ((a = 20) OR (a = 40))
+ Filter: (a = ANY ('{20,40}'::integer[]))
(5 rows)
explain (costs off) select * from rlp3 where a = 20; /* empty */
@@ -1933,10 +1937,10 @@
EXPLAIN (ANALYZE, COSTS OFF, BUFFERS, TIMING OFF, SUMMARY OFF) select * from hp where a = 1 and b = 'abcde' and
(c = 2 or c = 3);
- QUERY PLAN
-----------------------------------------------------------------------
+ QUERY PLAN
+--------------------------------------------------------------------------------
Seq Scan on hp2 hp (actual rows=0 loops=1)
- Filter: ((a = 1) AND (b = 'abcde'::text) AND ((c = 2) OR (c = 3)))
+ Filter: ((c = ANY ('{2,3}'::integer[])) AND (a = 1) AND (b = 'abcde'::text))
Rows Removed by Filter: 2
Buffers: shared hit=1
(4 rows)
@@ -2269,11 +2273,11 @@
Workers Launched: N
-> Parallel Append (actual rows=N loops=N)
-> Parallel Seq Scan on ab_a1_b2 ab_1 (actual rows=N loops=N)
- Filter: ((b = 2) AND ((a = $0) OR (a = $1)))
+ Filter: ((a = ANY (ARRAY[$0, $1])) AND (b = 2))
-> Parallel Seq Scan on ab_a2_b2 ab_2 (never executed)
- Filter: ((b = 2) AND ((a = $0) OR (a = $1)))
+ Filter: ((a = ANY (ARRAY[$0, $1])) AND (b = 2))
-> Parallel Seq Scan on ab_a3_b2 ab_3 (actual rows=N loops=N)
- Filter: ((b = 2) AND ((a = $0) OR (a = $1)))
+ Filter: ((a = ANY (ARRAY[$0, $1])) AND (b = 2))
(16 rows)
-- Test pruning during parallel nested loop query
Attachments:
[text/plain] regression.diffs (13.1K, ../../[email protected]/3-regression.diffs)
download | inline:
diff -U3 /home/alena/postgrespro7/src/test/regress/expected/create_index.out /home/alena/postgrespro7/src/test/regress/results/create_index.out
--- /home/alena/postgrespro7/src/test/regress/expected/create_index.out 2023-06-27 10:20:52.287769054 +0300
+++ /home/alena/postgrespro7/src/test/regress/results/create_index.out 2023-06-27 10:32:20.701220051 +0300
@@ -1838,26 +1838,14 @@
EXPLAIN (ANALYZE, COSTS OFF, BUFFERS, TIMING OFF, SUMMARY OFF)
SELECT * FROM tenk1
WHERE thousand = 42 AND (tenthous = 1 OR tenthous = 3 OR tenthous = 42);
- QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------
- Bitmap Heap Scan on tenk1 (actual rows=1 loops=1)
- Recheck Cond: (((thousand = 42) AND (tenthous = 1)) OR ((thousand = 42) AND (tenthous = 3)) OR ((thousand = 42) AND (tenthous = 42)))
- Heap Blocks: exact=1
+ QUERY PLAN
+------------------------------------------------------------------------------
+ Index Scan using tenk1_thous_tenthous on tenk1 (actual rows=1 loops=1)
+ Index Cond: ((thousand = 42) AND (tenthous = ANY ('{1,3,42}'::integer[])))
Buffers: shared hit=5 read=2
- -> BitmapOr (actual rows=0 loops=1)
- Buffers: shared hit=4 read=2
- -> Bitmap Index Scan on tenk1_thous_tenthous (actual rows=0 loops=1)
- Index Cond: ((thousand = 42) AND (tenthous = 1))
- Buffers: shared read=2
- -> Bitmap Index Scan on tenk1_thous_tenthous (actual rows=0 loops=1)
- Index Cond: ((thousand = 42) AND (tenthous = 3))
- Buffers: shared hit=2
- -> Bitmap Index Scan on tenk1_thous_tenthous (actual rows=1 loops=1)
- Index Cond: ((thousand = 42) AND (tenthous = 42))
- Buffers: shared hit=2
Planning:
Buffers: shared hit=51
-(17 rows)
+(5 rows)
SELECT * FROM tenk1
WHERE thousand = 42 AND (tenthous = 1 OR tenthous = 3 OR tenthous = 42);
@@ -1869,12 +1857,12 @@
EXPLAIN (ANALYZE, COSTS OFF, BUFFERS, TIMING OFF, SUMMARY OFF)
SELECT count(*) FROM tenk1
WHERE hundred = 42 AND (thousand = 42 OR thousand = 99);
- QUERY PLAN
---------------------------------------------------------------------------------------------
+ QUERY PLAN
+--------------------------------------------------------------------------------------
Aggregate (actual rows=1 loops=1)
Buffers: shared hit=5 read=3
-> Bitmap Heap Scan on tenk1 (actual rows=10 loops=1)
- Recheck Cond: ((hundred = 42) AND ((thousand = 42) OR (thousand = 99)))
+ Recheck Cond: ((hundred = 42) AND (thousand = ANY ('{42,99}'::integer[])))
Heap Blocks: exact=10
Buffers: shared hit=5 read=3
-> BitmapAnd (actual rows=0 loops=1)
@@ -1882,15 +1870,10 @@
-> Bitmap Index Scan on tenk1_hundred (actual rows=100 loops=1)
Index Cond: (hundred = 42)
Buffers: shared read=2
- -> BitmapOr (actual rows=0 loops=1)
+ -> Bitmap Index Scan on tenk1_thous_tenthous (actual rows=20 loops=1)
+ Index Cond: (thousand = ANY ('{42,99}'::integer[]))
Buffers: shared hit=3 read=1
- -> Bitmap Index Scan on tenk1_thous_tenthous (actual rows=10 loops=1)
- Index Cond: (thousand = 42)
- Buffers: shared hit=2
- -> Bitmap Index Scan on tenk1_thous_tenthous (actual rows=10 loops=1)
- Index Cond: (thousand = 99)
- Buffers: shared hit=1 read=1
-(19 rows)
+(14 rows)
SELECT count(*) FROM tenk1
WHERE hundred = 42 AND (thousand = 42 OR thousand = 99);
diff -U3 /home/alena/postgrespro7/src/test/regress/expected/join.out /home/alena/postgrespro7/src/test/regress/results/join.out
--- /home/alena/postgrespro7/src/test/regress/expected/join.out 2023-06-27 10:21:22.096306419 +0300
+++ /home/alena/postgrespro7/src/test/regress/results/join.out 2023-06-27 10:32:26.049288441 +0300
@@ -4207,10 +4207,10 @@
select * from tenk1 a join tenk1 b on
(a.unique1 = 1 and b.unique1 = 2) or
((a.unique2 = 3 or a.unique2 = 7) and b.hundred = 4);
- QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------
+ QUERY PLAN
+------------------------------------------------------------------------------------------------------------------------
Nested Loop (actual rows=201 loops=1)
- Join Filter: (((a.unique1 = 1) AND (b.unique1 = 2)) OR (((a.unique2 = 3) OR (a.unique2 = 7)) AND (b.hundred = 4)))
+ Join Filter: (((a.unique1 = 1) AND (b.unique1 = 2)) OR ((a.unique2 = ANY ('{3,7}'::integer[])) AND (b.hundred = 4)))
Rows Removed by Join Filter: 102
Buffers: shared hit=98
-> Bitmap Heap Scan on tenk1 b (actual rows=101 loops=1)
@@ -4228,7 +4228,7 @@
-> Materialize (actual rows=3 loops=101)
Buffers: shared hit=8
-> Bitmap Heap Scan on tenk1 a (actual rows=3 loops=1)
- Recheck Cond: ((unique1 = 1) OR (unique2 = 3) OR (unique2 = 7))
+ Recheck Cond: ((unique1 = 1) OR (unique2 = ANY ('{3,7}'::integer[])))
Heap Blocks: exact=2
Buffers: shared hit=8
-> BitmapOr (actual rows=0 loops=1)
@@ -4236,13 +4236,10 @@
-> Bitmap Index Scan on tenk1_unique1 (actual rows=1 loops=1)
Index Cond: (unique1 = 1)
Buffers: shared hit=2
- -> Bitmap Index Scan on tenk1_unique2 (actual rows=1 loops=1)
- Index Cond: (unique2 = 3)
- Buffers: shared hit=2
- -> Bitmap Index Scan on tenk1_unique2 (actual rows=1 loops=1)
- Index Cond: (unique2 = 7)
- Buffers: shared hit=2
-(33 rows)
+ -> Bitmap Index Scan on tenk1_unique2 (actual rows=2 loops=1)
+ Index Cond: (unique2 = ANY ('{3,7}'::integer[]))
+ Buffers: shared hit=4
+(30 rows)
--
-- test placement of movable quals in a parameterized join tree
diff -U3 /home/alena/postgrespro7/src/test/regress/expected/stats_ext.out /home/alena/postgrespro7/src/test/regress/results/stats_ext.out
--- /home/alena/postgrespro7/src/test/regress/expected/stats_ext.out 2023-06-27 00:35:33.761010034 +0300
+++ /home/alena/postgrespro7/src/test/regress/results/stats_ext.out 2023-06-27 10:32:37.537436241 +0300
@@ -1322,19 +1322,19 @@
SELECT * FROM check_estimated_rows('SELECT * FROM functional_dependencies WHERE (a = 1 OR a = 51) AND b = ''1''');
estimated | actual
-----------+--------
- 99 | 100
+ 100 | 100
(1 row)
SELECT * FROM check_estimated_rows('SELECT * FROM functional_dependencies WHERE (a = 1 OR a = 51) AND (b = ''1'' OR b = ''2'')');
estimated | actual
-----------+--------
- 99 | 100
+ 100 | 100
(1 row)
SELECT * FROM check_estimated_rows('SELECT * FROM functional_dependencies WHERE (a = 1 OR a = 2 OR a = 51 OR a = 52) AND (b = ''1'' OR b = ''2'')');
estimated | actual
-----------+--------
- 197 | 200
+ 200 | 200
(1 row)
-- OR clauses referencing different attributes are incompatible
@@ -1664,19 +1664,19 @@
SELECT * FROM check_estimated_rows('SELECT * FROM functional_dependencies WHERE ((a * 2) = 2 OR (a * 2) = 102) AND upper(b) = ''1''');
estimated | actual
-----------+--------
- 99 | 100
+ 100 | 100
(1 row)
SELECT * FROM check_estimated_rows('SELECT * FROM functional_dependencies WHERE ((a * 2) = 2 OR (a * 2) = 102) AND (upper(b) = ''1'' OR upper(b) = ''2'')');
estimated | actual
-----------+--------
- 99 | 100
+ 100 | 100
(1 row)
SELECT * FROM check_estimated_rows('SELECT * FROM functional_dependencies WHERE ((a * 2) = 2 OR (a * 2) = 4 OR (a * 2) = 102 OR (a * 2) = 104) AND (upper(b) = ''1'' OR upper(b) = ''2'')');
estimated | actual
-----------+--------
- 197 | 200
+ 200 | 200
(1 row)
-- OR clauses referencing different attributes
diff -U3 /home/alena/postgrespro7/src/test/regress/expected/partition_prune.out /home/alena/postgrespro7/src/test/regress/results/partition_prune.out
--- /home/alena/postgrespro7/src/test/regress/expected/partition_prune.out 2023-06-27 10:31:39.596703531 +0300
+++ /home/alena/postgrespro7/src/test/regress/results/partition_prune.out 2023-06-27 10:32:48.285575599 +0300
@@ -82,23 +82,25 @@
(2 rows)
EXPLAIN (ANALYZE, COSTS OFF, BUFFERS, TIMING OFF, SUMMARY OFF) select * from lp where a = 'a' or a = 'c';
- QUERY PLAN
-----------------------------------------------------------
+ QUERY PLAN
+------------------------------------------------------
Append (actual rows=0 loops=1)
-> Seq Scan on lp_ad lp_1 (actual rows=0 loops=1)
- Filter: ((a = 'a'::bpchar) OR (a = 'c'::bpchar))
+ Filter: (a = ANY ('{a,c}'::bpchar[]))
-> Seq Scan on lp_bc lp_2 (actual rows=0 loops=1)
- Filter: ((a = 'a'::bpchar) OR (a = 'c'::bpchar))
-(5 rows)
+ Filter: (a = ANY ('{a,c}'::bpchar[]))
+ Planning:
+ Buffers: shared hit=16
+(7 rows)
EXPLAIN (ANALYZE, COSTS OFF, BUFFERS, TIMING OFF, SUMMARY OFF) select * from lp where a is not null and (a = 'a' or a = 'c');
- QUERY PLAN
---------------------------------------------------------------------------------
+ QUERY PLAN
+---------------------------------------------------------------------
Append (actual rows=0 loops=1)
-> Seq Scan on lp_ad lp_1 (actual rows=0 loops=1)
- Filter: ((a IS NOT NULL) AND ((a = 'a'::bpchar) OR (a = 'c'::bpchar)))
+ Filter: ((a IS NOT NULL) AND (a = ANY ('{a,c}'::bpchar[])))
-> Seq Scan on lp_bc lp_2 (actual rows=0 loops=1)
- Filter: ((a IS NOT NULL) AND ((a = 'a'::bpchar) OR (a = 'c'::bpchar)))
+ Filter: ((a IS NOT NULL) AND (a = ANY ('{a,c}'::bpchar[])))
(5 rows)
explain (costs off) select * from lp where a <> 'g';
@@ -518,8 +520,10 @@
QUERY PLAN
----------------------------------------------
Seq Scan on rlp2 rlp (actual rows=0 loops=1)
- Filter: ((a = 1) OR (a = 7))
-(2 rows)
+ Filter: (a = ANY ('{1,7}'::integer[]))
+ Planning:
+ Buffers: shared hit=16
+(4 rows)
explain (costs off) select * from rlp where a = 1 or b = 'ab';
QUERY PLAN
@@ -600,9 +604,9 @@
--------------------------------------------------------------
Append (actual rows=0 loops=1)
-> Seq Scan on rlp4_1 rlp_1 (actual rows=0 loops=1)
- Filter: ((a = 20) OR (a = 40))
+ Filter: (a = ANY ('{20,40}'::integer[]))
-> Seq Scan on rlp5_default rlp_2 (actual rows=0 loops=1)
- Filter: ((a = 20) OR (a = 40))
+ Filter: (a = ANY ('{20,40}'::integer[]))
(5 rows)
explain (costs off) select * from rlp3 where a = 20; /* empty */
@@ -1933,10 +1937,10 @@
EXPLAIN (ANALYZE, COSTS OFF, BUFFERS, TIMING OFF, SUMMARY OFF) select * from hp where a = 1 and b = 'abcde' and
(c = 2 or c = 3);
- QUERY PLAN
-----------------------------------------------------------------------
+ QUERY PLAN
+--------------------------------------------------------------------------------
Seq Scan on hp2 hp (actual rows=0 loops=1)
- Filter: ((a = 1) AND (b = 'abcde'::text) AND ((c = 2) OR (c = 3)))
+ Filter: ((c = ANY ('{2,3}'::integer[])) AND (a = 1) AND (b = 'abcde'::text))
Rows Removed by Filter: 2
Buffers: shared hit=1
(4 rows)
@@ -2269,11 +2273,11 @@
Workers Launched: N
-> Parallel Append (actual rows=N loops=N)
-> Parallel Seq Scan on ab_a1_b2 ab_1 (actual rows=N loops=N)
- Filter: ((b = 2) AND ((a = $0) OR (a = $1)))
+ Filter: ((a = ANY (ARRAY[$0, $1])) AND (b = 2))
-> Parallel Seq Scan on ab_a2_b2 ab_2 (never executed)
- Filter: ((b = 2) AND ((a = $0) OR (a = $1)))
+ Filter: ((a = ANY (ARRAY[$0, $1])) AND (b = 2))
-> Parallel Seq Scan on ab_a3_b2 ab_3 (actual rows=N loops=N)
- Filter: ((b = 2) AND ((a = $0) OR (a = $1)))
+ Filter: ((a = ANY (ARRAY[$0, $1])) AND (b = 2))
(16 rows)
-- Test pruning during parallel nested loop query
[image/png] graph1.png (109.5K, ../../[email protected]/4-graph1.png)
download | view image
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: POC, WIP: OR-clause support for indexes
2023-06-27 13:19 Re: POC, WIP: OR-clause support for indexes Alena Rybakina <[email protected]>
@ 2023-06-27 15:49 ` Alena Rybakina <[email protected]>
0 siblings, 0 replies; 3+ messages in thread
From: Alena Rybakina @ 2023-06-27 15:49 UTC (permalink / raw)
To: Peter Geoghegan <[email protected]>; +Cc: Marcos Pegoraro <[email protected]>; Alena Rybakina <[email protected]>; Andrey Lepikhov <[email protected]>; pgsql-hackers; [email protected]
On 27.06.2023 16:19, Alena Rybakina wrote:
> Thank you for your feedback, your work is also very interesting and
> important, and I will be happy to review it. I learned something new
> from your letter, thank you very much for that!
>
> I analyzed the buffer consumption when I ran control regression tests
> using my patch. diff shows me that there is no difference between the
> number of buffer block scans without and using my patch, as far as I
> have seen. (regression.diffs)
>
>
> In addition, I analyzed the scheduling and duration of the execution
> time of the source code and with my applied patch. I generated 20
> billion data from pgbench and plotted the scheduling and execution
> time depending on the number of "or" expressions.
> By runtime, I noticed a clear acceleration for queries when using the
> index, but I can't say the same when the index is disabled.
> At first I turned it off in this way:
> 1)enable_seqscan='off'
> 2)enable_indexonlyscan='off'
> enable_indexscan='off'
>
> Unfortunately, it is not yet clear which constant needs to be set when
> the transformation needs to be done, I will still study in detail.
> (the graph for all this is presented in graph1.svg)
> \\
> --
> Regards,
> Alena Rybakina
Sorry, just now I noticed that there were incorrect names in the
headings of the pictures, I corrected it. I also attach its html copy,
because it may be more convenient for viewing it.
--
Regards,
Alena Rybakina
Attachments:
[text/html] graphs.html (3.6M, ../../[email protected]/3-graphs.html)
download
[image/png] graphs.png (90.1K, ../../[email protected]/4-graphs.png)
download | view image
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2023-06-27 15:49 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2020-03-19 06:11 [PATCH v45 5/7] Doc part of shared-memory based stats collector. Kyotaro Horiguchi <[email protected]>
2023-06-27 13:19 Re: POC, WIP: OR-clause support for indexes Alena Rybakina <[email protected]>
2023-06-27 15:49 ` Re: POC, WIP: OR-clause support for indexes Alena Rybakina <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox