performance - 分区 : views versus tables with inheritance - what's the point?

Question

我的基本问题是，具有继承性的表的父表是否与 View 相同/执行方式相似，如果是这样(看起来是这样)，为什么不使用 View 。

为了对此进行调查，我决定尝试评估单个表、基于月度表的 View 以及具有继承性的月度表之间的性能差异。这会变得很长，所以请随意提出关于在 stackoverflow 之外附加代码的建议，如果这是首选，或者我可以通过其他方式简化它。我也会描述我在做什么，因此您不一定需要运行代码。

我想要 2012 年每一分钟有 50 个值的 200 个实体。首先，我制作了我的大表“public.test_wide_no_inheritance”和每个较小的月度表“public.test_wide_inheritance_YYYYMM”，并像前者一样继承到一个空表“public.test_wide_inheritance”，最后是基于较小表的 View ，“test_wide_inheritance_2012”:

create table public.test_wide_no_inheritance
as
select *
from(
SELECT entity, localt, 
random()as val01,random()as val02,random()as val03,random()as val04,random()as val05,random()as val06,random()as val07,random()as val08,random()as val09,random()as val10,
random()as val11,random()as val12,random()as val13,random()as val14,random()as val15,random()as val16,random()as val17,random()as val18,random()as val19,random()as val20,
random()as val21,random()as val22,random()as val23,random()as val24,random()as val25,random()as val26,random()as val27,random()as val28,random()as val29,random()as val30,
random()as val31,random()as val32,random()as val33,random()as val34,random()as val35,random()as val36,random()as val37,random()as val38,random()as val39,random()as val40,
random()as val41,random()as val42,random()as val43,random()as val44,random()as val45,random()as val46,random()as val47,random()as val48,random()as val49,random()as val50
FROM generate_series('2012-01-01'::timestamp, '2012-12-31'::timestamp, interval '1 minutes') as localt
join 
(select *
FROM generate_series(1, 200, 1) as entity) as entity
on 1=1) as data;

CREATE INDEX ix_public_test_wide_no_inheritance_entity
  ON public.test_wide_no_inheritance (entity);

CREATE INDEX ix_public_test_wide_no_inheritance_localt
  ON public.test_wide_no_inheritance (localt);

create table public.test_wide_inheritance (like public.test_wide); 

