分区:视图还是继承的表--重点是什么？

Question

我的基本问题是，具有继承性的表的父表是否与视图的性能相同，如果是(看起来是这样)，为什么不使用视图呢？

为了研究这一点，我决定尝试评估单个表、基于月表的视图和具有继承的月表之间的性能差异。这段代码会很长，所以如果愿意的话，可以建议在stackoverflow之外添加代码，或者使用其他我可以简化的方法。我也会描述我在做什么，所以你不一定需要运行代码。

我想要200个实体，2012年每分钟有50个值。首先，我让我的大表"public.test_wide_no_inheritance“和每个较小的月表"public.test_wide_inheritance_YYYYMM”继承到一个空表，就像前面的"public.test_wide_inheritance"，最后是一个基于小表的视图"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;

如果您是在家中跟踪，请注意，第一个表是45 12，每个月表是3.9 12，大表之外的每个索引是2.2 12，12个月表的每个索引是242MB。我遗漏了其中的主键，但如果这可能会影响我的结果，请让我知道。

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

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)

我看不出视图和继承表之间有什么显著的区别。这可能是因为我在较小的表上使用了约束吗？继承的版本是否扫描所有的约束，视图也是如此吗？我不应该使用约束吗？除了使您的模式对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

视图也可以从表的联合、内/外联接或过滤器中生成。父表的目的是使数据库模型更紧密地与面向对象编程的结构相匹配。