Re: Extremely slow HashAggregate in simple UNION query
Jeff Janes <[email protected]>
| Newsgroups | gmane.comp.db.postgresql.performance |
|---|---|
| Message-ID | <CAMkU=1x+aT9kWCWKoskOdQPAdXZwLsv-cpKJ6B=rytCibfrpyA@mail.gmail.com> |
On Tue, Aug 20, 2019 at 11:12 AM Felix Geisendörfer <[email protected]> wrote: ... > [1] My actual query had bad estimates for other reasons (GIN Index), but > that's another story. The query above was of course deliberately designed > to have bad estimates. > As noted elsewhere, v12 thwarts your attempts to deliberately design the bad estimates. You can still get them, you just have to work a bit harder at it: CREATE FUNCTION j (bigint, bigint) returns setof bigint as $$ select generate_series($1,$2) $$ rows 1000 language sql; EXPLAIN ANALYZE SELECT * FROM j(1, 1) a, j(1, 1) b UNION SELECT * FROM j(1, 1) a, j(1, 1) b; QUERY PLAN -------------------------------------------------------------------------------------------------------------------------- HashAggregate (cost=80021.00..100021.00 rows=2000000 width=16) (actual time=11.332..13.241 rows=1 loops=1) Group Key: a.a, b.b -> Append (cost=0.50..70021.00 rows=2000000 width=16) (actual time=0.118..0.163 rows=2 loops=1) -> Nested Loop (cost=0.50..20010.50 rows=1000000 width=16) (actual time=0.117..0.118 rows=1 loops=1) -> Function Scan on j a (cost=0.25..10.25 rows=1000 width=8) (actual time=0.087..0.088 rows=1 loops=1) -> Function Scan on j b (cost=0.25..10.25 rows=1000 width=8) (actual time=0.027..0.027 rows=1 loops=1) -> Nested Loop (cost=0.50..20010.50 rows=1000000 width=16) (actual time=0.044..0.044 rows=1 loops=1) -> Function Scan on j a_1 (cost=0.25..10.25 rows=1000 width=8) (actual time=0.022..0.022 rows=1 loops=1) -> Function Scan on j b_1 (cost=0.25..10.25 rows=1000 width=8) (actual time=0.020..0.021 rows=1 loops=1) Planning Time: 0.085 ms Execution Time: 69.277 ms (11 rows) But the same advance in v12 which makes it harder to fool with your test case also opens the possibility of fixing your real case. I've made an extension which has a function which always returns true, but lies about how often it is expected to return true. See the attachment. With that, you can fine-tune the planner. CREATE EXTENSION pg_selectivities ; EXPLAIN ANALYZE SELECT * FROM j(1, 1) a, j(1, 1) b where pg_always(0.00001) UNION SELECT * FROM j(1, 1) a, j(1, 1) b where pg_always(0.00001); QUERY PLAN -------------------------------------------------------------------------------------------------------------------------- HashAggregate (cost=45021.40..45021.60 rows=20 width=16) (actual time=0.226..0.227 rows=1 loops=1) Group Key: a.a, b.b -> Append (cost=0.50..45021.30 rows=20 width=16) (actual time=0.105..0.220 rows=2 loops=1) -> Nested Loop (cost=0.50..22510.50 rows=10 width=16) (actual time=0.104..0.105 rows=1 loops=1) Join Filter: pg_always('1e-05'::double precision) -> Function Scan on j a (cost=0.25..10.25 rows=1000 width=8) (actual time=0.066..0.066 rows=1 loops=1) -> Function Scan on j b (cost=0.25..10.25 rows=1000 width=8) (actual time=0.035..0.035 rows=1 loops=1) -> Nested Loop (cost=0.50..22510.50 rows=10 width=16) (actual time=0.112..0.113 rows=1 loops=1) Join Filter: pg_always('1e-05'::double precision) -> Function Scan on j a_1 (cost=0.25..10.25 rows=1000 width=8) (actual time=0.077..0.077 rows=1 loops=1) -> Function Scan on j b_1 (cost=0.25..10.25 rows=1000 width=8) (actual time=0.034..0.034 rows=1 loops=1) Planning Time: 0.139 ms Execution Time: 0.281 ms Cheers, Jeff
pg_selectivities.patch
(application/octet-stream, 5.9 KB)
diff --git a/contrib/pg_selectivities/.gitignore b/contrib/pg_selectivities/.gitignore
new file mode 100644
index 0000000..5dcb3ff
--- /dev/null
+++ b/contrib/pg_selectivities/.gitignore
@@ -0,0 +1,4 @@
+# Generated subdirectories
+/log/
+/results/
+/tmp_check/
diff --git a/contrib/pg_selectivities/Makefile b/contrib/pg_selectivities/Makefile
new file mode 100644
index 0000000..9843326
--- /dev/null
+++ b/contrib/pg_selectivities/Makefile
@@ -0,0 +1,21 @@
+# contrib/pg_selectivities/Makefile
+
+MODULE_big = pg_selectivities
+OBJS = pg_selectivities.o $(WIN32RES)
+
+EXTENSION = pg_selectivities
+DATA = pg_selectivities--1.0.sql
+PGFILEDESC = "pg_selectivities - functions to implemente planner hints"
+
+REGRESS = pg_selectivities
+
+ifdef USE_PGXS
+PG_CONFIG = pg_config
+PGXS := $(shell $(PG_CONFIG) --pgxs)
+include $(PGXS)
+else
+subdir = contrib/pg_selectivities
+top_builddir = ../..
+include $(top_builddir)/src/Makefile.global
+include $(top_srcdir)/contrib/contrib-global.mk
+endif
diff --git a/contrib/pg_selectivities/expected/pg_selectivities.out b/contrib/pg_selectivities/expected/pg_selectivities.out
new file mode 100644
index 0000000..11fdf3f
--- /dev/null
+++ b/contrib/pg_selectivities/expected/pg_selectivities.out
@@ -0,0 +1,30 @@
+set cpu_operator_cost TO 0;
+set cpu_tuple_cost TO 0;
+create extension pg_selectivities;
+explain select * from (select 7 from generate_series(1,1000000)) foo(x) where x=7;
+ QUERY PLAN
+--------------------------------------------------------------------------
+ Function Scan on generate_series (cost=0.00..0.00 rows=1000000 width=4)
+(1 row)
+
+explain select * from (select 7 from generate_series(1,1000000)) foo(x) where x=7 and pg_always(1);
+ QUERY PLAN
+--------------------------------------------------------------------------
+ Function Scan on generate_series (cost=0.00..0.00 rows=1000000 width=4)
+ Filter: pg_always('1'::double precision)
+(2 rows)
+
+explain select * from (select 7 from generate_series(1,1000000)) foo(x) where x=7 and pg_always(0.001);
+ QUERY PLAN
+-----------------------------------------------------------------------
+ Function Scan on generate_series (cost=0.00..0.00 rows=1000 width=4)
+ Filter: pg_always('0.001'::double precision)
+(2 rows)
+
+explain select * from (select 7 from generate_series(1,1000000)) foo(x) where x=6 or pg_never(0.01);
+ QUERY PLAN
+------------------------------------------------------------------------
+ Function Scan on generate_series (cost=0.00..0.00 rows=10000 width=4)
+ Filter: pg_never('0.01'::double precision)
+(2 rows)
+
diff --git a/contrib/pg_selectivities/pg_selectivities--1.0.sql b/contrib/pg_selectivities/pg_selectivities--1.0.sql
new file mode 100644
index 0000000..90307a8
--- /dev/null
+++ b/contrib/pg_selectivities/pg_selectivities--1.0.sql
@@ -0,0 +1,21 @@
+/* contrib/pg_selectivities/pg_selectivities--1.0.sql */
+
+-- complain if script is sourced in psql, rather than via CREATE EXTENSION
+\echo Use "CREATE EXTENSION pg_selectivities" to load this file. \quit
+
+CREATE FUNCTION pg_selectivities_support(internal)
+RETURNS internal
+AS 'MODULE_PATHNAME'
+LANGUAGE C IMMUTABLE STRICT PARALLEL SAFE;
+
+CREATE FUNCTION pg_always(float)
+RETURNS boolean
+AS 'MODULE_PATHNAME'
+LANGUAGE C STRICT PARALLEL SAFE
+SUPPORT pg_selectivities_support;
+
+CREATE FUNCTION pg_never(float)
+RETURNS boolean
+AS 'MODULE_PATHNAME'
+LANGUAGE C STRICT PARALLEL SAFE
+SUPPORT pg_selectivities_support;
diff --git a/contrib/pg_selectivities/pg_selectivities.c b/contrib/pg_selectivities/pg_selectivities.c
new file mode 100644
index 0000000..9a8ef54
--- /dev/null
+++ b/contrib/pg_selectivities/pg_selectivities.c
@@ -0,0 +1,50 @@
+/*
+ * contrib/pg_selectivities/pg_selectivities.c
+ */
+#include "postgres.h"
+#include "nodes/supportnodes.h"
+#include "fmgr.h"
+
+PG_MODULE_MAGIC;
+
+PG_FUNCTION_INFO_V1(pg_selectivities_support);
+PG_FUNCTION_INFO_V1(pg_always);
+PG_FUNCTION_INFO_V1(pg_never);
+
+Datum
+pg_selectivities_support(PG_FUNCTION_ARGS)
+{
+ Node *rawreq = (Node *) PG_GETARG_POINTER(0);
+ Node *ret = NULL;
+
+ if (IsA(rawreq, SupportRequestSelectivity))
+ {
+ SupportRequestSelectivity *req = (SupportRequestSelectivity *) rawreq;
+ if (list_length(req->args) == 1)
+ {
+ Node * node;
+ node=(Node*) linitial(req->args);
+ if (IsA(node, Const))
+ {
+ Selectivity s1;
+ s1= DatumGetFloat8(((Const*)node)->constvalue);
+ req->selectivity = s1;
+ ret = (Node *) req;
+ }
+ }
+ }
+
+ PG_RETURN_POINTER(ret);
+}
+
+Datum
+pg_always(PG_FUNCTION_ARGS)
+{
+ PG_RETURN_BOOL(true);
+}
+
+Datum
+pg_never(PG_FUNCTION_ARGS)
+{
+ PG_RETURN_BOOL(false);
+}
diff --git a/contrib/pg_selectivities/pg_selectivities.control b/contrib/pg_selectivities/pg_selectivities.control
new file mode 100644
index 0000000..1605068
--- /dev/null
+++ b/contrib/pg_selectivities/pg_selectivities.control
@@ -0,0 +1,5 @@
+# pg_selectivities extension
+comment = 'functions which always return true or false, but report a custom selectivity'
+default_version = '1.0'
+module_pathname = '$libdir/pg_selectivities'
+relocatable = true
diff --git a/contrib/pg_selectivities/sql/pg_selectivities.sql b/contrib/pg_selectivities/sql/pg_selectivities.sql
new file mode 100644
index 0000000..47dd0fc
--- /dev/null
+++ b/contrib/pg_selectivities/sql/pg_selectivities.sql
@@ -0,0 +1,8 @@
+set cpu_operator_cost TO 0;
+set cpu_tuple_cost TO 0;
+create extension pg_selectivities;
+
+explain select * from (select 7 from generate_series(1,1000000)) foo(x) where x=7;
+explain select * from (select 7 from generate_series(1,1000000)) foo(x) where x=7 and pg_always(1);
+explain select * from (select 7 from generate_series(1,1000000)) foo(x) where x=7 and pg_always(0.001);
+explain select * from (select 7 from generate_series(1,1000000)) foo(x) where x=6 or pg_never(0.01);