public inbox for [email protected]help / color / mirror / Atom feed
[PATCH v5 2/3] Do not use RelationNeedsWAL to identify relation persistence 4+ messages / 4 participants [nested] [flat]
* [PATCH v5 2/3] Do not use RelationNeedsWAL to identify relation persistence @ 2021-01-18 05:47 Kyotaro Horiguchi <[email protected]> 0 siblings, 0 replies; 4+ messages in thread From: Kyotaro Horiguchi @ 2021-01-18 05:47 UTC (permalink / raw) RelationNeedsWAL() may return false for a permanent relation when wal_level=minimal and the relation is created or truncated in the current transaction. Directly examine relpersistence instead of using the function to know relation persistence. --- src/backend/catalog/pg_publication.c | 2 +- src/backend/optimizer/util/plancat.c | 3 ++- src/include/storage/bufmgr.h | 8 +++++++- src/include/utils/rel.h | 2 +- src/include/utils/snapmgr.h | 2 +- 5 files changed, 12 insertions(+), 5 deletions(-) diff --git a/src/backend/catalog/pg_publication.c b/src/backend/catalog/pg_publication.c index 5f8e1c64e1..84d2efcfd2 100644 --- a/src/backend/catalog/pg_publication.c +++ b/src/backend/catalog/pg_publication.c @@ -67,7 +67,7 @@ check_publication_add_relation(Relation targetrel) errdetail("System tables cannot be added to publications."))); /* UNLOGGED and TEMP relations cannot be part of publication. */ - if (!RelationNeedsWAL(targetrel)) + if (targetrel->rd_rel->relpersistence != RELPERSISTENCE_PERMANENT) ereport(ERROR, (errcode(ERRCODE_INVALID_PARAMETER_VALUE), errmsg("table \"%s\" cannot be replicated", diff --git a/src/backend/optimizer/util/plancat.c b/src/backend/optimizer/util/plancat.c index da322b453e..177e6e336a 100644 --- a/src/backend/optimizer/util/plancat.c +++ b/src/backend/optimizer/util/plancat.c @@ -126,7 +126,8 @@ get_relation_info(PlannerInfo *root, Oid relationObjectId, bool inhparent, relation = table_open(relationObjectId, NoLock); /* Temporary and unlogged relations are inaccessible during recovery. */ - if (!RelationNeedsWAL(relation) && RecoveryInProgress()) + if (relation->rd_rel->relpersistence != RELPERSISTENCE_PERMANENT && + RecoveryInProgress()) ereport(ERROR, (errcode(ERRCODE_FEATURE_NOT_SUPPORTED), errmsg("cannot access temporary or unlogged relations during recovery"))); diff --git a/src/include/storage/bufmgr.h b/src/include/storage/bufmgr.h index fb00fda6a7..e641174798 100644 --- a/src/include/storage/bufmgr.h +++ b/src/include/storage/bufmgr.h @@ -14,6 +14,7 @@ #ifndef BUFMGR_H #define BUFMGR_H +#include "access/xlog.h" #include "storage/block.h" #include "storage/buf.h" #include "storage/bufpage.h" @@ -278,12 +279,17 @@ TestForOldSnapshot(Snapshot snapshot, Relation relation, Page page) { Assert(relation != NULL); + /* + * While wal_level=minimal, early pruning can happen without updating page + * LSN. Don't take the fast-return path while wal_level=minimal so that we + * don't miss early pruning. + */ if (old_snapshot_threshold >= 0 && (snapshot) != NULL && ((snapshot)->snapshot_type == SNAPSHOT_MVCC || (snapshot)->snapshot_type == SNAPSHOT_TOAST) && !XLogRecPtrIsInvalid((snapshot)->lsn) - && PageGetLSN(page) > (snapshot)->lsn) + && (!XLogIsNeeded() || PageGetLSN(page) > (snapshot)->lsn)) TestForOldSnapshot_impl(snapshot, relation); } diff --git a/src/include/utils/rel.h b/src/include/utils/rel.h index 10b63982c0..f58d65cf28 100644 --- a/src/include/utils/rel.h +++ b/src/include/utils/rel.h @@ -554,7 +554,7 @@ typedef struct ViewOptions /* * RelationNeedsWAL - * True if relation needs WAL. + * True if relation needs WAL at the time. * * Returns false if wal_level = minimal and this relation is created or * truncated in the current transaction. See "Skipping WAL for New diff --git a/src/include/utils/snapmgr.h b/src/include/utils/snapmgr.h index 579be352c5..c21ee3c289 100644 --- a/src/include/utils/snapmgr.h +++ b/src/include/utils/snapmgr.h @@ -37,7 +37,7 @@ */ #define RelationAllowsEarlyPruning(rel) \ ( \ - RelationNeedsWAL(rel) \ + (rel)->rd_rel->relpersistence == RELPERSISTENCE_PERMANENT \ && !IsCatalogRelation(rel) \ && !RelationIsAccessibleInLogicalDecoding(rel) \ ) -- 2.27.0 ----Next_Part(Thu_Jan_21_00_28_44_2021_003)-- Content-Type: Text/X-Patch; charset=us-ascii Content-Transfer-Encoding: 7bit Content-Disposition: inline; filename="v5-0003-Poc-Keep-page-LSN-updated-while-WAL-skipping.patch" ^ permalink raw reply [nested|flat] 4+ messages in thread
* MergeJoin beats HashJoin in the case of multiple hash clauses @ 2023-06-15 08:30 Andrey Lepikhov <[email protected]> 2023-06-28 13:53 ` Re: MergeJoin beats HashJoin in the case of multiple hash clauses Alena Rybakina <[email protected]> 2023-09-11 04:51 ` Re: MergeJoin beats HashJoin in the case of multiple hash clauses Andy Fan <[email protected]> 0 siblings, 2 replies; 4+ messages in thread From: Andrey Lepikhov @ 2023-06-15 08:30 UTC (permalink / raw) To: pgsql-hackers; +Cc: Tom Lane <[email protected]> Hi, all. Some of my clients use JOIN's with three - four clauses. Quite frequently, I see complaints on unreasonable switch of JOIN algorithm to Merge Join instead of Hash Join. Quick research have shown one weak place - estimation of an average bucket size in final_cost_hashjoin (see q2.sql in attachment) with very conservative strategy. Unlike estimation of groups, here we use smallest ndistinct value across all buckets instead of multiplying them (or trying to make multivariate analysis). It works fine for the case of one clause. But if we have many clauses, and if each has high value of ndistinct, we will overestimate average size of a bucket and, as a result, prefer to use Merge Join. As the example in attachment shows, it leads to worse plan than possible, sometimes drastically worse. I assume, this is done with fear of functional dependencies between hash clause components. But as for me, here we should go the same way, as estimation of groups. The attached patch shows a sketch of the solution. -- regards, Andrey Lepikhov Postgres Professional diff --git a/src/backend/optimizer/path/costsize.c b/src/backend/optimizer/path/costsize.c index ef475d95a1..26f26d6a40 100644 --- a/src/backend/optimizer/path/costsize.c +++ b/src/backend/optimizer/path/costsize.c @@ -4033,11 +4033,12 @@ final_cost_hashjoin(PlannerInfo *root, HashPath *path, thismcvfreq = restrictinfo->left_mcvfreq; } - if (innerbucketsize > thisbucketsize) - innerbucketsize = thisbucketsize; - if (innermcvfreq > thismcvfreq) - innermcvfreq = thismcvfreq; + innerbucketsize *= thisbucketsize; + innermcvfreq *= thismcvfreq; } + + if (innerbucketsize > virtualbuckets) + innerbucketsize = 1.0 / virtualbuckets; } /* Attachments: [application/sql] q2.sql (1.9K, ../../[email protected]/2-q2.sql) download [text/plain] fix_bucketsize.diff (674B, ../../[email protected]/3-fix_bucketsize.diff) download | inline diff: diff --git a/src/backend/optimizer/path/costsize.c b/src/backend/optimizer/path/costsize.c index ef475d95a1..26f26d6a40 100644 --- a/src/backend/optimizer/path/costsize.c +++ b/src/backend/optimizer/path/costsize.c @@ -4033,11 +4033,12 @@ final_cost_hashjoin(PlannerInfo *root, HashPath *path, thismcvfreq = restrictinfo->left_mcvfreq; } - if (innerbucketsize > thisbucketsize) - innerbucketsize = thisbucketsize; - if (innermcvfreq > thismcvfreq) - innermcvfreq = thismcvfreq; + innerbucketsize *= thisbucketsize; + innermcvfreq *= thismcvfreq; } + + if (innerbucketsize > virtualbuckets) + innerbucketsize = 1.0 / virtualbuckets; } /* ^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: MergeJoin beats HashJoin in the case of multiple hash clauses 2023-06-15 08:30 MergeJoin beats HashJoin in the case of multiple hash clauses Andrey Lepikhov <[email protected]> @ 2023-06-28 13:53 ` Alena Rybakina <[email protected]> 1 sibling, 0 replies; 4+ messages in thread From: Alena Rybakina @ 2023-06-28 13:53 UTC (permalink / raw) To: Andrey Lepikhov <[email protected]>; pgsql-hackers; +Cc: Tom Lane <[email protected]> Hi! On 15.06.2023 11:30, Andrey Lepikhov wrote: > Hi, all. > > Some of my clients use JOIN's with three - four clauses. Quite > frequently, I see complaints on unreasonable switch of JOIN algorithm > to Merge Join instead of Hash Join. Quick research have shown one weak > place - estimation of an average bucket size in final_cost_hashjoin > (see q2.sql in attachment) with very conservative strategy. > Unlike estimation of groups, here we use smallest ndistinct value > across all buckets instead of multiplying them (or trying to make > multivariate analysis). > It works fine for the case of one clause. But if we have many clauses, > and if each has high value of ndistinct, we will overestimate average > size of a bucket and, as a result, prefer to use Merge Join. As the > example in attachment shows, it leads to worse plan than possible, > sometimes drastically worse. > I assume, this is done with fear of functional dependencies between > hash clause components. But as for me, here we should go the same way, > as estimation of groups. > The attached patch shows a sketch of the solution. > This problem is very important. Honestly, I'm still learning your code and looking for cases on which cases your patch can affect for the worse or for the better. But I have already found something that seemed interesting to me. I have found several other interesting cases where your patch can solve some problem in order to choose a more correct plan, but in focus on memory consumption. To make it easier to evaluate, I added a hook to your patch that makes it easier to switch to your or the original way of estimating the size of baskets (diff_estimate.diff). Here are other cases where your fix improves the query plan. First of all, I changed the way creation of tables are created to look at the behavior of the query plan in terms of planning and execution time: DROP TABLE IF EXISTS a,b CASCADE; CREATE TABLE a AS SELECT ((3*gs) % 300) AS x, ((3*gs+1) % 300) AS y, ((3*gs+2) % 300) AS z FROM generate_series(1,1e5) AS gs; CREATE TABLE b AS SELECT gs % 90 AS x, gs % 49 AS y, gs %100 AS z, 'abc' || gs AS payload FROM generate_series(1,1e5) AS gs; ANALYZE a,b; SET enable_cost_size = 'on'; EXPLAIN ANALYZE SELECT * FROM a,b WHERE a.x=b.x AND a.y=b.y AND a.z=b.z; SET enable_cost_size = 'off'; EXPLAIN ANALYZE SELECT * FROM a,b WHERE a.x=b.x AND a.y=b.y AND a.z=b.z; QUERY PLAN --------------------------------------------------------------------------- Hash Join (actual time=200.872..200.879 rows=0 loops=1) Hash Cond: ((b.x = a.x) AND (b.y = a.y) AND (b.z = a.z)) -> Seq Scan on b (actual time=0.029..15.946 rows=100000 loops=1) -> Hash (actual time=97.645..97.649 rows=100000 loops=1) Buckets: 131072 Batches: 1 Memory Usage: 5612kB -> Seq Scan on a (actual time=0.024..17.153 rows=100000 loops=1) Planning Time: 2.910 ms Execution Time: 201.949 ms (8 rows) SET QUERY PLAN --------------------------------------------------------------------------- Merge Join (actual time=687.415..687.416 rows=0 loops=1) Merge Cond: ((b.y = a.y) AND (b.x = a.x) AND (b.z = a.z)) -> Sort (actual time=462.022..536.716 rows=100000 loops=1) Sort Key: b.y, b.x, b.z Sort Method: external merge Disk: 3328kB -> Seq Scan on b (actual time=0.017..12.326 rows=100000 loops=1) -> Sort (actual time=111.295..113.196 rows=16001 loops=1) Sort Key: a.y, a.x, a.z Sort Method: external sort Disk: 2840kB -> Seq Scan on a (actual time=0.020..10.129 rows=100000 loops=1) Planning Time: 0.752 ms Execution Time: 688.829 ms (12 rows) Secondly, I found another case that is not related to the fact that the planner would prefer to choose merge join rather than hash join, but we have the opportunity to see that the plan has become better due to the consumption of less memory, and also takes less planning time. Here, with the same query, the planning time was reduced by 5 times, and the number of buckets by 128 times, therefore, memory consumption also decreased: DROP TABLE IF EXISTS a,b CASCADE; CREATE TABLE a AS SELECT ((3*gs) % 300) AS x, ((3*gs+1) % 300) AS y, ((3*gs+2) % 300) AS z FROM generate_series(1,600) AS gs; CREATE TABLE b AS SELECT gs % 90 AS x, gs % 49 AS y, gs %100 AS z, 'abc' || gs AS payload FROM generate_series(1,1e5) AS gs; ANALYZE a,b; SET enable_cost_size = 'on'; EXPLAIN ANALYZE SELECT * FROM a,b WHERE a.x=b.x AND a.y=b.y AND a.z=b.z; SET enable_cost_size = 'off'; EXPLAIN ANALYZE SELECT * FROM a,b WHERE a.x=b.x AND a.y=b.y AND a.z=b.z; QUERY PLAN ---------------------------------------------------------------------------------------------------------------- Hash Join (cost=20.50..3157.58 rows=8 width=32) (actual time=95.648..95.651 rows=0 loops=1) Hash Cond: ((b.x = (a.x)::numeric) AND (b.y = (a.y)::numeric) AND (b.z = (a.z)::numeric)) -> Seq Scan on b (cost=0.00..1637.00 rows=100000 width=20) (actual time=0.027..17.980 rows=100000 loops=1) -> Hash (cost=10.00..10.00 rows=600 width=12) (actual time=2.046..2.047 rows=600 loops=1) Buckets: 1024 Batches: 1 Memory Usage: 34kB -> Seq Scan on a (cost=0.00..10.00 rows=600 width=12) (actual time=0.022..0.315 rows=600 loops=1) Planning Time: 0.631 ms Execution Time: 95.730 ms (8 rows) SET QUERY PLAN ---------------------------------------------------------------------------------------------------------------------- Hash Join (cost=3387.00..8621.58 rows=8 width=32) (actual time=102.873..102.877 rows=0 loops=1) Hash Cond: (((a.x)::numeric = b.x) AND ((a.y)::numeric = b.y) AND ((a.z)::numeric = b.z)) -> Seq Scan on a (cost=0.00..10.00 rows=600 width=12) (actual time=0.014..0.131 rows=600 loops=1) -> Hash (cost=1637.00..1637.00 rows=100000 width=20) (actual time=101.920..101.921 rows=100000 loops=1) Buckets: 131072 Batches: 1 Memory Usage: 6474kB -> Seq Scan on b (cost=0.00..1637.00 rows=100000 width=20) (actual time=0.013..16.349 rows=100000 loops=1) Planning Time: 0.153 ms Execution Time: 103.518 ms (8 rows) I also give an improvement relative to the left external or right connection: DROP TABLE IF EXISTS a,b CASCADE; CREATE TABLE a AS SELECT ((3*gs) % 300) AS x, ((3*gs+1) % 300) AS y, ((3*gs+2) % 300) AS z FROM generate_series(1,600) AS gs; CREATE TABLE b AS SELECT gs % 90 AS x, gs % 49 AS y, gs %100 AS z, 'abc' || gs AS payload FROM generate_series(1,1e5) AS gs; ANALYZE a,b; SET enable_cost_size = 'on'; EXPLAIN ANALYZE SELECT * FROM a right join b on a.x=b.x AND a.y=b.y AND a.z=b.z; SET enable_cost_size = 'off'; EXPLAIN ANALYZE SELECT * FROM a right join b on a.x=b.x AND a.y=b.y AND a.z=b.z; QUERY PLAN ---------------------------------------------------------------------------------------------------------------- Hash Left Join (cost=20.50..3157.58 rows=100000 width=32) (actual time=1.846..102.264 rows=100000 loops=1) Hash Cond: ((b.x = (a.x)::numeric) AND (b.y = (a.y)::numeric) AND (b.z = (a.z)::numeric)) -> Seq Scan on b (cost=0.00..1637.00 rows=100000 width=20) (actual time=0.041..15.328 rows=100000 loops=1) -> Hash (cost=10.00..10.00 rows=600 width=12) (actual time=1.780..1.781 rows=600 loops=1) Buckets: 1024 Batches: 1 Memory Usage: 34kB -> Seq Scan on a (cost=0.00..10.00 rows=600 width=12) (actual time=0.031..0.252 rows=600 loops=1) Planning Time: 0.492 ms Execution Time: 107.609 ms (8 rows) SET QUERY PLAN ---------------------------------------------------------------------------------------------------------------------- Hash Right Join (cost=3387.00..8500.08 rows=100000 width=32) (actual time=80.919..101.613 rows=100000 loops=1) Hash Cond: (((a.x)::numeric = b.x) AND ((a.y)::numeric = b.y) AND ((a.z)::numeric = b.z)) -> Seq Scan on a (cost=0.00..10.00 rows=600 width=12) (actual time=0.017..0.084 rows=600 loops=1) -> Hash (cost=1637.00..1637.00 rows=100000 width=20) (actual time=80.122..80.123 rows=100000 loops=1) Buckets: 131072 Batches: 1 Memory Usage: 6474kB -> Seq Scan on b (cost=0.00..1637.00 rows=100000 width=20) (actual time=0.015..11.819 rows=100000 loops=1) Planning Time: 0.194 ms Execution Time: 104.662 ms (8 rows) -- Regards, Alena Rybakina Postgres Professional Attachments: [text/x-patch] diff_estimate.diff (2.3K, ../../[email protected]/3-diff_estimate.diff) download | inline diff: diff --git a/src/backend/optimizer/path/costsize.c b/src/backend/optimizer/path/costsize.c index ef475d95a18..31771dfba46 100644 --- a/src/backend/optimizer/path/costsize.c +++ b/src/backend/optimizer/path/costsize.c @@ -153,6 +153,7 @@ bool enable_parallel_hash = true; bool enable_partition_pruning = true; bool enable_presorted_aggregate = true; bool enable_async_append = true; +bool enable_cost_size = true; typedef struct { @@ -4033,11 +4034,22 @@ final_cost_hashjoin(PlannerInfo *root, HashPath *path, thismcvfreq = restrictinfo->left_mcvfreq; } + if (enable_cost_size) + { + innerbucketsize *= thisbucketsize; + innermcvfreq *= thismcvfreq; + } + else + { if (innerbucketsize > thisbucketsize) innerbucketsize = thisbucketsize; if (innermcvfreq > thismcvfreq) innermcvfreq = thismcvfreq; + } } + + if (enable_cost_size && innerbucketsize > virtualbuckets) + innerbucketsize = 1.0 / virtualbuckets; } /* diff --git a/src/backend/utils/misc/guc_tables.c b/src/backend/utils/misc/guc_tables.c index 71e27f8eb05..ded9ba3b7a9 100644 --- a/src/backend/utils/misc/guc_tables.c +++ b/src/backend/utils/misc/guc_tables.c @@ -1007,6 +1007,19 @@ struct config_bool ConfigureNamesBool[] = true, NULL, NULL, NULL }, + { + {"enable_cost_size", PGC_USERSET, QUERY_TUNING_OTHER, + gettext_noop("set the optimizer coefficient" + "so that custom or generic plan is selected more often. " + "by default, the value is set to 1, which means that " + "the choice of using both depends on the calculated cost"), + NULL, + GUC_EXPLAIN + }, + &enable_cost_size, + true, + NULL, NULL, NULL + }, { {"enable_async_append", PGC_USERSET, QUERY_TUNING_METHOD, gettext_noop("Enables the planner's use of async append plans."), diff --git a/src/include/optimizer/cost.h b/src/include/optimizer/cost.h index 6cf49705d3a..c79ec12e6d5 100644 --- a/src/include/optimizer/cost.h +++ b/src/include/optimizer/cost.h @@ -71,6 +71,7 @@ extern PGDLLIMPORT bool enable_partition_pruning; extern PGDLLIMPORT bool enable_presorted_aggregate; extern PGDLLIMPORT bool enable_async_append; extern PGDLLIMPORT int constraint_exclusion; +extern PGDLLIMPORT bool enable_cost_size; extern double index_pages_fetched(double tuples_fetched, BlockNumber pages, double index_pages, PlannerInfo *root); ^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: MergeJoin beats HashJoin in the case of multiple hash clauses 2023-06-15 08:30 MergeJoin beats HashJoin in the case of multiple hash clauses Andrey Lepikhov <[email protected]> @ 2023-09-11 04:51 ` Andy Fan <[email protected]> 1 sibling, 0 replies; 4+ messages in thread From: Andy Fan @ 2023-09-11 04:51 UTC (permalink / raw) To: Andrey Lepikhov <[email protected]>; +Cc: pgsql-hackers; Tom Lane <[email protected]> Hi, On Thu, Jun 15, 2023 at 4:30 PM Andrey Lepikhov <[email protected]> wrote: > Hi, all. > > Some of my clients use JOIN's with three - four clauses. Quite > frequently, I see complaints on unreasonable switch of JOIN algorithm to > Merge Join instead of Hash Join. Quick research have shown one weak > place - estimation of an average bucket size in final_cost_hashjoin (see > q2.sql in attachment) with very conservative strategy. > Unlike estimation of groups, here we use smallest ndistinct value across > all buckets instead of multiplying them (or trying to make multivariate > analysis). > It works fine for the case of one clause. But if we have many clauses, > and if each has high value of ndistinct, we will overestimate average > size of a bucket and, as a result, prefer to use Merge Join. As the > example in attachment shows, it leads to worse plan than possible, > sometimes drastically worse. > I assume, this is done with fear of functional dependencies between hash > clause components. But as for me, here we should go the same way, as > estimation of groups. > I can reproduce the visitation you want to improve and verify the patch can do it expectedly. I think this is a right thing to do. > The attached patch shows a sketch of the solution. > I understand that this is a sketch of the solution, but the below changes still make me confused. + if (innerbucketsize > virtualbuckets) + innerbucketsize = 1.0 / virtualbuckets; innerbucketsize is a fraction of rows in all the rows, so it is between 0.0 and 1.0. and virtualbuckets is the number of buckets in total (when considered the mutli batchs), how is it possible for 'innerbucketsize > virtualbuckets' ? Am I missing something? -- Best Regards Andy Fan ^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2023-09-11 04:51 UTC | newest] Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2021-01-18 05:47 [PATCH v5 2/3] Do not use RelationNeedsWAL to identify relation persistence Kyotaro Horiguchi <[email protected]> 2023-06-15 08:30 MergeJoin beats HashJoin in the case of multiple hash clauses Andrey Lepikhov <[email protected]> 2023-06-28 13:53 ` Re: MergeJoin beats HashJoin in the case of multiple hash clauses Alena Rybakina <[email protected]> 2023-09-11 04:51 ` Re: MergeJoin beats HashJoin in the case of multiple hash clauses Andy Fan <[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