|
The query plan doesn't look too bad, at least the indexes used look fine, so there doesn't seem to be any improvement potential by just changing the system schema.
So this may actually be something that needs to be solved on the MariaDB server side in the end.
But for now this can cause serious performance issues on the MariaDB server side.
Maybe the query should be re-implemented not using recursive CTE for now until the performance issue is solved on the server side?
|
|
What version of MariaDB did you use?
|
|
10.3.16
|
|
Here's a script I used to reproduce it:
#!/bin/bash
|
|
if [ $# -lt 3 ]
|
then
|
echo "USAGE: num_roles num_users num_databases"
|
exit 1
|
fi
|
|
num_roles=$1
|
num_users=$2
|
num_databases=$3
|
|
user=maxuser
|
password=maxpwd
|
host=127.0.0.1
|
port=3306
|
|
image=mariadb:10.4
|
container_name=create-users
|
|
docker run --network=host --name $container_name -d -e MYSQL_ALLOW_EMPTY_PASSWORD=Y $image --port=$port &> /dev/null
|
|
while true
|
do
|
mysql -uroot -h $host -P $port -e "create user $user identified by '$password'; grant all on *.* to $user with grant option;" >& /dev/null
|
|
if [ $? -eq 0 ]
|
then
|
break
|
fi
|
sleep 5
|
done
|
|
for ((i=0;i<$num_roles;i++))
|
do
|
mysql -u $user -p$password -h $host -P $port <<EOF
|
create role role$i;
|
grant all on *.* to role$i;
|
EOF
|
|
if [ $? -ne 0 ]
|
then
|
break
|
fi
|
done
|
|
for ((i=0;i<$num_users;i++))
|
do
|
mysql -u $user -p$password -h $host -P $port <<EOF
|
create user user$i identified by 'password';
|
grant role$((i%num_roles)) to user$i;
|
set default role role$((i%num_roles)) for user$i;
|
EOF
|
|
if [ $? -ne 0 ]
|
then
|
break
|
fi
|
done
|
|
for ((i=0;i<$num_databases;i++))
|
do
|
mysql -u $user -p$password -h $host -P $port <<EOF
|
create database db$i;
|
grant all on db$i.* to role$((i%num_roles));
|
EOF
|
|
if [ $? -ne 0 ]
|
then
|
break
|
fi
|
done
|
|
mysql -v -v -v -u $user -p$password -h $host -P $port <<EOF
|
ANALYZE WITH RECURSIVE t AS
|
(
|
SELECT u1.user, u1.host, d1.db, u1.select_priv, IF(u1.password <> '', u1.password, u1.authentication_string) AS password, u1.is_role, u1.default_role
|
FROM mysql.user AS u1
|
LEFT JOIN mysql.db AS d1
|
ON (u1.user = d1.user AND u1.host = d1.host)
|
WHERE u1.plugin IN ('', 'mysql_native_password')
|
UNION
|
SELECT u.user, u.host, t.db, u.select_priv, IF(u.password <> '', u.password, u.authentication_string), u.is_role, u.default_role
|
FROM mysql.user AS u
|
LEFT JOIN mysql.tables_priv AS t
|
ON (u.user = t.user AND u.host = t.host)
|
WHERE u.plugin IN ('', 'mysql_native_password')
|
)
|
, users AS
|
(
|
SELECT t.user, t.host, t.db, t.select_priv, t.password, t.default_role AS role
|
FROM t
|
WHERE t.is_role <> 'Y'
|
UNION
|
SELECT u.user, u.host, t.db, t.select_priv, u.password, r.role
|
FROM t
|
JOIN users AS u ON (t.user = u.role)
|
LEFT JOIN mysql.roles_mapping AS r ON (t.user = r.user)
|
)
|
SELECT DISTINCT t.user, t.host, t.db, t.select_priv, t.password
|
FROM users AS t
|
WHERE t.user <> 'root'
|
;
|
EOF
|
|
docker rm -vf $container_name > /dev/null
|
|
|
Numbers and explain plan are from 10.3.16, on 10.4 it gets even worse as mysql.user is now actually a view, so the query takes even about twice as long as on 10.3.16
|
|
Analyzing the issue piece-by-piece.
I take the first recursive CTE:
analyze format=json
|
WITH RECURSIVE t AS
|
(
|
/*500 rows instantly */
|
SELECT u1.user, u1.host, d1.db, u1.select_priv,
|
IF(u1.password <> '', u1.password, u1.authentication_string) AS password,
|
u1.is_role, u1.default_role
|
FROM user AS u1
|
LEFT JOIN db AS d1
|
ON (u1.user = d1.user AND u1.host = d1.host)
|
WHERE u1.plugin IN ('', 'mysql_native_password')
|
UNION
|
SELECT u.user, u.host, t.db, u.select_priv,
|
IF(u.password <> '', u.password, u.authentication_string),
|
u.is_role, u.default_role
|
FROM user AS u
|
LEFT JOIN tables_priv AS t
|
ON (u.user = t.user AND u.host = t.host)
|
WHERE u.plugin IN ('', 'mysql_native_password')
|
)
|
, add select * from t and run ANALYZE:
https://gist.github.com/spetrunia/d42d9013fa1bd964442c68e2bf0f190e
- 144 ms in total,
- of these, 101.22 ms are in joining u1 to d1
- * Note that the join is done on (u1.user = d1.user AND u1.host = d1.host) but the table db doesnt' have an index that could use both conditions.
- AFAIU, there was only one recursion step. One can see it here:
"query_block": {
|
"select_id": 3,
|
"operation": "UNION",
|
"r_loops": 1,
|
|
The whole query takes 5.30 sec for me, so the issue is not in this part
|
|
DELIMITER // ;
|
BEGIN NOT ATOMIC
|
|
DROP TEMPORARY TABLE IF EXISTS t;
|
|
CREATE TEMPORARY TABLE t AS
|
SELECT u.user, u.host, d.db, u.select_priv,
|
IF(u.password <> '', u.password, u.authentication_string) AS password,
|
u.is_role, u.default_role
|
FROM mysql.user AS u LEFT JOIN mysql.db AS d
|
ON (u.user = d.user AND u.host = d.host)
|
WHERE u.plugin IN ('', 'mysql_native_password')
|
UNION
|
SELECT u.user, u.host, t.db, u.select_priv,
|
IF(u.password <> '', u.password, u.authentication_string),
|
u.is_role, u.default_role
|
FROM mysql.user AS u LEFT JOIN mysql.tables_priv AS t
|
ON (u.user = t.user AND u.host = t.host)
|
WHERE u.plugin IN ('', 'mysql_native_password');
|
|
WITH RECURSIVE mxs_users AS (
|
SELECT t.user, t.host, t.db, t.select_priv, t.password, t.default_role AS role FROM t
|
WHERE t.is_role <> 'Y'
|
UNION
|
SELECT u.user, u.host, t.db, t.select_priv, u.password, r.role FROM t
|
JOIN mxs_users AS u
|
ON (t.user = u.role)
|
LEFT JOIN mysql.roles_mapping AS r
|
ON (t.user = r.user)
|
)
|
SELECT DISTINCT t.user, t.host, t.db, t.select_priv, t.password FROM mxs_users AS t;
|
|
DROP TEMPORARY TABLE t;
|
|
END //
|
DELIMITER ; //
|
With this SQL the time drops down from 2 seconds to around 1.5 seconds. Moving the SELECT of mysql.roles_mapping into a separate temporary table had no effect.
Here's the ANALYZE FORMAT=JSON output for the recursive part:
{
|
"query_block": {
|
"select_id": 1,
|
"r_loops": 1,
|
"r_total_time_ms": 1261.7,
|
"temporary_table": {
|
"table": {
|
"table_name": "<derived2>",
|
"access_type": "ALL",
|
"r_loops": 1,
|
"rows": 1466,
|
"r_rows": 1503,
|
"r_total_time_ms": 1.6321,
|
"filtered": 100,
|
"r_filtered": 100,
|
"materialized": {
|
"query_block": {
|
"recursive_union": {
|
"table_name": "<union2,3>",
|
"access_type": "ALL",
|
"r_loops": 0,
|
"r_rows": null,
|
"query_specifications": [
|
{
|
"query_block": {
|
"select_id": 2,
|
"r_loops": 1,
|
"r_total_time_ms": 4.3407,
|
"table": {
|
"table_name": "t",
|
"access_type": "ALL",
|
"r_loops": 1,
|
"rows": 1466,
|
"r_rows": 1503,
|
"r_total_time_ms": 0.9986,
|
"filtered": 100,
|
"r_filtered": 33.466,
|
"attached_condition": "t.is_role <> 'Y'"
|
}
|
}
|
},
|
{
|
"query_block": {
|
"select_id": 3,
|
"operation": "UNION",
|
"r_loops": 2,
|
"r_total_time_ms": 1237.3,
|
"table": {
|
"table_name": "t",
|
"access_type": "ALL",
|
"r_loops": 2,
|
"rows": 1466,
|
"r_rows": 1503,
|
"r_total_time_ms": 0.9251,
|
"filtered": 100,
|
"r_filtered": 100
|
},
|
"block-nl-join": {
|
"table": {
|
"table_name": "r",
|
"access_type": "index",
|
"key": "Host",
|
"key_length": "660",
|
"used_key_parts": ["Host", "User", "Role"],
|
"r_loops": 2,
|
"rows": 1000,
|
"r_rows": 1000,
|
"r_total_time_ms": 0.6053,
|
"filtered": 100,
|
"r_filtered": 100,
|
"using_index": true
|
},
|
"buffer_type": "flat",
|
"buffer_size": "256Kb",
|
"join_type": "BNL",
|
"attached_condition": "trigcond(r.`User` = t.`User`)",
|
"r_filtered": 0.1332
|
},
|
"block-nl-join": {
|
"table": {
|
"table_name": "<derived2>",
|
"access_type": "ALL",
|
"r_loops": 2,
|
"rows": 1466,
|
"r_rows": 751.5,
|
"r_total_time_ms": 1.5385,
|
"filtered": 100,
|
"r_filtered": 100
|
},
|
"buffer_type": "incremental",
|
"buffer_size": "256Kb",
|
"join_type": "BNL",
|
"attached_condition": "convert(t.`User` using utf8mb4) = u.`role`",
|
"r_filtered": 0.0332
|
}
|
}
|
}
|
]
|
}
|
}
|
}
|
}
|
}
|
}
|
}
|
|
|
Looks like adding WHERE t.is_role = 'Y' into the second recursive CTE helps. This drops down the query length from around 2 second to 1 second.
|
|
Let's materialize the first recursive CTE:
create view v1 as
|
WITH RECURSIVE t AS
|
(
|
/*500 rows instantly */
|
SELECT u1.user, u1.host, d1.db, u1.select_priv,
|
IF(u1.password <> '', u1.password, u1.authentication_string) AS password,
|
u1.is_role, u1.default_role
|
FROM user AS u1
|
LEFT JOIN db AS d1
|
ON (u1.user = d1.user AND u1.host = d1.host)
|
WHERE u1.plugin IN ('', 'mysql_native_password')
|
UNION
|
SELECT u.user, u.host, t.db, u.select_priv,
|
IF(u.password <> '', u.password, u.authentication_string),
|
u.is_role, u.default_role
|
FROM user AS u
|
LEFT JOIN tables_priv AS t
|
ON (u.user = t.user AND u.host = t.host)
|
WHERE u.plugin IN ('', 'mysql_native_password')
|
)
|
select * from t;
|
create table t_mat as select * from v1;
|
t_mat has 860 rows
|
|
... running the second part of the CTE : https://gist.github.com/spetrunia/5b9a7a296539fb3f22bef2faa808eaf5
an obvious issue is that table r (roles) is using a full index scan...
alter table roles_mapping add index(User);
|
https://gist.github.com/spetrunia/f1b5af91eaf76012342eb44a81dc77e8 - this speeds up the query 2x for me.
|
|
One other observation:
the numbers of rows examined at each iteration look as if the RCTE execution used the "non-linear" algorithm. (The "linear" algorithm is where only last-generation items contribute to construction of the next generation of recursive CTE population. The non-linear one is where all generations can contribute).
IIRC, the linear algorithm requires that the recursive select is an INNER join. If it is a LEFT JOIN, then it is not applicable.
markus makela: is LEFT JOIN necessary in the recursive part (can it be replaced with a subquery in the select list?)
I will also check the above with Igor. (looking at the ANALYZE output, I don't see any indication which algorithm is used.. I think it should be shown)
|
|
Following Igor's advice, checking is_unrestricted in bool st_select_lex_unit::exec_recursive(). It is equal to false. (So, it seems, the linear algorithm is used)
|
|
For comparison: same query on PostgreSQL takes around 100ms
|
|
EXPLAIN ANALYZE output from PostgreSQL 12:
QUERY PLAN
|
-----------------------------------------------------------------------------------------------------------------------------------------------
|
Unique (cost=355251.32..360281.32 rows=33702 width=1004) (actual time=42.786..46.716 rows=1489 loops=1)
|
CTE t
|
-> HashAggregate (cost=117.61..125.83 rows=822 width=1336) (actual time=1.218..1.316 rows=860 loops=1)
|
Group Key: u1."user", u1.host, d1.db, u1.select_priv, u1.password, u1.is_role, u1.default_role
|
-> Append (cost=34.30..103.23 rows=822 width=1336) (actual time=0.282..0.726 rows=984 loops=1)
|
-> Hash Right Join (cost=34.30..57.06 rows=411 width=334) (actual time=0.282..0.493 rows=556 loops=1)
|
Hash Cond: ((d1."user" = u1."user") AND (d1.host = u1.host))
|
-> Seq Scan on db d1 (cost=0.00..20.42 rows=442 width=207) (actual time=0.002..0.042 rows=442 loops=1)
|
-> Hash (cost=28.14..28.14 rows=411 width=269) (actual time=0.271..0.271 rows=411 loops=1)
|
Buckets: 1024 Batches: 1 Memory Usage: 129kB
|
-> Seq Scan on "user" u1 (cost=0.00..28.14 rows=411 width=269) (actual time=0.011..0.155 rows=411 loops=1)
|
Filter: (plugin = ANY ('{"",mysql_native_password}'::bpchar[]))
|
-> Hash Left Join (cost=2.60..33.84 rows=411 width=529) (actual time=0.022..0.198 rows=428 loops=1)
|
Hash Cond: ((u."user" = t_1."user") AND (u.host = t_1.host))
|
-> Seq Scan on "user" u (cost=0.00..28.14 rows=411 width=269) (actual time=0.002..0.098 rows=411 loops=1)
|
Filter: (plugin = ANY ('{"",mysql_native_password}'::bpchar[]))
|
-> Hash (cost=2.24..2.24 rows=24 width=828) (actual time=0.013..0.013 rows=24 loops=1)
|
Buckets: 1024 Batches: 1 Memory Usage: 14kB
|
-> Seq Scan on tables_priv t_1 (cost=0.00..2.24 rows=24 width=828) (actual time=0.003..0.007 rows=24 loops=1)
|
CTE users
|
-> Recursive Union (cost=0.00..21052.00 rows=337018 width=1328) (actual time=1.221..22.519 rows=8623 loops=1)
|
-> CTE Scan on t t_2 (cost=0.00..18.50 rows=818 width=1328) (actual time=1.220..1.639 rows=726 loops=1)
|
Filter: (is_role <> 'Y'::bpchar)
|
Rows Removed by Filter: 134
|
-> Hash Join (cost=70.62..1429.31 rows=33620 width=1085) (actual time=0.241..2.058 rows=9461 loops=3)
|
Hash Cond: (u_1.role = t_3."user")
|
-> WorkTable Scan on users u_1 (cost=0.00..163.60 rows=8180 width=1060) (actual time=0.000..0.115 rows=2874 loops=3)
|
-> Hash (cost=60.34..60.34 rows=822 width=673) (actual time=0.642..0.642 rows=1448 loops=1)
|
Buckets: 2048 (originally 1024) Batches: 1 (originally 1) Memory Usage: 281kB
|
-> Hash Right Join (cost=26.72..60.34 rows=822 width=673) (actual time=0.217..0.386 rows=1448 loops=1)
|
Hash Cond: (r."user" = t_3."user")
|
-> Seq Scan on roles_mapping r (cost=0.00..7.77 rows=177 width=162) (actual time=0.002..0.016 rows=177 loops=1)
|
-> Hash (cost=16.44..16.44 rows=822 width=592) (actual time=0.208..0.208 rows=860 loops=1)
|
Buckets: 1024 Batches: 1 Memory Usage: 134kB
|
-> CTE Scan on t t_3 (cost=0.00..16.44 rows=822 width=592) (actual time=0.001..0.070 rows=860 loops=1)
|
-> Sort (cost=334073.48..334911.82 rows=335333 width=1004) (actual time=42.785..44.349 rows=8620 loops=1)
|
Sort Key: t."user", t.host, t.db, t.select_priv, t.password
|
Sort Method: external merge Disk: 2160kB
|
-> CTE Scan on users t (cost=0.00..7582.90 rows=335333 width=1004) (actual time=1.222..27.884 rows=8620 loops=1)
|
Filter: ("user" <> 'root'::bpchar)
|
Rows Removed by Filter: 3
|
Planning Time: 3.635 ms
|
Execution Time: 49.416 ms
|
|
|
Non-zero handler counter increments:
mysql> show status like 'Handler%';
|
+----------------------------+-------+
|
| Variable_name | Value |
|
+----------------------------+-------+
|
| Handler_read_first | 8 |
|
| Handler_read_key | 4234 |
|
| Handler_read_next | 89388 |
|
| Handler_read_rnd_next | 33911 |
|
| Handler_tmp_write | 67013 |
|
+----------------------------+-------+
|
In MySQL 8.0.18, the query takes ~6 sec (debug build, laptop). The counter
increments:
mysql> show status like 'Handler%';
|
+----------------------------+---------+
|
| Variable_name | Value |
|
+----------------------------+---------+
|
| Handler_commit | 86 |
|
| Handler_external_lock | 302 |
|
| Handler_read_first | 20589 |
|
| Handler_read_key | 47654 |
|
| Handler_read_next | 3675830 |
|
| Handler_read_rnd_next | 20422 |
|
| Handler_write | 10972 |
|
+----------------------------+---------+
|
Explain:
mysql> source Downloads/MXS-2759-query-explain.sql
|
+----+--------------+------------+------------+-------+---------------+-------------+---------+------------+--------+----------+------------------------------+
|
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
|
+----+--------------+------------+------------+-------+---------------+-------------+---------+------------+--------+----------+------------------------------+
|
| 1 | PRIMARY | <derived2> | NULL | ALL | NULL | NULL | NULL | NULL | 651728 | 90.00 | Using where; Using temporary |
|
| 2 | DERIVED | <derived3> | NULL | ALL | NULL | NULL | NULL | NULL | 410 | 90.00 | Using where |
|
| 3 | DERIVED | u1 | NULL | ALL | NULL | NULL | NULL | NULL | 411 | 20.00 | Using where |
|
| 3 | DERIVED | d1 | NULL | ref | PRIMARY,User | User | 240 | j8.u1.User | 4 | 100.00 | Using where |
|
| 4 | UNION | u | NULL | ALL | NULL | NULL | NULL | NULL | 411 | 20.00 | Using where |
|
| 4 | UNION | t | NULL | ref | PRIMARY | PRIMARY | 180 | j8.u.Host | 1 | 100.00 | Using where; Using index |
|
| NULL | UNION RESULT | <union3,4> | NULL | ALL | NULL | NULL | NULL | NULL | NULL | NULL | Using temporary |
|
| 5 | UNION | u | NULL | ALL | NULL | NULL | NULL | NULL | 368 | 100.00 | Recursive; Using where |
|
| 5 | UNION | <derived3> | NULL | ref | <auto_key1> | <auto_key1> | 240 | u.role | 10 | 100.00 | NULL |
|
| 5 | UNION | r | NULL | index | NULL | Host | 660 | NULL | 177 | 100.00 | Using where; Using index |
|
| NULL | UNION RESULT | <union2,5> | NULL | ALL | NULL | NULL | NULL | NULL | NULL | NULL | Using temporary |
|
+----+--------------+------------+------------+-------+---------------+-------------+---------+------------+--------+----------+------------------------------+
|
Back to MariaDB,
max_recursive_iterations - qquery_time
|
1 - 1.6 sec
|
2 - 5.4 sec
|
3 - 5.87 sec
|
4 - 5.91 sec
|
5 - 5.84 sec
|
|
|
Running the recursive execution manually
=== Step0 ===
create table t_step0 as
|
SELECT u.user, u.host, t.db, t.select_priv, u.password, r.role
|
FROM t_mat as t
|
JOIN users AS u ON (t.user = u.role)
|
LEFT JOIN roles_mapping AS r ON (t.user = r.user)
|
# 726 rows
|
=== Step 1 ===
create table t_step1 as
|
SELECT u.user, u.host, t.db, t.select_priv, u.password, r.role
|
FROM t_mat as t
|
JOIN t_step0 AS u ON (t.user = u.role)
|
LEFT JOIN roles_mapping AS r ON (t.user = r.user)
|
# 8557 rows
|
|
create table t_step1A as select * from t_step1 except select * from t_step0;
|
# 7261 rows
|
=== Step 2 ==
create table t_step2 as
|
SELECT u.user, u.host, t.db, t.select_priv, u.password, r.role
|
FROM t_mat as t
|
JOIN t_step1A AS u ON (t.user = u.role)
|
LEFT JOIN roles_mapping AS r ON (t.user = r.user)
|
# 19826 rows
|
|
create table t_step2A as select * from t_step2 except select * from t_step1;
|
# 636 rows
|
== Step 3 ===
create table t_step3 as
|
SELECT u.user, u.host, t.db, t.select_priv, u.password, r.role
|
FROM t_mat as t
|
JOIN t_step2A AS u ON (t.user = u.role)
|
LEFT JOIN roles_mapping AS r ON (t.user = r.user)
|
# 0 rows.
|
|
|
So, the joins involved have noticeable fanout.
Removing the irrelevant parts of the query,
then trying to enable BNL-H join (an alternative to adding an index on roles_mapping). It gives a get a ~2x speedup :
https://gist.github.com/spetrunia/3c35471837c3bb61f4e69e88e99996ab - 5.75 sec
https://gist.github.com/spetrunia/17fb71db13468d40a92095ce121596fb - 2.32 sec.
|
|
The same simplified query with PostgreSQL (Note: PG is a release build, MariaDB is a debug build. )
WITH RECURSIVE users AS
|
(
|
SELECT t.user, t.host, t.db, t.select_priv, t.password, t.default_role AS role
|
FROM t_mat as t
|
WHERE t.is_role <> 'Y'
|
UNION
|
SELECT u.user, u.host, t.db, t.select_priv, u.password, r.role
|
FROM t_mat as t
|
JOIN users AS u ON (t.user = u.role)
|
LEFT JOIN roles_mapping AS r ON (t.user = r.user)
|
)
|
SELECT t2.user, t2.host, t2.db, t2.select_priv, t2.password
|
FROM users AS t2
|
WHERE t2.user <> 'root';
|
|
|
QUERY PLAN
|
------------------------------------------------------------------------------------------------------------------------------------------------
|
CTE Scan on users t2 (cost=18566.60..25606.98 rows=311341 width=1004) (actual time=0.010..25.368 rows=8620 loops=1)
|
Filter: ("user" <> 'root'::bpchar)
|
Rows Removed by Filter: 3
|
CTE users
|
-> Recursive Union (cost=0.00..18566.60 rows=312906 width=884) (actual time=0.008..20.399 rows=8623 loops=1)
|
-> Seq Scan on t_mat t (cost=0.00..46.75 rows=726 width=332) (actual time=0.007..0.128 rows=726 loops=1)
|
Filter: (is_role <> 'Y'::bpchar)
|
Rows Removed by Filter: 134
|
-> Hash Join (cost=97.24..1226.17 rows=31218 width=884) (actual time=0.205..2.017 rows=9461 loops=3)
|
Hash Cond: (u.role = t_1."user")
|
-> WorkTable Scan on users u (cost=0.00..145.20 rows=7260 width=1060) (actual time=0.000..0.114 rows=2874 loops=3)
|
-> Hash (cost=86.49..86.49 rows=860 width=229) (actual time=0.550..0.550 rows=1448 loops=1)
|
Buckets: 2048 (originally 1024) Batches: 1 (originally 1) Memory Usage: 281kB
|
-> Hash Right Join (cost=55.35..86.49 rows=860 width=229) (actual time=0.189..0.354 rows=1448 loops=1)
|
Hash Cond: (r."user" = t_1."user")
|
-> Seq Scan on roles_mapping r (cost=0.00..7.77 rows=177 width=162) (actual time=0.003..0.014 rows=177 loops=1)
|
-> Hash (cost=44.60..44.60 rows=860 width=148) (actual time=0.183..0.183 rows=860 loops=1)
|
Buckets: 1024 Batches: 1 Memory Usage: 134kB
|
-> Seq Scan on t_mat t_1 (cost=0.00..44.60 rows=860 width=148) (actual time=0.002..0.075 rows=860 loops=1)
|
Planning Time: 0.755 ms
|
Execution Time: 26.289 ms
|
(21 rows)
|
|
|
PG also does 3 iterations, has the same #rows at start.
It uses hash joins for each join operation, which benefits it in the situation where a large portion of the table participates in the join.
Another interesting part starts here:
-> WorkTable Scan on users u (cost=0.00..145.20 rows=7260 width=1060) (actual time=0.000..0.114 rows=2874 loops=3)
|
-> Hash (cost=86.49..86.49 rows=860 width=229) (actual time=0.550..0.550 rows=1448 loops=1)
|
WorkTable Scan has loops=3, while the rest of the join has loops=1. Joining roles_mapping with t_mat and hashing the result is done only once. A part of recursive CTE is "factored out", it seems.
|
|
If I manually "factor out" the t_mat left join roles_mapping operation, exec time (to both fill the temp. table and run the query) gets down to ~1 second https://gist.github.com/spetrunia/2cf035d7a70820497079546589f04caf
(EDIT: WAIT, that was also without the "PASSWORD" field. With PASSWORD, the time is 2.31 sec)
|
|
On a release build (and a bit weaker CPU):
- factored out query: 330 ms (1/3rd debug build)
- factored out query, w/o password field: 110 ms (2.3x faster debug build)
- non-factored query: 1 sec
|
|
psergey any suggestions on what to change in the SQL to make this perform better? We've added some optimizations that appear to work.
|
|
Added minor optimizations to the SQL. Creating an index on the User field of the role_mapping table can also improve performance.
|
MXS-2759-data.sql