CREATE TABLE public.test_wide_inheritance_201201 (
    CHECK ( localt >= DATE '2012-01-01' AND localt < DATE '2012-02-01' )
) INHERITS (public.test_wide_inheritance);
CREATE TABLE public.test_wide_inheritance_201202 (
    CHECK ( localt >= DATE '2012-02-01' AND localt < DATE '2012-03-01' )
) INHERITS (public.test_wide_inheritance);
CREATE TABLE public.test_wide_inheritance_201203 (
    CHECK ( localt >= DATE '2012-03-01' AND localt < DATE '2012-04-01' )
) INHERITS (public.test_wide_inheritance);
CREATE TABLE public.test_wide_inheritance_201204 (
    CHECK ( localt >= DATE '2012-04-01' AND localt < DATE '2012-05-01' )
) INHERITS (public.test_wide_inheritance);
CREATE TABLE public.test_wide_inheritance_201205 (
    CHECK ( localt >= DATE '2012-05-01' AND localt < DATE '2012-06-01' )
) INHERITS (public.test_wide_inheritance);
CREATE TABLE public.test_wide_inheritance_201206 (
    CHECK ( localt >= DATE '2012-06-01' AND localt < DATE '2012-07-01' )
) INHERITS (public.test_wide_inheritance);
CREATE TABLE public.test_wide_inheritance_201207 (
    CHECK ( localt >= DATE '2012-07-01' AND localt < DATE '2012-08-01' )
) INHERITS (public.test_wide_inheritance);
CREATE TABLE public.test_wide_inheritance_201208 (
    CHECK ( localt >= DATE '2012-08-01' AND localt < DATE '2012-09-01' )
) INHERITS (public.test_wide_inheritance);
CREATE TABLE public.test_wide_inheritance_201209 (
    CHECK ( localt >= DATE '2012-09-01' AND localt < DATE '2012-10-01' )
) INHERITS (public.test_wide_inheritance);
CREATE TABLE public.test_wide_inheritance_201210 (
    CHECK ( localt >= DATE '2012-10-01' AND localt < DATE '2012-11-01' )
) INHERITS (public.test_wide_inheritance);
CREATE TABLE public.test_wide_inheritance_201211 (
    CHECK ( localt >= DATE '2012-11-01' AND localt < DATE '2013-01-01' )
) INHERITS (public.test_wide_inheritance);
CREATE TABLE public.test_wide_inheritance_201212 (
    CHECK ( localt >= DATE '2012-12-01' AND localt < DATE '2013-01-01' )
) INHERITS (public.test_wide_inheritance);
CREATE INDEX ix_test_wide_inheritance_201201_localt ON public.test_wide_inheritance_201201 (localt);
CREATE INDEX ix_test_wide_inheritance_201202_localt ON public.test_wide_inheritance_201202 (localt);
CREATE INDEX ix_test_wide_inheritance_201203_localt ON public.test_wide_inheritance_201203 (localt);
CREATE INDEX ix_test_wide_inheritance_201204_localt ON public.test_wide_inheritance_201204 (localt);
CREATE INDEX ix_test_wide_inheritance_201205_localt ON public.test_wide_inheritance_201205 (localt);
CREATE INDEX ix_test_wide_inheritance_201206_localt ON public.test_wide_inheritance_201206 (localt);
CREATE INDEX ix_test_wide_inheritance_201207_localt ON public.test_wide_inheritance_201207 (localt);
CREATE INDEX ix_test_wide_inheritance_201208_localt ON public.test_wide_inheritance_201208 (localt);
CREATE INDEX ix_test_wide_inheritance_201209_localt ON public.test_wide_inheritance_201209 (localt);
CREATE INDEX ix_test_wide_inheritance_201210_localt ON public.test_wide_inheritance_201210 (localt);
CREATE INDEX ix_test_wide_inheritance_201211_localt ON public.test_wide_inheritance_201211 (localt);
CREATE INDEX ix_test_wide_inheritance_201212_localt ON public.test_wide_inheritance_201212 (localt);
CREATE INDEX ix_test_wide_inheritance_201201_entity ON public.test_wide_inheritance_201201 (entity);
CREATE INDEX ix_test_wide_inheritance_201202_entity ON public.test_wide_inheritance_201202 (entity);
CREATE INDEX ix_test_wide_inheritance_201203_entity ON public.test_wide_inheritance_201203 (entity);
CREATE INDEX ix_test_wide_inheritance_201204_entity ON public.test_wide_inheritance_201204 (entity);
CREATE INDEX ix_test_wide_inheritance_201205_entity ON public.test_wide_inheritance_201205 (entity);
CREATE INDEX ix_test_wide_inheritance_201206_entity ON public.test_wide_inheritance_201206 (entity);
CREATE INDEX ix_test_wide_inheritance_201207_entity ON public.test_wide_inheritance_201207 (entity);
CREATE INDEX ix_test_wide_inheritance_201208_entity ON public.test_wide_inheritance_201208 (entity);
CREATE INDEX ix_test_wide_inheritance_201209_entity ON public.test_wide_inheritance_201209 (entity);
CREATE INDEX ix_test_wide_inheritance_201210_entity ON public.test_wide_inheritance_201210 (entity);
CREATE INDEX ix_test_wide_inheritance_201211_entity ON public.test_wide_inheritance_201211 (entity);
CREATE INDEX ix_test_wide_inheritance_201212_entity ON public.test_wide_inheritance_201212 (entity);

insert into public.test_wide_inheritance_201201 select * from public.test_wide_no_inheritance where localt >= '2012-01-01' AND localt < '2012-02-01';
insert into public.test_wide_inheritance_201202 select * from public.test_wide_no_inheritance where localt >= '2012-02-01' AND localt < '2012-03-01';
insert into public.test_wide_inheritance_201203 select * from public.test_wide_no_inheritance where localt >= '2012-03-01' AND localt < '2012-04-01';
insert into public.test_wide_inheritance_201204 select * from public.test_wide_no_inheritance where localt >= '2012-04-01' AND localt < '2012-05-01';
insert into public.test_wide_inheritance_201205 select * from public.test_wide_no_inheritance where localt >= '2012-05-01' AND localt < '2012-06-01';
insert into public.test_wide_inheritance_201206 select * from public.test_wide_no_inheritance where localt >= '2012-06-01' AND localt < '2012-07-01';
insert into public.test_wide_inheritance_201207 select * from public.test_wide_no_inheritance where localt >= '2012-07-01' AND localt < '2012-08-01';
insert into public.test_wide_inheritance_201208 select * from public.test_wide_no_inheritance where localt >= '2012-08-01' AND localt < '2012-09-01';
insert into public.test_wide_inheritance_201209 select * from public.test_wide_no_inheritance where localt >= '2012-09-01' AND localt < '2012-10-01';
insert into public.test_wide_inheritance_201210 select * from public.test_wide_no_inheritance where localt >= '2012-10-01' AND localt < '2012-11-01';
insert into public.test_wide_inheritance_201211 select * from public.test_wide_no_inheritance where localt >= '2012-11-01' AND localt < '2012-12-01';
insert into public.test_wide_inheritance_201212 select * from public.test_wide_no_inheritance where localt >= '2012-12-01' AND localt < '2013-01-01';

