�R���e���c

�O���R���e���c

�A�N�Z�X�J�E���^

  • ���v: 6,147,143
  • ����: 1,722
  • ���: 1,480
�� �� 2012�N 12�� �� ��
�����������y��
1 2
3 4 5 6 7 8 9
10 11 12 13 14 15 16
17 18 19 20 21 22 23
24 25 26 27 28 29 30
31

�ŋ߂̋L��

�J�e�S��

�ς�� 2012/12/30

�ς�� 2012/12/30

UNION ���܂܂��r���[�ƃC���f�b�N�X

��Ђŕ����ꂽ �uUNION ���܂܂��r���[������񂾂��ǁA�C���f�b�N�X�g���Ă����H�v�Ƃ��� ����ɑ΂��� �u�f�[�^�x�[�X���������̕����������ǂ��Ɣ��f������g���Ă���邵�A���s�v�������Ă݂�΁H�v�Ƃ��� �񓚂�������ł����A�O�̂��ߎ����Ă݂܂��B

�܂��A�e�X�g�p�̃e�[�u���Ƀf�[�^��o�^���܂��B 

INSERT INTO table1 (f1)
  SELECT generate_series FROM generate_series(1, 10000);

INSERT INTO table2 (f1)
  SELECT generate_series FROM generate_series(1, 10000);

generate_series �֐��́A�֗��ł��ˁB

�܂��������ɒP���� SQL �Ŏ��s�v����擾�B ������񎖑O�Ƀe�[�u���� Analyze ���Ă����܂��B 

EXPLAIN
  SELECT * FROM table1
  WHERE f1 = 1

������ 

Index Scan using table1_pkey on table1  (cost=0.00..8.27 rows=1 width=8)
  Index Cond: (f1 = 1)

�C���f�b�N�X table1_pkey ���g�p����Ă��܂��B

���ɕ��₢���킹�� UNION ALL ���Ď��s�v����擾�B 

EXPLAIN
  SELECT *
  FROM (SELECT * FROM table1
        UNION ALL
        SELECT * FROM table2) AS t1
  WHERE f1 = 1

������ 

Result  (cost=0.00..16.54 rows=2 width=8)
  ->  Append  (cost=0.00..16.54 rows=2 width=8)
        ->  Index Scan using table1_pkey on table1  (cost=0.00..8.27 rows=1 width=8)
              Index Cond: (f1 = 1)
        ->  Index Scan using table2_pkey on table2  (cost=0.00..8.27 rows=1 width=8)
              Index Cond: (f1 = 1)

����������ƁA�C���f�b�N�X table1_pkey �� table2_pkey ���g�p����Ă��܂��B

���Ȃ݂� UNION ALL ����Ȃ��� UNION �̏ꍇ�B 

EXPLAIN
  SELECT *
  FROM (SELECT * FROM table1
        UNION
        SELECT * FROM table2) AS t1
  WHERE f1 = 1

������ 

Unique  (cost=16.57..16.58 rows=2 width=8)
  ->  Sort  (cost=16.57..16.57 rows=2 width=8)
        Sort Key: table1.f1, table1.f2
        ->  Append  (cost=0.00..16.56 rows=2 width=8)
              ->  Index Scan using table1_pkey on table1  (cost=0.00..8.27 rows=1 width=8)
                    Index Cond: (f1 = 1)
              ->  Index Scan using table2_pkey on table2  (cost=0.00..8.27 rows=1 width=8)
                    Index Cond: (f1 = 1)

��������C���f�b�N�X�͎g���Ă���܂��B

�O�̂��߁A�r���[���쐬���Ď����Ă݂܂��B 

CREATE VIEW view1 AS
  SELECT *
  FROM (SELECT * FROM table1
        UNION ALL
        SELECT * FROM table2) AS t1

�r���[�ɑ΂��Ď��s�v����擾�B 

EXPLAIN
  SELECT * FROM view1
  WHERE f1 = 1

������ 

Result  (cost=0.00..16.54 rows=2 width=8)
  ->  Append  (cost=0.00..16.54 rows=2 width=8)
        ->  Index Scan using table1_pkey on table1  (cost=0.00..8.27 rows=1 width=8)
              Index Cond: (f1 = 1)
        ->  Index Scan using table2_pkey on table2  (cost=0.00..8.27 rows=1 width=8)
              Index Cond: (f1 = 1)

���ʂ͕��₢���킹�Ɠ����ł����B

���������̂́A�����̎�Ŏ����Ă����ƈ��S�ł��ˁB

PostgreSQL 2012/12/30

Google �T�C�g������

Amazon�A�\�V�G�C�g