postgresql 9.2的新功能测试

〇〇

postgresql没有minus,但有except
test=# explain select max(a.info)from sli_test a where a.id in(select id from sli_test except select id from sli_test2 where id<5000);
                                                           QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------
Aggregate  (cost=220114.69..220114.70 rows=1 width=12)
   ->  Merge Semi Join  (cost=154083.92..218864.69 rows=500000 width=12)
         Merge Cond: (a.id = "ANY_subquery".id)
         ->  Index Scan using sli_test_pkey on sli_test a  (cost=0.00..32258.35 rows=1000000 width=16)
         ->  Materialize  (cost=154083.92..171606.34 rows=1000000 width=4)
               ->  Subquery Scan on "ANY_subquery"  (cost=154083.92..169106.34 rows=1000000 width=4)
                     ->  SetOp Except  (cost=154083.92..159106.34 rows=1000000 width=4)
                           ->  Sort  (cost=154083.92..156595.13 rows=1004485 width=4)
                                 Sort Key: "*SELECT* 1".id
                                 ->  Append  (cost=0.00..26478.69 rows=1004485 width=4)
                                       ->  Subquery Scan on "*SELECT* 1"  (cost=0.00..26274.00 rows=1000000 width=4)
                                             ->  Seq Scan on sli_test  (cost=0.00..16274.00 rows=1000000 width=4)
                                       ->  Subquery Scan on "*SELECT* 2"  (cost=0.00..204.69 rows=4485 width=4)
                                             ->  Index Only Scan using b_id on sli_test2  (cost=0.00..159.84 rows=4485 width=4)
                                                   Index Cond: (id < 5000)
(15 行记录)

myth8860

oo测测dm7，数据可以从postgre迁过去

〇〇

myth8860 发表于 2012-9-18 16:23
oo测测dm7，数据可以从postgre迁过去

it01测了

myth8860

〇〇 发表于 2012-9-18 16:32
it01测了

嗯，01测的tpch，你这里测的是子查询吧？

〇〇

myth8860 发表于 2012-9-18 16:54
嗯，01测的tpch，你这里测的是子查询吧？

你用我的例子贴出达梦的执行计划？

myth8860

〇〇 发表于 2012-9-19 09:28
你用我的例子贴出达梦的执行计划？

好的，我试试

myth8860

create table sli_test (id int primary key,info varchar(20));

begin
for i in 1..1000000 loop
   insert into sli_test values(i,'digoal'||i);
   if mod(i,1000)=0 then
      commit;
   end if;
end loop;
end;
[执行语句1]:
begin
for i in 1..1000000 loop
   insert into sli_test values(i,'digoal'||i);
   if mod(i,1000)=0 then
      commit;
   end if;
end loop;
end;
执行成功, 耗时28秒532毫秒. 执行号:2,996,921


--这个没有postgre那种方式插入数据快

create table sli_test2 (id int not null,info varchar(20));

begin
for i in 1..1000000 loop
   insert into sli_test2 values(i,'digoal'||i);
   if mod(i,1000)=0 then
      commit;
   end if;
end loop;
end;

explain select max(b.info) from sli_test a,sli_test2 b where a.id=b.id;

#NSET2: [390, 1, 0]
  #PRJT2: [390, 1, 28]; exp_num(1), is_atom(FALSE)
    #AAGR2: [390, 1, 28]; grp_num(0), sfun_num(1)
      #HASH2 INNER JOIN: [390, 1000000, 28]; LKEY_UNIQUE KEY_NUM(1);
        #SSCN: [27, 1000000, 4]; INDEX33555470(sli_test)
        #CSCN2: [197, 1000000, 24]; INDEX33555471(sli_test2)


select max(b.info) from sli_test a,sli_test2 b where a.id=b.id;

[执行语句1]:
select max(b.info) from sli_test a,sli_test2 b where a.id=b.id;
执行成功, 耗时270毫秒. 执行号:2,996,933


explain select max(b.info) from sli_test a,sli_test2 b where a.id=b.id and a.id>500000;
#NSET2: [276, 1, 0]
  #PRJT2: [276, 1, 28]; exp_num(1), is_atom(FALSE)
    #AAGR2: [276, 1, 28]; grp_num(0), sfun_num(1)
      #HASH2 INNER JOIN: [276, 49500, 28]; LKEY_UNIQUE KEY_NUM(1);
        #SSEK2: [7, 50000, 4]; scan_type(ASC), INDEX33555470(sli_test), scan_range(,]
        #CSCN2: [197, 1000000, 24]; INDEX33555471(sli_test2)



select max(b.info) from sli_test a,sli_test2 b where a.id=b.id and a.id>500000;


[执行语句1]:
select max(b.info) from sli_test a,sli_test2 b where a.id=b.id and a.id>500000;
执行成功, 耗时234毫秒. 执行号:2,996,935

create index b_id on sli_test2(id);
[执行语句1]:
create index b_id on sli_test2(id);
执行成功, 耗时781毫秒. 执行号:2,996,996


