- 论坛徽章:
- 0
|
今天在互联网上看到有人发表了这样的感慨，其中“INSERT INTO my_table SELECT 1, 'a' UNION SELECT 2, 'b' UNION SELECT 3, 'c' UNION ...
”这句可能是笔误，应该改成"INSERT INTO my_table SELECT 1, 'a' UNION ALL SELECT 2, 'b' UNION ALL SELECT 3, 'c' UNION ALL...
"其他的都蛮对的。
You can use the INSERT INTO tbl <query> syntax to accelerate the speed of inserts by batching them together. For example...
INSERT INTO my_table SELECT 1, 'a' UNION SELECT 2, 'b' UNION SELECT 3, 'c' UNION ...
If you batch up many sets of values per INSERT statement and batch up multiple INSERT statements per transaction, you can achieve significantly faster insertion performance. I managed to achieve almost 8x faster inserts on a PostgreSQL 8.1 / Win2K installation by batching up 100 (small) using this technique.
If anyone knows a faster/more elegant way to construct the row set than using SELECT..UNION then please let me know!
下面来用例子分析一下:
首先要介绍几个PostgreSQL的系统列(就不翻译了,还是原味比较好):
tableoid
The OID of the table containing this row. This column is particularly handy for queries that select from inheritance hierarchies, since without it, it's difficult to tell which individual table a row came from. The tableoid can be joined against the oid column of pg_class to obtain the table name.
xmin
The identity (transaction ID) of the inserting transaction for this row version. (A row version is an individual state of a row; each update of a row creates a new row version for the same logical row.)
cmin
The command identifier (starting at zero) within the inserting transaction.
xmax
The identity (transaction ID) of the deleting transaction, or zero for an undeleted row version. It is possible for this column to be nonzero in a visible row version. That usually indicates that the deleting transaction hasn't committed yet, or that an attempted deletion was rolled back.
cmax
The command identifier within the deleting transaction, or zero.
ctid
The physical location of the row version within its table. Note that although the ctid can be used to locate the row version very quickly, a row's ctid will change if it is updated or moved by VACUUM FULL. Therefore ctid is useless as a long-term row identifier. The OID, or even better a user-defined serial number, should be used to identify logical rows.
例一:
test=> \d tbl_test
Table "test.tbl_test"
Column | Type | Modifiers
--------+--------+-----------
id | bigint | not null
Indexes:
"tbl_test_pkey" PRIMARY KEY, btree (id)
test=> insert into tbl_Test (id) values (1),(2),(3),(4);
INSERT 0 4
test=> select ctid,cmin,cmax,xmin,xmax,* from tbl_test;
ctid | cmin | cmax | xmin | xmax | id
-------+------+------+------+------+----
(0,1) | 0 | 0 | 668 | 0 | 1
(0,2) | 0 | 0 | 668 | 0 | 2
(0,3) | 0 | 0 | 668 | 0 | 3
(0,4) | 0 | 0 | 668 | 0 | 4
(4 rows)
从结果上看四条记录都是同一个事务,同一个CMIN，CMAX值。
例二：
test=> begin;
BEGIN
test=> insert into tbl_Test (id) values (5);
INSERT 0 1
test=> insert into tbl_Test (id) values (6);
INSERT 0 1
test=> insert into tbl_Test (id) values (7);
INSERT 0 1
test=> insert into tbl_Test (id) values ( ;
INSERT 0 1
test=> commit;
COMMIT
test=> select ctid,cmin,cmax,xmin,xmax,* from tbl_test order by id;
ctid | cmin | cmax | xmin | xmax | id
-------+------+------+------+------+----
(0,1) | 0 | 0 | 668 | 0 | 1
(0,2) | 0 | 0 | 668 | 0 | 2
(0,3) | 0 | 0 | 668 | 0 | 3
(0,4) | 0 | 0 | 668 | 0 | 4
(0,5) | 0 | 0 | 669 | 0 | 5
(0,6) | 1 | 1 | 669 | 0 | 6
(0,7) | 2 | 2 | 669 | 0 | 7
(0, | 3 | 3 | 669 | 0 | 8
(8 rows)
从结果上看四条记录都是同一个事务,但是每一次插入操作占用了一个CMIN，CMAX值。
例三：
test=> insert into tbl_Test (id) select 9 union all select 10 union all select 11 union all select 12;
INSERT 0 4
test=> select ctid,cmin,cmax,xmin,xmax,* from tbl_test order by id;
ctid | cmin | cmax | xmin | xmax | id
--------+------+------+------+------+----
(0,1) | 0 | 0 | 668 | 0 | 1
(0,2) | 0 | 0 | 668 | 0 | 2
(0,3) | 0 | 0 | 668 | 0 | 3
(0,4) | 0 | 0 | 668 | 0 | 4
(0,5) | 0 | 0 | 669 | 0 | 5
(0,6) | 1 | 1 | 669 | 0 | 6
(0,7) | 2 | 2 | 669 | 0 | 7
(0, | 3 | 3 | 669 | 0 | 8
(0,9) | 0 | 0 | 670 | 0 | 9
(0,10) | 0 | 0 | 670 | 0 | 10
(0,11) | 0 | 0 | 670 | 0 | 11
(0,12) | 0 | 0 | 670 | 0 | 12
(12 rows)
从结果上看四条记录都是同一个事务,同一个CMIN，CMAX值。
例四：
test=> insert into tbl_test (id) select generate_series(13,16);
INSERT 0 4
test=> select ctid,cmin,cmax,xmin,xmax,* from tbl_test order by id;
ctid | cmin | cmax | xmin | xmax | id
--------+------+------+------+------+----
(0,1) | 0 | 0 | 668 | 0 | 1
(0,2) | 0 | 0 | 668 | 0 | 2
(0,3) | 0 | 0 | 668 | 0 | 3
(0,4) | 0 | 0 | 668 | 0 | 4
(0,5) | 0 | 0 | 669 | 0 | 5
(0,6) | 1 | 1 | 669 | 0 | 6
(0,7) | 2 | 2 | 669 | 0 | 7
(0, | 3 | 3 | 669 | 0 | 8
(0,9) | 0 | 0 | 670 | 0 | 9
(0,10) | 0 | 0 | 670 | 0 | 10
(0,11) | 0 | 0 | 670 | 0 | 11
(0,12) | 0 | 0 | 670 | 0 | 12
(0,13) | 0 | 0 | 671 | 0 | 13
(0,14) | 0 | 0 | 671 | 0 | 14
(0,15) | 0 | 0 | 671 | 0 | 15
(0,16) | 0 | 0 | 671 | 0 | 16
(16 rows)
从结果上看四条记录都是同一个事务,同一个CMIN，CMAX值。
例五：
test=> prepare t_test(int as insert into tbl_test (id) values ($1);
PREPARE
test=> begin;execute t_test(17);
BEGIN
INSERT 0 1
test=> execute t_test(1;
INSERT 0 1
test=> execute t_test(19);
INSERT 0 1
test=> execute t_test(20);
INSERT 0 1
test=> commit;
COMMIT
test=> select tableoid,ctid,cmin,cmax,xmin,xmax,* from tbl_test order by id;
tableoid | ctid | cmin | cmax | xmin | xmax | id
----------+--------+------+------+------+------+----
16388 | (0,1) | 0 | 0 | 668 | 0 | 1
16388 | (0,2) | 0 | 0 | 668 | 0 | 2
16388 | (0,3) | 0 | 0 | 668 | 0 | 3
16388 | (0,4) | 0 | 0 | 668 | 0 | 4
16388 | (0,5) | 0 | 0 | 669 | 0 | 5
16388 | (0,6) | 1 | 1 | 669 | 0 | 6
16388 | (0,7) | 2 | 2 | 669 | 0 | 7
16388 | (0, | 3 | 3 | 669 | 0 | 8
16388 | (0,9) | 0 | 0 | 670 | 0 | 9
16388 | (0,10) | 0 | 0 | 670 | 0 | 10
16388 | (0,11) | 0 | 0 | 670 | 0 | 11
16388 | (0,12) | 0 | 0 | 670 | 0 | 12
16388 | (0,13) | 0 | 0 | 671 | 0 | 13
16388 | (0,14) | 0 | 0 | 671 | 0 | 14
16388 | (0,15) | 0 | 0 | 671 | 0 | 15
16388 | (0,16) | 0 | 0 | 671 | 0 | 16
16388 | (0,17) | 0 | 0 | 672 | 0 | 17
16388 | (0,1 | 1 | 1 | 672 | 0 | 18
16388 | (0,19) | 2 | 2 | 672 | 0 | 19
16388 | (0,20) | 3 | 3 | 672 | 0 | 20
(20 rows)
从结果上看四条记录都是同一个事务,但是每一次插入操作占用了一个CMIN，CMAX值。
例六：
test=> prepare t_test1(int8,int8,int8,int as insert into tbl_test (id) values ($1),($2),($3),($4);
PREPARE
test=> begin;
BEGIN
test=> execute t_test1(21,22,23,24);
INSERT 0 4
test=> commit;
COMMIT
test=> select tableoid,ctid,cmin,cmax,xmin,xmax,* from tbl_test order by id;
tableoid | ctid | cmin | cmax | xmin | xmax | id
----------+--------+------+------+------+------+----
16388 | (0,1) | 0 | 0 | 668 | 0 | 1
16388 | (0,2) | 0 | 0 | 668 | 0 | 2
16388 | (0,3) | 0 | 0 | 668 | 0 | 3
16388 | (0,4) | 0 | 0 | 668 | 0 | 4
16388 | (0,5) | 0 | 0 | 669 | 0 | 5
16388 | (0,6) | 1 | 1 | 669 | 0 | 6
16388 | (0,7) | 2 | 2 | 669 | 0 | 7
16388 | (0, | 3 | 3 | 669 | 0 | 8
16388 | (0,9) | 0 | 0 | 670 | 0 | 9
16388 | (0,10) | 0 | 0 | 670 | 0 | 10
16388 | (0,11) | 0 | 0 | 670 | 0 | 11
16388 | (0,12) | 0 | 0 | 670 | 0 | 12
16388 | (0,13) | 0 | 0 | 671 | 0 | 13
16388 | (0,14) | 0 | 0 | 671 | 0 | 14
16388 | (0,15) | 0 | 0 | 671 | 0 | 15
16388 | (0,16) | 0 | 0 | 671 | 0 | 16
16388 | (0,17) | 0 | 0 | 672 | 0 | 17
16388 | (0,18) | 1 | 1 | 672 | 0 | 18
16388 | (0,19) | 2 | 2 | 672 | 0 | 19
16388 | (0,20) | 3 | 3 | 672 | 0 | 20
16388 | (0,21) | 0 | 0 | 673 | 0 | 21
16388 | (0,22) | 0 | 0 | 673 | 0 | 22
16388 | (0,23) | 0 | 0 | 673 | 0 | 23
16388 | (0,24) | 0 | 0 | 673 | 0 | 24
(24 rows)
从结果上看四条记录都是同一个事务,同一个CMIN，CMAX值。
例七：
test=> begin;
BEGIN
test=> insert into tbl_Test (id) values (25);
INSERT 0 1
test=> select tableoid,ctid,cmin,cmax,xmin,xmax,* from tbl_test order by id;
tableoid | ctid | cmin | cmax | xmin | xmax | id
----------+--------+------+------+------+------+----
16388 | (0,1) | 0 | 0 | 668 | 0 | 1
16388 | (0,2) | 0 | 0 | 668 | 0 | 2
16388 | (0,3) | 0 | 0 | 668 | 0 | 3
16388 | (0,4) | 0 | 0 | 668 | 0 | 4
16388 | (0,5) | 0 | 0 | 669 | 0 | 5
16388 | (0,6) | 1 | 1 | 669 | 0 | 6
16388 | (0,7) | 2 | 2 | 669 | 0 | 7
16388 | (0,8) | 3 | 3 | 669 | 0 | 8
16388 | (0,9) | 0 | 0 | 670 | 0 | 9
16388 | (0,10) | 0 | 0 | 670 | 0 | 10
16388 | (0,11) | 0 | 0 | 670 | 0 | 11
16388 | (0,12) | 0 | 0 | 670 | 0 | 12
16388 | (0,13) | 0 | 0 | 671 | 0 | 13
16388 | (0,14) | 0 | 0 | 671 | 0 | 14
16388 | (0,15) | 0 | 0 | 671 | 0 | 15
16388 | (0,16) | 0 | 0 | 671 | 0 | 16
16388 | (0,17) | 0 | 0 | 672 | 0 | 17
16388 | (0,18) | 1 | 1 | 672 | 0 | 18
16388 | (0,19) | 2 | 2 | 672 | 0 | 19
16388 | (0,20) | 3 | 3 | 672 | 0 | 20
16388 | (0,21) | 0 | 0 | 673 | 0 | 21
16388 | (0,22) | 0 | 0 | 673 | 0 | 22
16388 | (0,23) | 0 | 0 | 673 | 0 | 23
16388 | (0,24) | 0 | 0 | 673 | 0 | 24
16388 | (0,27) | 0 | 0 | 675 | 0 | 25
(25 rows)
test=> insert into tbl_Test (id) values (26);
INSERT 0 1
test=> savepoint one;
SAVEPOINT
test=> insert into tbl_Test (id) values (27);
INSERT 0 1
test=> insert into tbl_Test (id) values (28);
INSERT 0 1
test=> select tableoid,ctid,cmin,cmax,xmin,xmax,* from tbl_test order by id;
tableoid | ctid | cmin | cmax | xmin | xmax | id
----------+--------+------+------+------+------+----
16388 | (0,1) | 0 | 0 | 668 | 0 | 1
16388 | (0,2) | 0 | 0 | 668 | 0 | 2
16388 | (0,3) | 0 | 0 | 668 | 0 | 3
16388 | (0,4) | 0 | 0 | 668 | 0 | 4
16388 | (0,5) | 0 | 0 | 669 | 0 | 5
16388 | (0,6) | 1 | 1 | 669 | 0 | 6
16388 | (0,7) | 2 | 2 | 669 | 0 | 7
16388 | (0,8) | 3 | 3 | 669 | 0 | 8
16388 | (0,9) | 0 | 0 | 670 | 0 | 9
16388 | (0,10) | 0 | 0 | 670 | 0 | 10
16388 | (0,11) | 0 | 0 | 670 | 0 | 11
16388 | (0,12) | 0 | 0 | 670 | 0 | 12
16388 | (0,13) | 0 | 0 | 671 | 0 | 13
16388 | (0,14) | 0 | 0 | 671 | 0 | 14
16388 | (0,15) | 0 | 0 | 671 | 0 | 15
16388 | (0,16) | 0 | 0 | 671 | 0 | 16
16388 | (0,17) | 0 | 0 | 672 | 0 | 17
16388 | (0,18) | 1 | 1 | 672 | 0 | 18
16388 | (0,19) | 2 | 2 | 672 | 0 | 19
16388 | (0,20) | 3 | 3 | 672 | 0 | 20
16388 | (0,21) | 0 | 0 | 673 | 0 | 21
16388 | (0,22) | 0 | 0 | 673 | 0 | 22
16388 | (0,23) | 0 | 0 | 673 | 0 | 23
16388 | (0,24) | 0 | 0 | 673 | 0 | 24
16388 | (0,27) | 0 | 0 | 675 | 0 | 25
16388 | (0,28) | 1 | 1 | 675 | 0 | 26
16388 | (0,29) | 2 | 2 | 676 | 0 | 27
16388 | (0,30) | 3 | 3 | 676 | 0 | 28
(28 rows)
test=> rollback to one;
ROLLBACK
test=> select tableoid,ctid,cmin,cmax,xmin,xmax,* from tbl_test order by id;
tableoid | ctid | cmin | cmax | xmin | xmax | id
----------+--------+------+------+------+------+----
16388 | (0,1) | 0 | 0 | 668 | 0 | 1
16388 | (0,2) | 0 | 0 | 668 | 0 | 2
16388 | (0,3) | 0 | 0 | 668 | 0 | 3
16388 | (0,4) | 0 | 0 | 668 | 0 | 4
16388 | (0,5) | 0 | 0 | 669 | 0 | 5
16388 | (0,6) | 1 | 1 | 669 | 0 | 6
16388 | (0,7) | 2 | 2 | 669 | 0 | 7
16388 | (0,8) | 3 | 3 | 669 | 0 | 8
16388 | (0,9) | 0 | 0 | 670 | 0 | 9
16388 | (0,10) | 0 | 0 | 670 | 0 | 10
16388 | (0,11) | 0 | 0 | 670 | 0 | 11
16388 | (0,12) | 0 | 0 | 670 | 0 | 12
16388 | (0,13) | 0 | 0 | 671 | 0 | 13
16388 | (0,14) | 0 | 0 | 671 | 0 | 14
16388 | (0,15) | 0 | 0 | 671 | 0 | 15
16388 | (0,16) | 0 | 0 | 671 | 0 | 16
16388 | (0,17) | 0 | 0 | 672 | 0 | 17
16388 | (0,18) | 1 | 1 | 672 | 0 | 18
16388 | (0,19) | 2 | 2 | 672 | 0 | 19
16388 | (0,20) | 3 | 3 | 672 | 0 | 20
16388 | (0,21) | 0 | 0 | 673 | 0 | 21
16388 | (0,22) | 0 | 0 | 673 | 0 | 22
16388 | (0,23) | 0 | 0 | 673 | 0 | 23
16388 | (0,24) | 0 | 0 | 673 | 0 | 24
16388 | (0,27) | 0 | 0 | 675 | 0 | 25
16388 | (0,28) | 1 | 1 | 675 | 0 | 26
(26 rows)
test=> insert into tbl_Test (id) values (27);
INSERT 0 1
test=> insert into tbl_Test (id) values (28);
INSERT 0 1
test=> select tableoid,ctid,cmin,cmax,xmin,xmax,* from tbl_test order by id;
tableoid | ctid | cmin | cmax | xmin | xmax | id
----------+--------+------+------+------+------+----
16388 | (0,1) | 0 | 0 | 668 | 0 | 1
16388 | (0,2) | 0 | 0 | 668 | 0 | 2
16388 | (0,3) | 0 | 0 | 668 | 0 | 3
16388 | (0,4) | 0 | 0 | 668 | 0 | 4
16388 | (0,5) | 0 | 0 | 669 | 0 | 5
16388 | (0,6) | 1 | 1 | 669 | 0 | 6
16388 | (0,7) | 2 | 2 | 669 | 0 | 7
16388 | (0,8) | 3 | 3 | 669 | 0 | 8
16388 | (0,9) | 0 | 0 | 670 | 0 | 9
16388 | (0,10) | 0 | 0 | 670 | 0 | 10
16388 | (0,11) | 0 | 0 | 670 | 0 | 11
16388 | (0,12) | 0 | 0 | 670 | 0 | 12
16388 | (0,13) | 0 | 0 | 671 | 0 | 13
16388 | (0,14) | 0 | 0 | 671 | 0 | 14
16388 | (0,15) | 0 | 0 | 671 | 0 | 15
16388 | (0,16) | 0 | 0 | 671 | 0 | 16
16388 | (0,17) | 0 | 0 | 672 | 0 | 17
16388 | (0,18) | 1 | 1 | 672 | 0 | 18
16388 | (0,19) | 2 | 2 | 672 | 0 | 19
16388 | (0,20) | 3 | 3 | 672 | 0 | 20
16388 | (0,21) | 0 | 0 | 673 | 0 | 21
16388 | (0,22) | 0 | 0 | 673 | 0 | 22
16388 | (0,23) | 0 | 0 | 673 | 0 | 23
16388 | (0,24) | 0 | 0 | 673 | 0 | 24
16388 | (0,27) | 0 | 0 | 675 | 0 | 25
16388 | (0,28) | 1 | 1 | 675 | 0 | 26
16388 | (0,31) | 4 | 4 | 677 | 0 | 27
16388 | (0,32) | 5 | 5 | 677 | 0 | 28
(28 rows)
test=> commit;
COMMIT
从结果上看每一个SAVEPOINT会产生一个新的事物号，但是CMIN , CMAX值是连续的，不会在新事物重置。回滚到SAVEPOINT后，CMIN,CMAX值的产生和序列差不多，不能回滚，继续往前。 |
|
免费注册
发表于 2011-02-22 19:18