预置条件
CREATE TABLE st(
zjhm text, -- 证件号码
rzsj timestamptz, -- 入住时间
ldsj timestamptz -- 离店时间
);- 场景1 ABS < interval
原始SQL
SELECT *
FROM st a, st b
WHERE abs(a.rzsj-b.rzsj) < interval '10 min'
-- 执行计划
postgres=# EXPLAIN SELECT *
postgres-# FROM st a, st b
postgres-# WHERE abs(a.rzsj-b.rzsj) < interval '10 min'
postgres-# ;
QUERY PLAN
-------------------------------------------------------------------------------------
id | operation | E-rows | E-memory | E-width | E-costs
----+--------------------------------------+--------+----------+---------+---------
1 | -> Streaming (type: GATHER) | 300 | | 96 | 42.18
2 | -> Nested Loop (3,5) | 300 | 1MB | 96 | 29.68
3 | -> Streaming(type: BROADCAST) | 90 | 2MB | 48 | 12.80
4 | -> Seq Scan on st a | 30 | 1MB | 48 | 10.10
5 | -> Materialize | 30 | 16MB | 48 | 10.15
6 | -> Seq Scan on st b | 30 | 1MB | 48 | 10.10
Predicate Information (identified by plan id)
-----------------------------------------------------------------------------
2 --Nested Loop (3,5)
Join Filter: (abs(((a.rzsj - b.rzsj))::numeric) < .00694444444444444)
改写SQL
WITH t AS(
SELECT
zjhm,
rzsj,
ldsj,
extract(epoch from rzsj)::numeric AS rzlen,
interval '10min' as interval,
extract(epoch from interval '10min')::numeric AS step,
trunc(rzlen/step)::bigint AS rzmod
FROM st
)
SELECT
a.zjhm, a.rzsj, a.ldsj, b.zjhm, b.rzsj, b.ldsj
FROM t a, t b
WHERE a.rzmod = b.rzmod
UNION ALL
SELECT
a.zjhm, a.rzsj, a.ldsj, b.zjhm, b.rzsj, b.ldsj
FROM t a, t b
WHERE a.rzmod = b.rzmod + 1
AND a.rzsj-b.rzsj < a.interval
UNION ALL
SELECT
a.zjhm, a.rzsj, a.ldsj, b.zjhm, b.rzsj, b.ldsj
FROM t a, t b
WHERE a.rzmod = b.rzmod - 1
AND b.rzsj-a.rzsj < a.interval
;
-- 执行计划
QUERY PLAN
-------------------------------------------------------------------------------------------------
id | operation | E-rows | E-memory | E-width | E-costs
----+--------------------------------------------------+--------+----------+---------+---------
1 | -> Streaming (type: GATHER) | 9 | | 96 | 10.04
2 | -> Result | 9 | 1MB | 96 | 2.04
3 | -> CTE Append(4, 6) | 9 | 1MB | 96 | 2.04
4 | -> Streaming(type: LOCAL GATHER) | 3 | 2MB | 18 | 1.03
5 | -> Seq Scan on st [4, CTE t(1)] | 1 | 1MB | 18 | 1.03
6 | -> Append(7, 12, 17) | 9 | 1MB | 96 | 1.01
7 | -> Nested Loop (8,9) | 3 | 1MB | 96 | 0.32
8 | -> CTE Scan on t(1) a | 1 | 1MB | 56 | 0.02
9 | -> Materialize | 3 | 16MB | 56 | 0.29
10 | -> Streaming(type: BROADCAST) | 3 | 2MB | 56 | 0.29
11 | -> CTE Scan on t(1) b | 1 | 1MB | 56 | 0.02
12 | -> Nested Loop (13,14) | 3 | 1MB | 96 | 0.33
13 | -> CTE Scan on t(1) a | 1 | 1MB | 72 | 0.02
14 | -> Materialize | 3 | 16MB | 56 | 0.29
15 | -> Streaming(type: BROADCAST) | 3 | 2MB | 56 | 0.29
16 | -> CTE Scan on t(1) b | 1 | 1MB | 56 | 0.02
17 | -> Nested Loop (18,19) | 3 | 1MB | 96 | 0.33
18 | -> CTE Scan on t(1) a | 1 | 1MB | 72 | 0.02
19 | -> Materialize | 3 | 16MB | 56 | 0.29
20 | -> Streaming(type: BROADCAST) | 3 | 2MB | 56 | 0.29
21 | -> CTE Scan on t(1) b | 1 | 1MB | 56 | 0.02
Predicate Information (identified by plan id)
---------------------------------------------------------------------------------------
7 --Nested Loop (8,9)
Join Filter: (a.rzmod = b.rzmod)
12 --Nested Loop (13,14)
Join Filter: (((a.rzsj - b.rzsj) < a."interval") AND (a.rzmod = (b.rzmod + 1)))
17 --Nested Loop (18,19)
Join Filter: (((b.rzsj - a.rzsj) < a."interval") AND (a.rzmod = (b.rzmod - 1)))
计划对比
- 场景2 BETWEEN AND
原始SQL
SELECT a.zjhm, a.rzsj, a.ldsj, b.zjhm, b.rzsj, b.ldsj
FROM st a, st b
WHERE a.rzsj BETWEEN b.rzsj AND b.ldsj
;
-- 执行计划
QUERY PLAN
-------------------------------------------------------------------------------------
id | operation | E-rows | E-memory | E-width | E-costs
----+--------------------------------------+--------+----------+---------+---------
1 | -> Streaming (type: GATHER) | 173 | | 96 | 36.18
2 | -> Nested Loop (3,5) | 173 | 1MB | 96 | 28.18
3 | -> Streaming(type: BROADCAST) | 90 | 2MB | 48 | 12.80
4 | -> Seq Scan on st a | 30 | 1MB | 48 | 10.10
5 | -> Materialize | 30 | 16MB | 48 | 10.15
6 | -> Seq Scan on st b | 30 | 1MB | 48 | 10.10
Predicate Information (identified by plan id)
----------------------------------------------------------------
2 --Nested Loop (3,5)
Join Filter: ((a.rzsj >= b.rzsj) AND (a.rzsj <= b.ldsj))
改写SQL
WITH t AS(
SELECT
zjhm,
rzsj,
ldsj,
extract(epoch from rzsj)::numeric AS rzlen,
extract(epoch from interval (ldsj-rzsj))::numeric AS step,
trunc(rzlen/step)::bigint AS rzmod
FROM st
)
SELECT
a.zjhm, a.rzsj, a.ldsj, b.zjhm, b.rzsj, b.ldsj
FROM t a, t b
WHERE a.rzmod = b.rzmod
AND a.rzsj BETWEEN b.rzsj AND b.ldsj
UNION ALL
SELECT
a.zjhm, a.rzsj, a.ldsj, b.zjhm, b.rzsj, b.ldsj
FROM t a, t b
WHERE a.rzmod = b.rzmod + 1
AND a.rzsj BETWEEN b.rzsj AND b.ldsj
UNION ALL
SELECT
a.zjhm, a.rzsj, a.ldsj, b.zjhm, b.rzsj, b.ldsj
FROM t a, t b
WHERE a.rzmod = b.rzmod - 1
AND a.rzsj BETWEEN b.rzsj AND b.ldsj
;
-- 执行计划
QUERY PLAN
-------------------------------------------------------------------------------------------------
id | operation | E-rows | E-memory | E-width | E-costs
----+--------------------------------------------------+--------+----------+---------+---------
1 | -> Streaming (type: GATHER) | 9 | | 96 | 10.06
2 | -> Result | 9 | 1MB | 96 | 2.06
3 | -> CTE Append(4, 6) | 9 | 1MB | 96 | 2.06
4 | -> Streaming(type: LOCAL GATHER) | 3 | 2MB | 18 | 1.04
5 | -> Seq Scan on st [4, CTE t(1)] | 1 | 1MB | 18 | 1.04
6 | -> Append(7, 12, 17) | 9 | 1MB | 96 | 1.02
7 | -> Nested Loop (8,9) | 3 | 1MB | 96 | 0.33
8 | -> CTE Scan on t(1) a | 1 | 1MB | 56 | 0.02
9 | -> Materialize | 3 | 16MB | 56 | 0.29
10 | -> Streaming(type: BROADCAST) | 3 | 2MB | 56 | 0.29
11 | -> CTE Scan on t(1) b | 1 | 1MB | 56 | 0.02
12 | -> Nested Loop (13,14) | 3 | 1MB | 96 | 0.33
13 | -> CTE Scan on t(1) a | 1 | 1MB | 56 | 0.02
14 | -> Materialize | 3 | 16MB | 56 | 0.29
15 | -> Streaming(type: BROADCAST) | 3 | 2MB | 56 | 0.29
16 | -> CTE Scan on t(1) b | 1 | 1MB | 56 | 0.02
17 | -> Nested Loop (18,19) | 3 | 1MB | 96 | 0.33
18 | -> CTE Scan on t(1) a | 1 | 1MB | 56 | 0.02
19 | -> Materialize | 3 | 16MB | 56 | 0.29
20 | -> Streaming(type: BROADCAST) | 3 | 2MB | 56 | 0.29
21 | -> CTE Scan on t(1) b | 1 | 1MB | 56 | 0.02
Predicate Information (identified by plan id)
----------------------------------------------------------------------------------------------
7 --Nested Loop (8,9)
Join Filter: ((a.rzsj >= b.rzsj) AND (a.rzsj <= b.ldsj) AND (a.rzmod = b.rzmod))
12 --Nested Loop (13,14)
Join Filter: ((a.rzsj >= b.rzsj) AND (a.rzsj <= b.ldsj) AND (a.rzmod = (b.rzmod + 1)))
17 --Nested Loop (18,19)
Join Filter: ((a.rzsj >= b.rzsj) AND (a.rzsj <= b.ldsj) AND (a.rzmod = (b.rzmod - 1)))
原创文章，作者：奋斗，如若转载，请注明出处：https://blog.ytso.com/tech/bigdata/317286.html