CREATE TABLE test (x numeric);
INSERT INTO test
SELECT random() * 10000
FROM generate_series(1, 10000000);
analyze VERBOSE test ;
vastbase=# analyze VERBOSE test ;
INFO: analyzing "public.test"(vastbase pid=65753)
INFO: ANALYZE INFO : "test": scanned 30000 of 67568 pages, containing 4439936 live rows and 0 dead rows; 30000 rows in sample, 9999920 estimated total rows(vastbase pid=65753)
ANALYZE
EXPLAIN SELECT * FROM test;
vastbase=# EXPLAIN SELECT * FROM test;
QUERY PLAN
----------------------------------------------------------------
Seq Scan on test (cost=0.00..167567.20 rows=9999920 width=11)
(1 row)
SELECT reltuples FROM pg_class WHERE relname = 'test';
vastbase=# SELECT reltuples FROM pg_class WHERE relname = 'test';
reltuples
-----------
9999920
SELECT reltuples,
current_setting('cpu_tuple_cost') AS cpu_tuple_cost,
reltuples * current_setting('cpu_tuple_cost')::real AS total
FROM pg_class WHERE relname='test';
vastbase=# SELECT reltuples,
vastbase-# current_setting('cpu_tuple_cost') AS cpu_tuple_cost,
vastbase-# reltuples * current_setting('cpu_tuple_cost')::real AS total
vastbase-# FROM pg_class WHERE relname='test';
reltuples | cpu_tuple_cost | total
-----------+----------------+------------------
9999920 | 0.01 | 99999.1977648437
(1 row)
这里测试有不一致，应该有优化
EXPLAIN SELECT count(*) FROM test;
vastbase=# EXPLAIN SELECT count(*) FROM test;
QUERY PLAN
---------------------------------------------------------------------
Aggregate (cost=192567.00..192567.01 rows=1 width=8)
-> Seq Scan on test (cost=0.00..167567.20 rows=9999920 width=0)
(2 rows)
SELECT
reltuples,
current_setting('cpu_operator_cost') AS cpu_operator_cost,
round((
reltuples * current_setting('cpu_operator_cost')::real
)::numeric, 2) AS cpu_cost
FROM pg_class WHERE relname='test';
reltuples | cpu_operator_cost | cpu_cost
-----------+-------------------+----------
9999920 | 0.0025 | 24999.80
(1 row)
WITH t(cpu_cost) AS (
SELECT round((
reltuples * current_setting('cpu_operator_cost')::real
)::numeric, 2)
FROM pg_class WHERE relname = 'test'
)
SELECT 21.39 + t.cpu_cost AS startup_cost,
round((
21.39 + t.cpu_cost +
1 * current_setting('cpu_tuple_cost')::real
)::numeric, 2) AS total_cost
FROM t;
startup_cost | total_cost
--------------+------------
25021.19 | 25021.20
(1 row)