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