PostgreSQL 如何估計 JOIN 查詢中的行數，例如：
EXPLAIN
SELECT *
FROM R, S
WHERE (R.StartTime < S.EndTime) AND (S.StartTime < R.EndTime);
uj5u.com熱心網友回復：
假設所涉及的資料型別是timestamp with time time zone（但它并不重要，正如我們將看到的），可以通過以下方式找到連接選擇性估計函式：
SELECT oprjoin
FROM pg_operator
WHERE oprname = '<'
AND oprleft = 'timestamptz'::regtype
AND oprright = 'timestamptz'::regtype;
oprjoin
═════════════════
scalarltjoinsel
(1 row)
該函式定義在src/backend/utils/adt/selfuncs.c：
/*
* scalarltjoinsel - Join selectivity of "<" for scalars
*/
Datum
scalarltjoinsel(PG_FUNCTION_ARGS)
{
PG_RETURN_FLOAT8(DEFAULT_INEQ_SEL);
}
這被定義src/include/utils/selfuncs.h為
/* default selectivity estimate for inequalities such as "A < b" */
#define DEFAULT_INEQ_SEL 0.3333333333333333
所以，聽起來很簡單，PostgreSQL 會估計一個不等式連接條件會過濾掉三分之二的行。由于有兩個這樣的條件，選擇性相乘，PostgreSQL會估計結果的行數為
(#rows in R) * (#rows in S) / 9
到目前為止，PostgreSQL 還沒有任何交叉表統計資訊可以使這不那么粗糙。
uj5u.com熱心網友回復：
手冊中有一個章節準確地解決了您的問題：
- 行估計示例
除其他外，還解釋了 Laurenz 提供的內容。
但這還不是全部。我們還需要基礎表的行數（基數）。Postgres 使用estimate_rel_size()定義在src/backend/utils/adt/plancat.c：
/*
* estimate_rel_size - estimate # pages and # tuples in a table or index
*
* We also estimate the fraction of the pages that are marked all-visible in
* the visibility map, for use in estimation of index-only scans.
*
* If attr_widths isn't NULL, it points to the zero-index entry of the
* relation's attr_widths[] cache; we fill this in if we have need to compute
* the attribute widths for estimation purposes.
*/
void
estimate_rel_size(Relation rel, int32 *attr_widths,
BlockNumber *pages, double *tuples, double *allvisfrac)
...
這是重現計算的最小 SQL 查詢（忽略一些極端情況）：
SELECT (reltuples / relpages * (pg_relation_size(oid) / 8192))::bigint
FROM pg_class
WHERE oid = 'mytable'::regclass; -- your table here
更多細節：
- 在 PostgreSQL 中發現表的行數的快速方法
例子
CREATE TEMP TABLE r(id serial, start_time timestamptz, end_time timestamptz);
CREATE TEMP TABLE s(id serial, start_time timestamptz, end_time timestamptz);
INSERT INTO r(start_time, end_time)
SELECT now(), now() -- actual values don't matter for this particular case
FROM generate_series (1, 5000);
INSERT INTO s(start_time, end_time)
SELECT now(), now()
FROM generate_series (1, 10000);
VACUUM r, s; -- set reltuples & relpages in pg_class
-- add 2000 rows to S
INSERT INTO s(start_time, end_time)
SELECT now(), now()
FROM generate_series (1, 2000);
pg_class仍然有 5000 和 10000 個 reltuples，但我們知道在 R 和 S 中有 5000 和 12000 行。（因為這些是臨時表，它們沒有被 autovacuum 覆寫，所以數字永遠不會自動更新。）檢查：
SELECT relname, reltuples, relpages -- 5000 | 10000
FROM pg_class c
WHERE c.oid IN ('pg_temp.r'::regclass, 'pg_temp.s'::regclass);
SELECT count(*) FROM r; -- 5000
SELECT count(*) FROM s; -- 12000
查詢計劃：
EXPLAIN
SELECT *
FROM r, s
WHERE (r.start_time < s.end_time) AND (s.start_time < r.end_time);
'Nested Loop (cost=0.00..1053004.31 rows=6683889 width=40)'
' Join Filter: ((r.start_time < s.end_time) AND (s.start_time < r.end_time))'
' -> Seq Scan on s (cost=0.00..197.31 rows=12031 width=20)'
' -> Materialize (cost=0.00..107.00 rows=5000 width=20)'
' -> Seq Scan on r (cost=0.00..82.00 rows=5000 width=20)'
'JIT:'
' Functions: 6'
' Options: Inlining true, Optimization true, Expressions true, Deforming true'
Postgres estimates rows=12031 for table s. A pretty good estimate, the algorithm worked.
The estimate is more easily thrown off by deleting rows, as the physical size of the table doesn't shrink automatically. It's a good idea to VACUUM ANALYZE after a major DELETE. Or even VACUUM FULL ANALYZE. See:
- VACUUM returning disk space to operating system
Postgres expects rows=6683889, which matches our expectation (as per Laurenz' explanation):
SELECT 5000 * 12031 * 0.3333333333333333^2 -- 6683888.89
Better query
Your example query is just that: an example. But it happens to be a poor one, as the same can be achieved with range types and operators more efficiently. Specifically with tstzrange and &&:
Selectivity for &&?
SELECT oprjoin -- areajoinsel
FROM pg_operator
WHERE oprname = '&&'
AND oprleft = 'anyrange'::regtype
AND oprright = 'anyrange'::regtype;
The source code in `src/backend/utils/adt/geoselfuncs.c:
Datum
areajoinsel(PG_FUNCTION_ARGS)
{
PG_RETURN_FLOAT8(0.005);
}
Much more selective 0.005 << 0.333! And typically more realistic.
EXPLAIN
SELECT *
FROM r, s
WHERE tstzrange(r.start_time, r.end_time) && tstzrange(s.start_time, s.end_time);
Happens to be exactly equivalent, since tstzrange defaults to including the lower bound and excluding the upper bound. I get this query plan:
'Nested Loop (cost=0.00..1203391.81 rows=300775 width=40)'
' Join Filter: (tstzrange(r.start_time, r.end_time) && tstzrange(s.start_time, s.end_time))'
' -> Seq Scan on s (cost=0.00..197.31 rows=12031 width=20)'
' -> Materialize (cost=0.00..107.00 rows=5000 width=20)'
' -> Seq Scan on r (cost=0.00..82.00 rows=5000 width=20)'
'JIT:'
' Functions: 6'
' Options: Inlining true, Optimization true, Expressions true, Deforming true'
Our expectation:
SELECT 5000 * 12031 * 0.005 -- 300775.000
It's a Bingo!
And this query can be supported with an index efficiently, changing the game ...
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/317117.html