如果最近在 postgresql 12 中读取了表，如何查看？
有没有一种方法可以在一张巨大的桌子上的 postgres 12 中执行 ALTER COLUMN 而无需等待一生？
我尝试将字段从 bigint 转换为 smallint ：
ALTER TABLE huge ALTER COLUMN result_code TYPE SMALLINT;
需要8个小时，有没有更聪明的方法？该表具有序列、键和外键
我想压缩一个 postgresql 11 db，我的想法是将大量 bigint 转换为 int 数据类型，当且仅当考虑到内部数据时 int 数据类型的限制存在合理的滞后。
那么，你能帮我找到一个脚本来找到所有可以在 postgresql 中转换为 int 字段的 bigint 吗？
查询：
SELECT Object1.Column1, Object2.Column2 AS Column3, Object2.Column4 AS Column5,
Object3.Column6, Object3.Column7,Object1.Column8, Object1.Column9,
Object1.Column10, Object1.Column11, Object1.Column12, Object1.Column13,
Object1.Column14, Object1.Column15 as Column15, Object1.Column16,
Object4.Column4 AS Column17, Object4.Column2 AS Column18, Object1.Column19,
Object1.Column20, Object1.Column21, Object1.Column22, Object1.Column23,
Object1.Column24, Object1.Column25, Object1.Column26, Object5.Column4,
Object1.Column27, Object1.Column28, Object1.Column29, Object3.Column30,
Object3.Column1 as Column31, Object3.Column32 as Column33, Object1.Column34
as Column34, ? AS Column35 , Object3.Column36 as Column37
FROM Object6 AS Object1
INNER JOIN Object7 AS Object3 ON Object1.Column38 = Object3.Column1
INNER JOIN Object8 AS Object2 ON Object3.Column30 = Object2.Column1
LEFT JOIN Object9 AS Object4 ON Object1.Column16 = Object4.Column2
LEFT JOIN Object10 AS Object5 ON Object1.Column9 = Object5.Column2
WHERE Object2.Column1 <> ? AND Object1.Column8 = ?
AND ( coalesce(Column16,?)= ? )
AND EXISTS (
SELECT ?
FROM Object11
WHERE Column39 = ?
AND Column30 = Object3.Column30)
ORDER BY Column7 desc
OFFSET ? ROWS FETCH FIRST ? ROWS ONLY
我知道我应该为此添加一个索引：
Database1.Schema1.Object7.Column30, Database1.Schema1.Object7.Column36, Database1.Schema1.Object7.Column6, Database1.Schema1.Object7.Column32
但是其中一列是 varchar 4000，由于该字段的尺寸很大，因此无法创建它。
我注意到仅当返回的行少于获取的第一个数字时才需要 25 秒
我在启用了内存中 OLTP 功能的 sql server 2017 标准版上有一个数据库，我必须将它移动到另一个磁盘上，这是最好的方法吗？有什么可能的问题吗？
我可以设法摆脱这个 OLTP 功能吗？有人说要删除并重新创建数据库？但是如何......保存数据？
我有这个脚本：
SELECT type_desc, name, physical_name from sys.database_files
USE master
EXEC sp_detach_db mydb
CREATE DATABASE mydb ON
PRIMARY ( NAME = mydbdata,
FILENAME = 'Y:\Database\SQLServer\DATA\mydbdata.mdf'),
( NAME = mydbdata2,
FILENAME = 'Y:\Database\SQLServer\DATA\mydbdata2.mdf'),
FILEGROUP FileStreamGroup1 CONTAINS FILESTREAM( NAME = mydbdata_mod1,
FILENAME = 'Y:\Database\SQLServer\DATA\mydbdata_mod1')
LOG ON ( NAME = mydbdata_log,
FILENAME = 'Z:\Database\SQLServer\DATA\mydbdata_log.ldf')
, ( NAME = mydbdata_log2,
FILENAME = 'Z:\Database\SQLServer\DATA\mydbdata_log2.ldf')
FOR ATTACH
GO
更新：这个dba.stackexchange.com/questions/52007/… 回答了如何移动的问题，没关系，但是有很多方法……尽管我对所有方法都有信心。我担心这全都与 OLTP 功能和 OLTP 文件有关
有没有办法（干净或不干净）消除生产数据库上的文件流数据文件？
我在空文件流上强制执行垃圾回收：
sp_filestream_force_garbage_collection @dbname = N'DB' , @filename = N'db_mod';
我试过了，内存选项和文件流实际上没有在数据库上启用
SELECT name, description FROM sys.dm_os_loaded_modules WHERE description = 'XTP Native DLL'
这个脚本给出了数据库中的文件：
选择 sdf.name AS [FileName], size/128 AS [Size_in_MB], fg.name AS [File_Group_Name] FROM sys.database_files sdf INNER JOIN sys.filegroups fg ON sdf.data_space_id=fg.data_space_id
文件名 Size_in_MB 文件组名称
db 7282 主 db_mod 1528 db_mod
我可以设法消除这个阻止我使用数据库镜像的空文件和文件组吗？
我当然试过
-- Remove the File
改变数据库 db 删除文件 db_mod1 ; -- 删除文件组 ALTER DATABASE db REMOVE FILEGROUP db_mod;
我收到错误：
消息 41802，级别 16，状态 1，第 1 行无法删除最后一个内存优化容器“db_mod1”。
在具有完整模型备份的 sql server 数据库中，每 15 分钟备份一次 tran 日志，但是，日志文件仍然很大。db 和 tran 日志（每 15 分钟）的备份（每天一次）是有规律的，日志空间几乎是空的……那么缩小一点好吗？
为什么 Postgresql 10 上的声明性分区比非分区表慢？
我根据日期标准对 3 个表进行了分区，但发现选择查询速度明显变慢。
你能弄清楚为什么吗？
这是我的分区表
CREATE TABLE xxx.documentstate (
iddoc int8 NOT NULL DEFAULT nextval('xxx.document_state_iddoc_seq'::regclass),
documento uuid NULL,
idstate int8 NULL,
iduser int4 NULL,
idprocessor int8 NULL,
datecreate timestamp NULL,
usercreate varchar NULL,
datemodifie timestamp NULL,
usermodifie varchar NULL,
datestate timestamp NULL,
iddocuments int8 NULL,
requestc int8 NULL,
details varchar NULL
) PARTITION BY RANGE (datecreate)
WITH (
OIDS = FALSE,
toast.autovacuum_enabled = TRUE
)
TABLESPACE pg_default;
ALTER TABLE xxx.documentstate
OWNER to postgres;
CREATE INDEX idx_documentstatedatecreated201601 ON xxx.documentstate201601 USING btree (date_created);
CREATE INDEX idx_documentstate_id_docu201601 ON xxx.documentstate201601 USING btree (iddoc);
CREATE INDEX ind_documentstate_id_documents201601 ON xxx.documentstate201601 USING btree (iddocuments);
CREATE INDEX ind_documentstate_id_state201601 ON xxx.documentstate201601 USING btree (idstate);
CREATE INDEX ind_documentstate_id_state_brin201601 ON xxx.documentstate201601 USING brin (idstate);
explain analyze
SELECT count(d.iddoc) , d.idcompanyservice
FROM xxx.document d
inner join xxx.documentstate ds on ds.iddoc = d.iddoc
where ds.idstate = 20010
and d.idcompanyservice is not null
group by idcompanyservice
Finalize GroupAggregate (cost=935420631.26..935420636.26 rows=200 width=16) (actual time=110535.775..110535.784 rows=29 loops=1)
Group Key: d.id_companyservice
-> Sort (cost=935420631.26..935420632.26 rows=400 width=12) (actual time=110535.773..110535.773 rows=62 loops=1)
Sort Key: d.id_companyservice
Sort Method: quicksort Memory: 27kB
-> Gather (cost=935420571.97..935420613.97 rows=400 width=12) (actual time=110535.745..110535.757 rows=62 loops=1)
Workers Planned: 2
Workers Launched: 2
-> Partial HashAggregate (cost=935419571.97..935419573.97 rows=200 width=12) (actual time=109882.667..109882.669 rows=21 loops=3)
Group Key: d.id_companyservice
-> Merge Join (cost=2332304.32..702165704.23 rows=46650773548 width=12) (actual time=106889.051..109875.658 rows=58283 loops=3)
Merge Cond: (d.iddoc = ds.iddoc)
-> Sort (cost=1787443.66..1804554.64 rows=6844395 width=20) (actual time=79562.415..81991.339 rows=5424772 loops=3)
Sort Key: d.iddoc
Sort Method: external merge Disk: 190856kB
-> Append (cost=0.00..910122.67 rows=6844395 width=20) (actual time=2008.883..76660.707 rows=5474273 loops=3)
-> Parallel Seq Scan on document201601 d (cost=0.00..10.59 rows=59 width=20) (actual time=0.001..0.001 rows=0 loops=3)
Filter: (id_companyservice IS NOT NULL)
-> Parallel Seq Scan on document201602 d_1 (cost=0.00..10.59 rows=59 width=20) (actual time=0.000..0.000 rows=0 loops=3)
Filter: (id_companyservice IS NOT NULL)
-> Parallel Seq Scan on document201603 d_2 (cost=0.00..10.59 rows=59 width=20) (actual time=0.000..0.000 rows=0 loops=3)
Filter: (id_companyservice IS NOT NULL)
-> Parallel Seq Scan on document201604 d_3 (cost=0.00..10.59 rows=59 width=20) (actual time=0.000..0.000 rows=0 loops=3)
Filter: (id_companyservice IS NOT NULL)
-> Parallel Seq Scan on document201605 d_4 (cost=0.00..10.59 rows=59 width=20) (actual time=0.000..0.000 rows=0 loops=3)
Filter: (id_companyservice IS NOT NULL)
-> Parallel Seq Scan on document201606 d_5 (cost=0.00..3431.96 rows=1 width=20) (actual time=36.862..36.862 rows=0 loops=3)
Filter: (id_companyservice IS NOT NULL)
Rows Removed by Filter: 21985
-> Parallel Seq Scan on document201607 d_6 (cost=0.00..16338.53 rows=1 width=20) (actual time=718.271..718.271 rows=0 loops=3)
Filter: (id_companyservice IS NOT NULL)
Rows Removed by Filter: 113242
-> Parallel Seq Scan on document201608 d_7 (cost=0.00..8168.01 rows=1 width=20) (actual time=306.724..306.724 rows=0 loops=3)
Filter: (id_companyservice IS NOT NULL)
Rows Removed by Filter: 55361
-> Parallel Seq Scan on document201609 d_8 (cost=0.00..5539.07 rows=1 width=20) (actual time=379.428..379.428 rows=0 loops=3)
Filter: (id_companyservice IS NOT NULL)
Rows Removed by Filter: 37206
-> Parallel Seq Scan on document201610 d_9 (cost=0.00..15122.91 rows=1 width=20) (actual time=565.746..565.746 rows=0 loops=3)
Filter: (id_companyservice IS NOT NULL)
Rows Removed by Filter: 106153
-> Parallel Seq Scan on document201611 d_10 (cost=0.00..33221.16 rows=65683 width=20) (actual time=1.846..2412.654 rows=52298 loops=3)
Filter: (id_companyservice IS NOT NULL)
Rows Removed by Filter: 185074
-> Parallel Seq Scan on document201612 d_11 (cost=0.00..34286.35 rows=278135 width=20) (actual time=1.371..1684.774 rows=222508 loops=3)
Filter: (id_companyservice IS NOT NULL)
-> Parallel Seq Scan on document201701 d_12 (cost=0.00..31089.63 rows=268162 width=20) (actual time=1.272..3087.268 rows=214530 loops=3)
Filter: (id_companyservice IS NOT NULL)
-> Parallel Seq Scan on document201702 d_13 (cost=0.00..30850.53 rows=267453 width=20) (actual time=1.480..5823.452 rows=213963 loops=3)
Filter: (id_companyservice IS NOT NULL)
-> Parallel Seq Scan on document201703 d_14 (cost=0.00..59920.47 rows=494547 width=20) (actual time=1.682..3240.924 rows=395637 loops=3)
Filter: (id_companyservice IS NOT NULL)
-> Parallel Seq Scan on document201704 d_15 (cost=0.00..46090.52 rows=351252 width=20) (actual time=1.428..2715.674 rows=281001 loops=3)
Filter: (id_companyservice IS NOT NULL)
-> Parallel Seq Scan on document201705 d_16 (cost=0.00..39761.00 rows=295000 width=20) (actual time=1.340..5237.661 rows=236000 loops=3)
Filter: (id_companyservice IS NOT NULL)
-> Parallel Seq Scan on document201706 d_17 (cost=0.00..45077.68 rows=320868 width=20) (actual time=1.558..2865.148 rows=256694 loops=3)
Filter: (id_companyservice IS NOT NULL)
-> Parallel Seq Scan on document201707 d_18 (cost=0.00..257229.40 rows=2211640 width=20) (actual time=1.349..24593.124 rows=1769312 loops=3)
Filter: (id_companyservice IS NOT NULL)
-> Parallel Seq Scan on document201708 d_19 (cost=0.00..135926.79 rows=1126079 width=20) (actual time=0.862..11922.558 rows=900863 loops=3)
Filter: (id_companyservice IS NOT NULL)
-> Parallel Seq Scan on document201709 d_20 (cost=0.00..81090.89 rows=619589 width=20) (actual time=1.144..6460.049 rows=495671 loops=3)
Filter: (id_companyservice IS NOT NULL)
-> Parallel Seq Scan on document201710 d_21 (cost=0.00..66755.43 rows=544743 width=20) (actual time=1.234..4409.249 rows=435794 loops=3)
Filter: (id_companyservice IS NOT NULL)
-> Parallel Seq Scan on document201711 d_22 (cost=0.00..10.59 rows=59 width=20) (actual time=0.001..0.001 rows=0 loops=3)
Filter: (id_companyservice IS NOT NULL)
-> Parallel Seq Scan on document201712 d_23 (cost=0.00..10.59 rows=59 width=20) (actual time=0.001..0.001 rows=0 loops=3)
Filter: (id_companyservice IS NOT NULL)
-> Parallel Seq Scan on document201801 d_24 (cost=0.00..10.59 rows=59 width=20) (actual time=0.001..0.001 rows=0 loops=3)
Filter: (id_companyservice IS NOT NULL)
-> Parallel Seq Scan on document201802 d_25 (cost=0.00..10.59 rows=59 width=20) (actual time=0.001..0.001 rows=0 loops=3)
Filter: (id_companyservice IS NOT NULL)
-> Parallel Seq Scan on document201803 d_26 (cost=0.00..10.59 rows=59 width=20) (actual time=0.000..0.000 rows=0 loops=3)
Filter: (id_companyservice IS NOT NULL)
-> Parallel Seq Scan on document201804 d_27 (cost=0.00..10.59 rows=59 width=20) (actual time=0.001..0.001 rows=0 loops=3)
Filter: (id_companyservice IS NOT NULL)
-> Parallel Seq Scan on document201805 d_28 (cost=0.00..10.59 rows=59 width=20) (actual time=0.001..0.001 rows=0 loops=3)
Filter: (id_companyservice IS NOT NULL)
-> Parallel Seq Scan on document201806 d_29 (cost=0.00..10.59 rows=59 width=20) (actual time=0.001..0.001 rows=0 loops=3)
Filter: (id_companyservice IS NOT NULL)
-> Parallel Seq Scan on document201807 d_30 (cost=0.00..10.59 rows=59 width=20) (actual time=0.001..0.001 rows=0 loops=3)
Filter: (id_companyservice IS NOT NULL)
-> Parallel Seq Scan on document201808 d_31 (cost=0.00..10.59 rows=59 width=20) (actual time=0.001..0.001 rows=0 loops=3)
Filter: (id_companyservice IS NOT NULL)
-> Parallel Seq Scan on document201809 d_32 (cost=0.00..10.59 rows=59 width=20) (actual time=0.000..0.000 rows=0 loops=3)
Filter: (id_companyservice IS NOT NULL)
-> Parallel Seq Scan on document201810 d_33 (cost=0.00..10.59 rows=59 width=20) (actual time=0.001..0.001 rows=0 loops=3)
Filter: (id_companyservice IS NOT NULL)
-> Parallel Seq Scan on document201811 d_34 (cost=0.00..10.59 rows=59 width=20) (actual time=0.001..0.001 rows=0 loops=3)
Filter: (id_companyservice IS NOT NULL)
-> Parallel Seq Scan on document201812 d_35 (cost=0.00..10.59 rows=59 width=20) (actual time=0.001..0.001 rows=0 loops=3)
Filter: (id_companyservice IS NOT NULL)
-> Parallel Seq Scan on document201511 d_36 (cost=0.00..10.59 rows=59 width=20) (actual time=0.002..0.002 rows=0 loops=3)
Filter: (id_companyservice IS NOT NULL)
-> Parallel Seq Scan on document201512 d_37 (cost=0.00..10.59 rows=59 width=20) (actual time=0.246..0.247 rows=1 loops=3)
Filter: (id_companyservice IS NOT NULL)
-> Sort (cost=544860.66..548268.80 rows=1363255 width=8) (actual time=27304.766..27488.071 rows=3089184 loops=3)
Sort Key: ds.iddoc
Sort Method: external sort Disk: 66456kB
-> Append (cost=2.16..405954.36 rows=1363255 width=8) (actual time=47.104..26133.196 rows=3089184 loops=3)
-> Bitmap Heap Scan on documentstate201601 ds (cost=2.16..5.29 rows=2 width=8) (actual time=0.107..0.107 rows=0 loops=3)
Recheck Cond: (idstate = 20010)
-> Bitmap Index Scan on ind_documentstateidstate201601 (cost=0.00..2.16 rows=2 width=0) (actual time=0.104..0.104 rows=0 loops=3)
Index Cond: (idstate = 20010)
-> Bitmap Heap Scan on documentstate201602 ds_1 (cost=2.16..5.29 rows=2 width=8) (actual time=0.006..0.006 rows=0 loops=3)
Recheck Cond: (idstate = 20010)
-> Bitmap Index Scan on ind_documentstateidstate201602 (cost=0.00..2.16 rows=2 width=0) (actual time=0.005..0.005 rows=0 loops=3)
Index Cond: (idstate = 20010)
-> Bitmap Heap Scan on documentstate201603 ds_2 (cost=2.16..5.29 rows=2 width=8) (actual time=0.005..0.005 rows=0 loops=3)
Recheck Cond: (idstate = 20010)
-> Bitmap Index Scan on ind_documentstateidstate201603 (cost=0.00..2.16 rows=2 width=0) (actual time=0.005..0.005 rows=0 loops=3)
Index Cond: (idstate = 20010)
-> Bitmap Heap Scan on documentstate201604 ds_3 (cost=2.16..5.29 rows=2 width=8) (actual time=0.005..0.005 rows=0 loops=3)
Recheck Cond: (idstate = 20010)
-> Bitmap Index Scan on ind_documentstateidstate201604 (cost=0.00..2.16 rows=2 width=0) (actual time=0.005..0.005 rows=0 loops=3)
Index Cond: (idstate = 20010)
-> Bitmap Heap Scan on documentstate201605 ds_4 (cost=2.16..5.29 rows=2 width=8) (actual time=0.005..0.005 rows=0 loops=3)
Recheck Cond: (idstate = 20010)
-> Bitmap Index Scan on ind_documentstateidstate201605 (cost=0.00..2.16 rows=2 width=0) (actual time=0.005..0.005 rows=0 loops=3)
Index Cond: (idstate = 20010)
-> Index Scan using ind_documentstateidstate201606 on documentstate201606 ds_5 (cost=0.42..2.44 rows=1 width=8) (actual time=6.118..6.118 rows=0 loops=3)
Index Cond: (idstate = 20010)
-> Index Scan using ind_documentstateidstate201607 on documentstate201607 ds_6 (cost=0.42..4.44 rows=1 width=8) (actual time=12.798..12.798 rows=0 loops=3)
Index Cond: (idstate = 20010)
-> Index Scan using ind_documentstateidstate201608 on documentstate201608 ds_7 (cost=0.42..3.92 rows=1 width=8) (actual time=8.572..8.572 rows=0 loops=3)
Index Cond: (idstate = 20010)
-> Index Scan using ind_documentstateidstate201609 on documentstate201609 ds_8 (cost=0.42..4.44 rows=1 width=8) (actual time=3.371..3.371 rows=0 loops=3)
Index Cond: (idstate = 20010)
-> Index Scan using ind_documentstateidstate201610 on documentstate201610 ds_9 (cost=0.42..98.90 rows=65 width=8) (actual time=16.110..16.139 rows=64 loops=3)
Index Cond: (idstate = 20010)
-> Index Scan using ind_documentstateidstate201611 on documentstate201611 ds_10 (cost=0.43..3.91 rows=1 width=8) (actual time=13.238..13.238 rows=0 loops=3)
Index Cond: (idstate = 20010)
-> Bitmap Heap Scan on documentstate201612 ds_11 (cost=30.64..24312.21 rows=96006 width=8) (actual time=380.697..1518.272 rows=96697 loops=3)
Recheck Cond: (idstate = 20010)
Rows Removed by Index Recheck: 1500409
Heap Blocks: lossy=21837
-> Bitmap Index Scan on ind_documentstateidstate_brin201612 (cost=0.00..6.63 rows=185326 width=0) (actual time=4.523..4.523 rows=218880 loops=3)
Index Cond: (idstate = 20010)
-> Index Scan using ind_documentstateidstate201701 on documentstate201701 ds_12 (cost=0.43..3.96 rows=1 width=8) (actual time=1.105..1.105 rows=0 loops=3)
Index Cond: (idstate = 20010)
-> Index Scan using ind_documentstateidstate201702 on documentstate201702 ds_13 (cost=0.43..18154.42 rows=17039 width=8) (actual time=1.027..14.226 rows=17160 loops=3)
Index Cond: (idstate = 20010)
-> Bitmap Heap Scan on documentstate201703 ds_14 (cost=60.31..46576.28 rows=208351 width=8) (actual time=1.375..2589.299 rows=208302 loops=3)
Recheck Cond: (idstate = 20010)
Rows Removed by Index Recheck: 2559383
Heap Blocks: lossy=38564
-> Bitmap Index Scan on ind_documentstateidstate_brin201703 (cost=0.00..8.22 rows=636158 width=0) (actual time=1.327..1.327 rows=386560 loops=3)
Index Cond: (idstate = 20010)
-> Bitmap Heap Scan on documentstate201704 ds_15 (cost=90.17..52815.64 rows=319212 width=8) (actual time=22.487..2495.299 rows=319482 loops=3)
Recheck Cond: (idstate = 20010)
Rows Removed by Index Recheck: 2302167
Heap Blocks: lossy=37519
-> Bitmap Index Scan on ind_documentstateidstate_brin201704 (cost=0.00..10.37 rows=1216518 width=0) (actual time=1.396..1.396 rows=376320 loops=3)
Index Cond: (idstate = 20010)
-> Bitmap Heap Scan on documentstate201705 ds_16 (cost=80.81..49305.67 rows=279897 width=8) (actual time=1.005..1707.179 rows=280566 loops=3)
Recheck Cond: (idstate = 20010)
Rows Removed by Index Recheck: 1983543
Heap Blocks: lossy=32398
-> Bitmap Index Scan on ind_documentstateidstate_brin201705 (cost=0.00..10.83 rows=1346149 width=0) (actual time=0.983..0.983 rows=325120 loops=3)
Index Cond: (idstate = 20010)
-> Bitmap Heap Scan on documentstate201706 ds_17 (cost=104.96..66914.41 rows=362916 width=8) (actual time=1.758..2557.485 rows=362439 loops=3)
Recheck Cond: (idstate = 20010)
Rows Removed by Index Recheck: 2267194
Heap Blocks: lossy=39275
-> Bitmap Index Scan on ind_documentstateidstate_brin201706 (cost=0.00..14.23 rows=2202756 width=0) (actual time=1.737..1.737 rows=392960 loops=3)
Index Cond: (idstate = 20010)
-> Index Scan using ind_documentstateidstate201707 on documentstate201707 ds_18 (cost=0.43..64378.77 rows=34762 width=8) (actual time=1.217..3838.822 rows=464172 loops=3)
Index Cond: (idstate = 20010)
-> Index Scan using ind_documentstateidstate201708 on documentstate201708 ds_19 (cost=0.43..35257.56 rows=19036 width=8) (actual time=0.461..2812.105 rows=409098 loops=3)
Index Cond: (idstate = 20010)
-> Index Scan using ind_documentstateidstate201709 on documentstate201709 ds_20 (cost=0.43..29246.77 rows=15791 width=8) (actual time=1.408..6518.468 rows=642150 loops=3)
Index Cond: (idstate = 20010)
-> Index Scan using ind_documentstateidstate201710 on documentstate201710 ds_21 (cost=0.43..18785.91 rows=10142 width=8) (actual time=1.223..1904.309 rows=289054 loops=3)
Index Cond: (idstate = 20010)
-> Bitmap Heap Scan on documentstate201711 ds_22 (cost=2.16..5.29 rows=2 width=8) (actual time=0.823..0.823 rows=0 loops=3)
Recheck Cond: (idstate = 20010)
-> Bitmap Index Scan on ind_documentstateidstate201711 (cost=0.00..2.16 rows=2 width=0) (actual time=0.814..0.814 rows=0 loops=3)
Index Cond: (idstate = 20010)
-> Bitmap Heap Scan on documentstate201712 ds_23 (cost=2.16..5.29 rows=2 width=8) (actual time=0.547..0.547 rows=0 loops=3)
Recheck Cond: (idstate = 20010)
-> Bitmap Index Scan on ind_documentstateidstate201712 (cost=0.00..2.16 rows=2 width=0) (actual time=0.546..0.546 rows=0 loops=3)
Index Cond: (idstate = 20010)
-> Bitmap Heap Scan on documentstate201801 ds_24 (cost=2.16..5.29 rows=2 width=8) (actual time=0.684..0.684 rows=0 loops=3)
Recheck Cond: (idstate = 20010)
-> Bitmap Index Scan on ind_documentstateidstate201801 (cost=0.00..2.16 rows=2 width=0) (actual time=0.683..0.683 rows=0 loops=3)
Index Cond: (idstate = 20010)
-> Bitmap Heap Scan on documentstate201802 ds_25 (cost=2.16..5.29 rows=2 width=8) (actual time=0.638..0.638 rows=0 loops=3)
Recheck Cond: (idstate = 20010)
-> Bitmap Index Scan on ind_documentstateidstate201802 (cost=0.00..2.16 rows=2 width=0) (actual time=0.636..0.636 rows=0 loops=3)
Index Cond: (idstate = 20010)
-> Bitmap Heap Scan on documentstate201803 ds_26 (cost=2.16..5.29 rows=2 width=8) (actual time=0.571..0.571 rows=0 loops=3)
Recheck Cond: (idstate = 20010)
-> Bitmap Index Scan on ind_documentstateidstate201803 (cost=0.00..2.16 rows=2 width=0) (actual time=0.564..0.564 rows=0 loops=3)
Index Cond: (idstate = 20010)
-> Bitmap Heap Scan on documentstate201804 ds_27 (cost=2.16..5.29 rows=2 width=8) (actual time=0.496..0.496 rows=0 loops=3)
Recheck Cond: (idstate = 20010)
-> Bitmap Index Scan on ind_documentstateidstate201804 (cost=0.00..2.16 rows=2 width=0) (actual time=0.494..0.494 rows=0 loops=3)
Index Cond: (idstate = 20010)
-> Bitmap Heap Scan on documentstate201805 ds_28 (cost=2.16..5.29 rows=2 width=8) (actual time=0.481..0.481 rows=0 loops=3)
Recheck Cond: (idstate = 20010)
-> Bitmap Index Scan on ind_documentstateidstate201805 (cost=0.00..2.16 rows=2 width=0) (actual time=0.480..0.480 rows=0 loops=3)
Index Cond: (idstate = 20010)
-> Bitmap Heap Scan on documentstate201806 ds_29 (cost=2.16..5.29 rows=2 width=8) (actual time=0.553..0.553 rows=0 loops=3)
Recheck Cond: (idstate = 20010)
-> Bitmap Index Scan on ind_documentstateidstate201806 (cost=0.00..2.16 rows=2 width=0) (actual time=0.552..0.552 rows=0 loops=3)
Index Cond: (idstate = 20010)
-> Bitmap Heap Scan on documentstate201807 ds_30 (cost=2.16..5.29 rows=2 width=8) (actual time=0.527..0.527 rows=0 loops=3)
Recheck Cond: (idstate = 20010)
-> Bitmap Index Scan on ind_documentstateidstate201807 (cost=0.00..2.16 rows=2 width=0) (actual time=0.525..0.525 rows=0 loops=3)
Index Cond: (idstate = 20010)
-> Bitmap Heap Scan on documentstate201808 ds_31 (cost=2.16..5.29 rows=2 width=8) (actual time=0.589..0.589 rows=0 loops=3)
Recheck Cond: (idstate = 20010)
-> Bitmap Index Scan on ind_documentstateidstate201808 (cost=0.00..2.16 rows=2 width=0) (actual time=0.588..0.588 rows=0 loops=3)
Index Cond: (idstate = 20010)
-> Bitmap Heap Scan on documentstate201809 ds_32 (cost=2.16..5.29 rows=2 width=8) (actual time=0.511..0.511 rows=0 loops=3)
Recheck Cond: (idstate = 20010)
-> Bitmap Index Scan on ind_documentstateidstate201809 (cost=0.00..2.16 rows=2 width=0) (actual time=0.510..0.510 rows=0 loops=3)
Index Cond: (idstate = 20010)
Planning time: 27.433 ms
Execution time: 110570.959 ms
我正在尝试在我的 postgresql 9.5 数据库中设置 autovacuum，但表统计信息没有显示这方面的证据。
在 postgresql.conf 中，autovacuum 已经启用：
autovacuum = on
在设置中，另外，我有 ( select * from pg_settings where category like 'Autovacuum')
autovacuum on
autovacuum_analyze_scale_factor 0.1
autovacuum_analyze_threshold 50
autovacuum_freeze_max_age 200000000
autovacuum_max_workers 3
autovacuum_multixact_freeze_max_age 400000000
autovacuum_naptime 60
autovacuum_vacuum_cost_delay 20
autovacuum_vacuum_cost_limit -1
autovacuum_vacuum_scale_factor 0.2
autovacuum_vacuum_threshold 50
如果我执行这个查询：
SELECT schemaname, relname,
last_vacuum ,
last_autovacuum ,
last_analyze ,
last_autoanalyze
FROM pg_stat_all_tables
where schemaname = 'cdi'
我得到这个结果：
relname last_vacuum last_autovacuum last_analyze last_autoanalyze aa 11/11/2017 05:56 04/11/2017 03:24 13/11/2017 23:25 bb 09/10/2017 15:01 04/11/2017 03:55 09/10/2017 15:01 抄送 13/09/2017 13:23 04/11/2017 03:55 11/11/2017 04:12 dd 14/09/2017 10:27 04/11/2017 03:55 12/11/2017 17:09 ee 14/09/2017 10:32 04/11/2017 03:00
ff 11/10/2017 09:20 04/11/2017 03:48 13/11/2017 22:19 gg 04/11/2017 03:55
hh 04/11/2017 03:55
二 04/11/2017 03:16 11/11/2017 06:51 ll 04/11/2017 03:16
mm 04/11/2017 03:55
nn 04/11/2017 03:55
oo 04/11/2017 03:55
pp 04/11/2017 03:00
在我看来没有有效的自动真空
有人可以告诉我一种使 autovacuum 发生的方法吗？
这是我的桌子：
顺序扫描 156 顺序元组读取 5448762930 索引扫描 802121339750 获取的索引元组 800736733206 插入的元组 21907385 元组更新 6190434 删除的元组 190337 元组热更新 3958397 活元组 59641911 死元组 703334 堆块读取 13685226418 堆块命中 786803454925 索引块读取 5104418623 索引块命中 3208275818736 Toast 块读取 0 吐司块命中 0 Toast 索引块读取 0 Toast 索引块命中 0 上次真空 2017-10-11 09:20:22.358935+02 最后一次自动清理 最后分析 2017-11-04 03:48:49.753917+01 最后自动分析 2017-11-13 22:19:08.47618+01 真空计数器1 自动真空计数器 0 分析计数器 10 自动分析计数器 3 表大小 6778 MB Toast 表大小 8192 字节 索引大小 3969 MB