[HACKERS] estimation for join results cardinality is sometimes more than the product of the downstream nodes'
Hello,
Postgres can produce a plan with a nested loop node having rows estimate
much more than the product of underlying nodes' estimates, relying only
on outer relation size:
alexey=# explain
SELECT oid, relname
FROM (
SELECT m.oid, m.relname
FROM pg_class m
UNION ALL
SELECT m.oid, m.relname
FROM pg_class m
) m
WHERE oid IN (VALUES (162456317), (162456310));
QUERY PLAN
Nested Loop (cost=0.31..33.24 rows=*341* width=68)
-> Unique (cost=0.04..0.04 rows=*2* width=4)
-> Sort (cost=0.04..0.04 rows=2 width=4)
Sort Key: (("*VALUES*".column1)::oid)
-> Values Scan on "*VALUES*" (cost=0.00..0.03 rows=2
width=4)
-> Append (cost=0.27..16.58 rows=*2* width=68)
-> Index Scan using pg_class_oid_index on pg_class m
(cost=0.27..8.29 rows=1 width=68)
Index Cond: (oid = ("*VALUES*".column1)::oid)
-> Index Scan using pg_class_oid_index on pg_class m_1
(cost=0.27..8.29 rows=1 width=68)
Index Cond: (oid = ("*VALUES*".column1)::oid)
(10 rows)
Why?
Is there a reason that join cardinality estimates are not limited by the
product of the joined parts cardinalities like in the
join-card-est.patch attached?
An example of a query working faster as a result of this change is in
join-card-est.sql, result is in join-card-est.result
Best Regards,
Alexey
diff --git a/src/backend/optimizer/path/costsize.c b/src/backend/optimizer/path/costsize.c
index b35acb7..fa4bebc 100644
--- a/src/backend/optimizer/path/costsize.c
+++ b/src/backend/optimizer/path/costsize.c
@@ -2185,15 +2185,6 @@ final_cost_nestloop(PlannerInfo *root, NestPath *path,
else
path->path.rows = path->path.parent->rows;
- /* For partial paths, scale row estimate. */
- if (path->path.parallel_workers > 0)
- {
- double parallel_divisor = get_parallel_divisor(>path);
-
- path->path.rows =
- clamp_row_est(path->path.rows / parallel_divisor);
- }
-
/*
* We could include disable_cost in the preliminary estimate, but that
* would amount to optimizing for the case where the join method is
@@ -2321,6 +2312,19 @@ final_cost_nestloop(PlannerInfo *root, NestPath *path,
ntuples = outer_path_rows * inner_path_rows;
}
+ /* We cannot emit more rows than we process. */
+ if (path->path.rows > ntuples)
+ path->path.rows = clamp_row_est(ntuples);
+
+ /* For partial paths, scale row estimate. */
+ if (path->path.parallel_workers > 0)
+ {
+ double parallel_divisor = get_parallel_divisor(>path);
+
+ path->path.rows =
+ clamp_row_est(path->path.rows / parallel_divisor);
+ }
+
/* CPU costs */
cost_qual_eval(_qual_cost, path->joinrestrictinfo, root);
startup_cost += restrict_qual_cost.startup;
join-card-est.sql
Description: application/sql
===MASTER:
Hash Join (cost=3085.45..23948.76 rows=100 width=12) (actual
time=36.048..36.432 rows=4 loops=1)
Hash Cond: (aa1.b = bb.b)
-> Nested Loop (cost=1.46..34.85 rows=100 width=8) (actual
time=0.048..0.091 rows=4 loops=1)
-> Unique (cost=1.03..1.04 rows=2 width=4) (actual time=0.015..0.018
rows=2 loops=1)
-> Sort (cost=1.03..1.03 rows=2 width=4) (actual
time=0.015..0.016 rows=2 loops=1)
Sort Key: ai.a
Sort Method: quicksort Memory: 25kB
-> Seq Scan on ai (cost=0.00..1.02 rows=2 width=4)
(actual time=0.008..0.009 rows=2 loops=1)
-> Append (cost=0.42..16.89 rows=2 width=8) (actual
time=0.019..0.033 rows=2 loops=2)
-> Index Scan using aa1_pkey on aa1 (cost=0.42..8.44 rows=1
width=8) (actual time=0.018..0.020 rows=1 loops=2)
Index Cond: (a = ai.a)
-> Index Scan using aa2_pkey on aa2 (cost=0.42..8.44 rows=1
width=8) (actual time=0.011..0.011 rows=1 loops=2)
Index Cond: (a = ai.a)
-> Hash (cost=1443.00..1443.00 rows=10 width=8) (actual
time=35.871..35.871 rows=10 loops=1)
Buckets: 131072 Batches: 2 Memory Usage: 2976kB
-> Seq Scan on bb (cost=0.00..1443.00 rows=10 width=8) (actual
[HACKERS] patch: optimize information_schema.constraint_column_usage
Hello hackers,
The view information_schema.constraint_column_usage becomes slow when
the number of columns and constraints raise to substantial values.
This is because of a join condition that allows only join filter to
enforce. The patch is to optimize it.
See many_constraints.sql file attached for a performance test: create
3000 tables with 10 columns and a PK each and select * from the view.
The last statement works for 22 seconds on master branch, 34
milliseconds optimized on my laptop.
Best Regards,
Alexey Bashtanov
many-constraints.sql
Description: application/sql
diff --git a/src/backend/catalog/information_schema.sql b/src/backend/catalog/information_schema.sql
index 00550eb..ffb1564 100644
--- a/src/backend/catalog/information_schema.sql
+++ b/src/backend/catalog/information_schema.sql
@@ -801,8 +801,8 @@ CREATE VIEW constraint_column_usage AS
WHERE nr.oid = r.relnamespace
AND r.oid = a.attrelid
AND nc.oid = c.connamespace
-AND (CASE WHEN c.contype = 'f' THEN r.oid = c.confrelid AND a.attnum = ANY (c.confkey)
- ELSE r.oid = c.conrelid AND a.attnum = ANY (c.conkey) END)
+AND r.oid = CASE c.contype WHEN 'f' THEN c.confrelid ELSE c.conrelid END
+AND a.attnum = ANY (CASE c.contype WHEN 'f' THEN c.confkey ELSE c.conkey END)
AND NOT a.attisdropped
AND c.contype IN ('p', 'u', 'f')
AND r.relkind = 'r'
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] OOM on EXPLAIN with lots of nodes
On 13.01.2015 16:47, Heikki Linnakangas wrote: Hmm, something like the attached? Seems reasonable... - Heikki yes, i have tested something like this, it stopped eating memory Just one small notice to the patch you attached: maybe it would be more safe to switch to oldcxt before calling ExplainPropertyText/ExplainPropertyList? Otherwise are you pretty sure ExplainPropertyText and ExplainPropertyList are not going perform any pallocs without immediate pfrees in current memory context even in future? Regards, Alexey Bashtanov -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] OOM on EXPLAIN with lots of nodes
Hello! I found that EXPLAIN command takes very much memory to execute when huge unions are used. For example the following sql -- begin sql create table t (a000 int, a001 int, ... a099 int); explain select * from ( select a001 a from t union all select a001 a from t union all ... (1000 times) ... union all select a001 a from t ) _ where a = 1; -- end sql took more than 1GB of memory to execute. Namely converting of the plan to a human-readable form causes excessive memory usage, not planning itself. By varying the parameters and reading source code I determined that memory usage linearly depends on (plan nodes count)*(overall columns count), thus it quadratically depends on number of tables unionized. To remove this excessive memory usage I propose to run deparse_context_for_planstate+deparse_expression in a separate memory context and free it after a plan node is generated. Any reasons to treat this idea as bad? BTW in this case explain execution is also quite long (I got tens of seconds). But I have no immediate ideas how to improve it. Regards, Alexey Bashtanov -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Autovacuum fails to keep visibility map up-to-date in mostly-insert-only-tables
Hello! Autovacuum daemon performs vacuum when the number of rows updated/deleted (n_dead_tuples) reaches some threshold. Similarly it performs analyze when the number of rows changed in any way (incl. inserted). When a table is mostly insert-only, its visibility map is not updated as vacuum threshold is almost never reached, but analyze does not update visibility map. Why could it be a bad idea to run vacuum after some number of any changes including inserts, like analyze? Or at least make it tunable by user (add a second bunch of paramters to control second vacuum threshold, disabled by default)? Best regards, Alexey Bashtanov -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