create or replace view test_wide_inheritance_2012 as 
          SELECT * FROM public.test_wide_inheritance_201201
UNION ALL SELECT * FROM public.test_wide_inheritance_201202
UNION ALL SELECT * FROM public.test_wide_inheritance_201203
UNION ALL SELECT * FROM public.test_wide_inheritance_201204
UNION ALL SELECT * FROM public.test_wide_inheritance_201205
UNION ALL SELECT * FROM public.test_wide_inheritance_201206
UNION ALL SELECT * FROM public.test_wide_inheritance_201207
UNION ALL SELECT * FROM public.test_wide_inheritance_201208
UNION ALL SELECT * FROM public.test_wide_inheritance_201209
UNION ALL SELECT * FROM public.test_wide_inheritance_201210
UNION ALL SELECT * FROM public.test_wide_inheritance_201211
UNION ALL SELECT * FROM public.test_wide_inheritance_201212;

如果您在家关注，请注意第一个表是 45GB，每个月度表是 3.9GB，大表的每个索引是 2.2GB，12 个月度表的每个索引是 242MB。我从所有这些中遗漏了主键，但如果这可能会影响我的结果，请告诉我。

接下来，我需要一种评估性能的方法，这当然很棘手并且取决于用例，所以我想出了一些我希望具有代表性的查询并解析了EXPLAIN ANALYZE。本质上，我创建了随机查询，其中为一半的查询选择了可变天数(也是随机的)。它还随机使用 View 、大表或使用继承的父表。我在 R 中使用 RPostgreSQL 做了这个:

wait <- function(wait.time = 5){
        now <- proc.time()[3]
        while(proc.time()[3] < (now + wait.time)) dum <- 0
}
table_choices <- c("public.test_wide_no_inheritance",
                   "public.test_wide_inheritance_2012",
                   "public.test_wide_inheritance")
set.seed(12345)
starts <- sample(1:366,1000,replace=T)
stops <- sample(1:366,1000,replace=T)
entity <- sample(1:200,1000,replace=T)
table <- sample(1:3,1000,replace=T)
st <- data.frame(starts,stops,entity,table)
st_final <- st[stops>starts,]
st_final$start_date <- first_day + st_final$starts
st_final$end_date <- first_day + st_final$stops
st_final$aday <- 0
st_final2 <- st_final
st_final2$end_date  <- st_final2$start_date + 1
st_final2$aday <- 1
st_final <- rbind(st_final,st_final2)
set.seed(12345)
st_final <- st_final[sample(nrow(st_final)),]
query <- list()
dat <- list()
result <- list()
k <- as.vector(0)
for(k in 1:nrow(st_final)){
  wait(5)
  query[[k]] <- paste("explain analyze 
                      select entity,localt, val01, val02, val03 
                      from ",table_choices[st_final$table[k]]," 
                      where entity = ",
                      st_final$entity[k]," and localt >= '",
                      st_final$start_date[k],"' and localt < '",
                      st_final$end_date[k],"' limit 100",sep="")
  dat[[k]] <- fetch(dbSendQuery(con,
                                statement = paste(query[k])),n=-1)
  result[[k]] <- data.frame(table=st_final$table[k],
                            runtime=as.numeric(substr(dat[[k]],
                            regexpr("Total runtime: ",
                            dat[[k]])[1]+15,nchar(dat[[k]])-5)),
                            entity=st_final$entity[k],
                            start=st_final$start_date[k],
                            end=st_final$end_date[k], 
                            diff= as.numeric(st_final$end_date[k]) - 
                              as.numeric(st_final$start_date[k]))
  print(k)
}
results <- do.call("rbind", result)

我没有看到 View 和继承表之间有任何显着差异。这可能是因为我在较小的表上使用了约束吗？继承版本是否扫描所有约束， View 也是如此？我不应该使用约束吗？除了让您的模式对 Postgresql 更独特之外，我不明白表继承增加了什么。

这是显示结果的图表:

这是我使用 Postgres 9.2.3 的一些设置:

name                    setting
max_connections             100
shared_buffers          2097152
effective_cache_size    6291456
maintenance_work_mem    1048576
work_mem                 262144

非常感谢您提供的任何意见。

Answer 1

View 也可以从表的联合或内部/外部连接或过滤器中创建。父表旨在使数据库模型与面向对象编程的结构更加匹配。