explain select max(b.info) from sli_test a,sli_test2 b where a.id=b.id;
#NSET2: [390, 1, 0]
  #PRJT2: [390, 1, 28]; exp_num(1), is_atom(FALSE)
    #AAGR2: [390, 1, 28]; grp_num(0), sfun_num(1)
      #HASH2 INNER JOIN: [390, 1000000, 28]; LKEY_UNIQUE KEY_NUM(1);
        #SSCN: [27, 1000000, 4]; INDEX33555470(sli_test)
        #CSCN2: [197, 1000000, 24]; INDEX33555471(sli_test2)


explain select max(b.info) from sli_test a,sli_test2 b where a.id=b.id and a.id>500000;
#NSET2: [276, 1, 0]
  #PRJT2: [276, 1, 28]; exp_num(1), is_atom(FALSE)
    #AAGR2: [276, 1, 28]; grp_num(0), sfun_num(1)
      #HASH2 INNER JOIN: [276, 49500, 28]; LKEY_UNIQUE KEY_NUM(1);
        #SSEK2: [7, 50000, 4]; scan_type(ASC), INDEX33555470(sli_test), scan_range(,]
        #CSCN2: [197, 1000000, 24]; INDEX33555471(sli_test2)



SQL>select max(b.info) from sli_test a,sli_test2 b where a.id=b.id;

行号       MAX(b.info)
---------- ------------
1          digoal999999

已用时间: 288.128(ms) clock tick:106405104. Execute id is 2997001.


SQL>select max(b.info) from sli_test a,sli_test2 b where a.id=b.id and a.id>500000;

行号       MAX(b.info)
---------- ------------
1          digoal999999

已用时间: 297.448(ms) clock tick:150122588. Execute id is 2997000.


这几个查询的时间比postgre 似乎快一点，不过机器不一样，不具有可比性

myth8860

--DM7对子查询做了深度的优化，计划是“平面”的
SQL>explain select max(a.info)from sli_test a where a.id not in(select b.id from sli_test2 b where b.id<50000);

#NSET2: [307, 1, 0]
  #PRJT2: [307, 1, 0]; exp_num(1), is_atom(FALSE)
    #AAGR2: [307, 1, 0]; grp_num(0), sfun_num(1)
      #HASH RIGHT SEMI JOIN2: [307, 50000, 0];  (ANTI),
        #PRJT2: [7, 50000, 4]; exp_num(1), is_atom(FALSE)
          #SSEK2: [7, 50000, 4]; scan_type(ASC), b_id(sli_test2), scan_range(,)
        #CSCN2: [197, 1000000, 24]; INDEX33555469(sli_test)

已用时间: 2.071(ms) clock tick:2010168. Execute id is 0.
SQL>select max(a.info)from sli_test a where a.id not in(select b.id from sli_test2 b where b.id<50000);

行号       MAX(a.info)
---------- ------------
1          digoal999999

已用时间: 156.033(ms) clock tick:136683312. Execute id is 2997002.


SQL>explain select max(a.info)from sli_test a where a.id in(select b.id from sli_test2 b  where b.id>=50000);

#NSET2: [307, 1, 0]
  #PRJT2: [307, 1, 0]; exp_num(1), is_atom(FALSE)
    #AAGR2: [307, 1, 0]; grp_num(0), sfun_num(1)
      #HASH RIGHT SEMI JOIN2: [307, 49500, 0];
        #SSEK2: [7, 50000, 4]; scan_type(ASC), b_id(sli_test2), scan_range[,]
        #CSCN2: [197, 1000000, 24]; INDEX33555469(sli_test)

已用时间: 1.966(ms) clock tick:1813680. Execute id is 0.


SQL>select max(a.info)from sli_test a where a.id in(select b.id from sli_test2 b where b.id>=50000);

行号       MAX(a.info)
---------- ------------
1          digoal999999

已用时间: 439.264(ms) clock tick:186240404. Execute id is 2997006.

myth8860

--not exists
SQL>explain select max(a.info)from sli_test a where not exists(select 1 from sli_test2 b where b.id<50000 and a.id=b.id);

#NSET2: [307, 1, 0]
  #PRJT2: [307, 1, 0]; exp_num(1), is_atom(FALSE)
    #AAGR2: [307, 1, 0]; grp_num(0), sfun_num(1)
      #HASH RIGHT SEMI JOIN2: [307, 49500, 0];  (ANTI),
        #SSEK2: [7, 50000, 4]; scan_type(ASC), b_id(sli_test2), scan_range(,)
        #CSCN2: [197, 1000000, 32]; INDEX33555469(sli_test)

已用时间: 1.894(ms) clock tick:1405244. Execute id is 0.

〇〇

myth8860 发表于 2012-9-19 10:41
--DM7对子查询做了深度的优化，计划是“平面”的
SQL>explain select max(a.info)from sli_test a where a ...

不错。