SQL �� LATERAL �L�[���[�h

PostgreSQL 9.3 ���� SQL �� LATERAL �Ƃ����L�[���[�h���L�q�ł���悤�ɂȂ��Ă��܂��B �܂��Ǝ��̊g�����Ǝv������A���� LATERAL �� SQL�W�� �ŋK�肳��Ă��邻���ł��B Oracle �ł� 12c ����T�|�[�g����Ă���悤�ł��B

[�Q�l]
LATERAL���g���Ă݂悤 ? Let's Postgres

�g�����Ƃ��Ă� �x���]���H �݂����Ȋ����ł��傤���B PostgreSQL �ł́ALATERAL �𕛖₢���킹��֐��ɑ΂��Ă��Ďw��ł���̂ł����A FROM �Ɏw�肵���₢�����̌��ʂ��A���₢���킹�̒��Ŏg�p���邱�Ƃ��ł��܂��B

���Ƃ��΁A���̂悤�� SQL �̓G���[�ɂȂ�܂��B

psql=# SELECT table1.field1, subquery1.field2
psql-# FROM table1
psql-# LEFT JOIN (
psql-#    SELECT field2 FROM table2
psql-#    WHERE table2.field1 = table1.field1) AS subquery1
psql-#    ON TRUE;

����� �Ԏ��̕����𕛖₢���킹�Ŏg�p���Ă��邽�߂ł��B

�{���́A���̂悤�ɕ��₢���킹����O�ɏo���K�v������܂��B

psql=# SELECT table1.field1, subquery1.field2
psql-# FROM table1
psql-# LEFT JOIN (
psql-#    SELECT field2 FROM table2) AS subquery1
psql-#    ON (subquery1.field1 = table1.field1);

�Ƃ��낪 LATERAL ���g���� ���̂悤�ȏ��������ł��܂��B

psql=# SELECT table1.field1, subquery1.field2
psql-# FROM table1
psql-# LEFT JOIN LATERAL (
psql-#    SELECT field2 FROM table2
psql-#    WHERE table2.field1 = table1.field1) AS subquery1
psql-#    ON TRUE;

LATERAL ���w�肵�����₢���킹�� �ォ��]�������̂� ���₢���킹�̒��� FROM ��̖₢���킹�̗���g�p���邱�Ƃ��ł��܂��B

���ꂾ������ �P�ɕςȏ��������ł��� �����Ȃ̂ł��� ��ŕ]������邱�Ƃ� �`���[�j���O�ɖ𗧂ꍇ������܂��B

�Ⴆ�� �����i100���j�ƌo��ׁi500�����j���� �������Ƃ̌o����擾���܂��B

psql=# SELECT ALL
psql-#     ����.����ID
psql-# ,   �����o��.���v���z
psql-# ,   �����o��.���׌���
psql-# FROM
psql-#     ����
psql-# LEFT JOIN
psql-#     (
psql-#         SELECT ALL
psql-#             ����ID
psql-#         ,   SUM(���z) AS ���v���z
psql-#         ,   COUNT(*) AS ���׌���
psql-#         FROM
psql-#             �o���
psql-#         GROUP BY
psql-#             ����ID
psql-#     ) AS �����o��
psql-#     ON
psql-#         (
psql-#             �����o��.����ID = ����.����ID
psql-#         );

�����炭����� SQL �ɂȂ�Ǝv���܂��B
�������‹��ł� 5,391ms �ł����B

LATERAL ���g���Ǝ��̂悤�ɏ����܂��B

psql=# SELECT ALL
psql-#     ����.����ID
psql-# ,   ����.������
psql-# ,   �����o��.���v���z
psql-# ,   �����o��.���׌���
psql-# FROM
psql-#     ����
psql-# LEFT JOIN
psql-#     LATERAL
psql-#     (
psql-#         SELECT ALL
psql-#             SUM(���z) AS ���v���z
psql-#         ,   COUNT(*) AS ���׌���
psql-#         FROM
psql-#             �o���
psql-#         WHERE
psql-#             �o���.����ID = ����.����ID
psql-#     ) AS �����o��
psql-#     ON
psql-#         TRUE;

���͂��̏ꍇ�͋t�ɒx���Ȃ��� 21,614 ms �ł����B 1�s���•]������������Ԃ�������悤�ł��B

�L�[���ڂł͂Ȃ��������������ɒlj����܂��B
�܂� LATERAL ���g��Ȃ� SQL ����B

psql=# SELECT ALL
psql-#     ����.����ID
psql-# ,   �����o��.���v���z
psql-# ,   �����o��.���׌���
psql-# FROM
psql-#     ����
psql-# LEFT JOIN
psql-#     (
psql-#         SELECT ALL
psql-#             ����ID
psql-#         ,   SUM(���z) AS ���v���z
psql-#         ,   COUNT(*) AS ���׌���
psql-#         FROM
psql-#             �o���
psql-#         GROUP BY
psql-#             ����ID
psql-#     ) AS �����o��
psql-#     ON
psql-#         (
psql-#             �����o��.����ID = ����.����ID
psql-#         )
psql-# WHERE
psql-#     ����.������ = '�V�X�e���P��';
Hash Right Join (actual time=3314.571..3314.574 rows=1 loops=1)
  Hash Cond: ((t_kj_keihi_meisai.����ID)::text = (����.����ID)::text)
  ->  HashAggregate (actual time=3314.411..3314.441 rows=141 loops=1)
        ->  Seq Scan on t_kj_keihi_meisai (actual time=0.121..716.138 rows=5021645 loops=1)
  ->  Hash (actual time=0.089..0.089 rows=1 loops=1)
        Buckets: 1024  Batches: 1  Memory Usage: 1kB
        ->  Seq Scan on r_kj_jigyousho ���� (actual time=0.022..0.085 rows=1 loops=1)
              Filter: (������ = '�V�X�e���P��'::text)
              Rows Removed by Filter: 99

����� 3,314ms �ł����B

���� LATERAL �o�[�W�����B

psql=# SELECT ALL
psql-#     ����.����ID
psql-# ,   ����.������
psql-# ,   �����o��.���v���z
psql-# ,   �����o��.���׌���
psql-# FROM
psql-#     ����
psql-# LEFT JOIN
psql-#     LATERAL
psql-#     (
psql-#         SELECT ALL
psql-#             SUM(���z) AS ���v���z
psql-#         ,   COUNT(*) AS ���׌���
psql-#         FROM
psql-#             �o���
psql-#         WHERE
psql-#             �o���.����ID = ����.����ID
psql-#     ) AS �����o��
psql-#     ON
psql-#         TRUE
psql-# WHERE
psql-#     ����.������ = '�V�X�e���P��';
Nested Loop (actual time=221.481..221.523 rows=1 loops=1)
  ->  Seq Scan on r_kj_jigyousho ���� (actual time=0.029..0.070 rows=1 loops=1)
        Filter: (������ = '�V�X�e���P��'::text)
        Rows Removed by Filter: 99
  ->  Aggregate (actual time=221.448..221.449 rows=1 loops=1)
        ->  Bitmap Heap Scan on t_kj_keihi_meisai �o��� (actual time=217.928..219.075 rows=4633 loops=1)
              Recheck Cond: ((����ID)::text = (����.����ID)::text)
              ->  Bitmap Index Scan on t_yj_keihi_meisai_idx1 (actual time=217.882..217.882 rows=4633 loops=1)
                    Index Cond: ((����ID)::text = (����.����ID)::text)

������� 221ms �ł����B 10�{�ȏ㑬���Ȃ��Ă��܂��B

���̂悤�ɏꍇ�ɂ���Ă� ���Ȃ葬���Ȃ邱�Ƃ�����܂��B

�܂� LATERAL ���w�肵���ꍇ FROM �̌��ʂ̍s�ɑ΂��� 1�s���•]������邽�� INNER JOIN ���w�肵���ꍇ�ł��O�������̂悤�� ���̌��ʂ̍s��S�ĕԂ��̂Œ��ӂ��K�v�ł��B

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

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