Chapter 13. ������������

Table of Contents
13.1. ʹ�� EXPLAIN
13.2. �滮��ʹ�õ�ͳ����Ϣ
13.3. ����ȷ�� JOIN �����ӣ����ƹ滮��
13.4. �����ݿ�����Ӽ�¼
13.4.1. �ر��Զ��ύ
13.4.2. ʹ�� COPY
13.4.3. ɾ������
13.4.4. ���� maintenance_work_mem
13.4.5. ���� checkpoint_segments
13.4.6. �º�����ANALYZE

��ѯ�����ܿ����ܶ�������Ӱ�졣 ����һЩ���ؿ������û����ݣ����������������²�ϵͳ��ƵĻ��������ˡ� ���������ṩһЩ�й����͵��� PostgreSQL ���ܵ�������

13.1. ʹ�� EXPLAIN

PostgreSQL Ϊ������ÿ����ѯ����һ����ѯ�滮�� Ϊƥ���ѯ�ṹ����������ѡ����ȷ�Ĺ滮�����ܾ����йؼ��Ե�Ӱ�졣 �����ʹ�� EXPLAIN ����쿴ϵͳΪÿ����ѯ���ɵIJ�ѯ�滮��ʲô�� �Ķ���ѯ�滮��һ��ֵ��дһ���൱���Ľ̵̳�ѧ�ʣ� ��������ĵ��ɲ��������Ľ̳̣�����������һЩ��������Ϣ��

Ŀǰ�� EXPLAN ���õ������ǣ�

�������Դ���ҳ��Ĵ�ȡΪ��λ����ġ� ��Ԥ�Ƶ� CPU ������һЩ�dz�����������Ȩֵ��ת���ɴ���ҳ�浥λ�� �������������Щ������������� Section 16.4.5.2 �������ʱ�����б���

��һ�����Ҫ���Ǿ���һ���ϲ�ڵ�Ŀ����������������ӽڵ�Ŀ����� ����һ��Ҳ����Ҫ�������������ֻ��ӳ�滮��/�Ż������ĵĶ����� �����ǿ���û�аѽ���д��ݸ�ǰ�˵�ʱ�俼�ǽ�ȥ�� ���ʱ���������������ʱ������ռ���൱��Ҫ�ķ����� ���DZ��滮�������ˣ���Ϊ���޷�ͨ���޸Ĺ滮���ı�֮�� ���������ţ�ÿ����ȷ�Ĺ滮�������ͬ���ļ�¼������

�����������һЩС���ɣ���Ϊ��������ѯ����/ɨ�����������ͨ������һЩ�� ��ӳ��Ӧ���ڴ˽ڵ��ϵ�����WHERE�Ӿ�������ѡ���Թ��ơ� ͨ�����ԣ��������Ԥ�ƻ�ӽ��ڲ�ѯʵ�ʷ��أ����£���ɾ����������

�����Ǽ������ӣ��õ��Ǿ��� VACUUM ANALYZE ��Ļع�������ݿ��Լ� 7.3 �Ŀ������룩��

EXPLAIN SELECT * FROM tenk1;
                         QUERY PLAN
-------------------------------------------------------------
 Seq Scan on tenk1  (cost=0.00..333.00 rows=10000 width=148)

������Ӿ������ӱ���һ��ֱ���˵����������һ��

SELECT * FROM pg_class WHERE relname = 'tenk1';

��ᷢ��tenk1�� 233 ����ҳ��� 10000 �С� ��˿�������Ϊ 233 ��ҳ���ȡ������Ϊÿ�� 1.0�� ���� 10000 * cpu_tuple_cost��Ŀǰ�� 0.01�������� SHOW cpu_tuple_cost �鿴����

�����������޸IJ�ѯ������һ��WHERE������

EXPLAIN SELECT * FROM tenk1 WHERE unique1 < 1000;

                         QUERY PLAN
------------------------------------------------------------
 Seq Scan on tenk1  (cost=0.00..358.00 rows=1033 width=148)
   Filter: (unique1 < 1000)

Ԥ�Ƶ�������������ˣ���Ϊ��WHERE�Ӿ䡣 ������ɨ���Խ������������ 10000 �У���˿���û�н��ͣ� ʵ��������������һЩ�Է�ӳ���WHERE�����Ķ��� CPU ʱ�䡣

������ѯʵ��ѡ��������� 1000������Ԥ�Ƶ���Ŀֻ�Ǹ���š� �������ͼ�ظ�������飬��ô��ܿ��ܵõ���Щ��ͬ��Ԥ�ƣ� ���У����Ԥ�ƻ���ÿ�� ANALYZE ����֮��ı䣬 ��Ϊ ANALYZE ���ɵ�ͳ���ǴӸñ��������ȡ����������ġ�

�Ѳ�ѯ�޸�Ϊ�����������ϸ�

EXPLAIN SELECT * FROM tenk1 WHERE unique1 < 50;

                                   QUERY PLAN
-------------------------------------------------------------------------------
 Index Scan using tenk1_unique1 on tenk1  (cost=0.00..179.33 rows=49 width=148)
   Index Cond: (unique1 < 50)

��ʱ��ῴ����������ǰ�WHERE��������㹻��ѡ���ԣ� �滮�������վ���һ������ɨ�轫��һ��˳��ɨ��졣 ��Ϊ������������滮��ֻ��Ҫ���� 50 ����¼�� ��˾���ÿ����¼������ץȡ��˳���ȡ��������ҳ��Ŀ����� ���������ѯ�滮������ʤ����

��WHERE�Ӿ�������������һ��������

EXPLAIN SELECT * FROM tenk1 WHERE unique1 < 50 AND stringu1 = 'xxx';

                                  QUERY PLAN
-------------------------------------------------------------------------------
 Index Scan using tenk1_unique1 on tenk1  (cost=0.00..179.45 rows=1 width=148)
   Index Cond: (unique1 < 50)
   Filter: (stringu1 = 'xxx'::name)

���������� stringu1 = 'xxx' ������Ԥ�Ƶ�����У� ����û�м��ٿ�������Ϊ������Ȼ��Ҫ������ͬ���С� ��ע�� stringu1 �Ӿ䲻�ܵ���һ����������ʩ�� ����Ϊ�������ֻ���� unique1 �����У��� ���ǵ���һ���������м��������еĹ��������õġ� ��˿���ʵ������΢������һЩ�Է�ӳ�������ļ�顣

����������ʹ�������������۵��ֶ�����������

EXPLAIN SELECT * FROM tenk1 t1, tenk2 t2 WHERE t1.unique1 < 50 AND t1.unique2 = t2.unique2;

                               QUERY PLAN
----------------------------------------------------------------------------
 Nested Loop  (cost=0.00..327.02 rows=49 width=296)
   ->  Index Scan using tenk1_unique1 on tenk1 t1
                                      (cost=0.00..179.33 rows=49 width=148)
         Index Cond: (unique1 < 50)
   ->  Index Scan using tenk2_unique2 on tenk2 t2
                                      (cost=0.00..3.01 rows=1 width=148)
         Index Cond: ("outer".unique2 = t2.unique2)

�����Ƕ��ѭ����������ɨ�������ǰһ��������һ���ģ� ������Ŀ�����������һ���ģ���Ϊ���Ƕ��Ǹ��ڵ�Ӧ����WHERE�Ӿ� unique1 < 50�� t1.unique2 = t2.unique2 ��ʱ������أ� �����û��Ӱ�����ɨ����м����� �����ڲ�ɨ�裬 Ŀǰ�����ɨ���е�unique2ֵ�����뵽�ڲ�����ɨ��������һ���� t2.unique2 = constant �����������������������Ǿ͵õ���������Ҫ�ĺͲ�ѯ EXPLAIN SELECT * FROM tenk2 WHERE unique2 = 42 ͬ�����ڲ�ɨ��滮�Ϳ����� Ȼ���������ɨ��Ŀ���Ϊ��������ѭ���ڵ�Ŀ����� ����һ��Ϊÿ�������ɨ���ظ����ڲ�ɨ�裨������ 49 * 3.01���� �ټ���һ��㴦�����ӵ� CPU ʱ�䡣

�������������ӵ��������������ɨ��������ij˻���ͬ�� ����ͨ�������������ģ���Ϊͨ��������ἰ�������WHERE�Ӿ䣬 �����ֻ��Ӧ�������ӣ�join���㣬������Ӱ��������ϵ������ɨ�衣 ���磬������Ǽ�һ�� WHERE ... AND t1.hundred < t2.hundred�� ������������������Dz��ı��κ�һ������ɨ�衣

Ѱ������һ���滮�ķ�����ͨ������ÿ�ֹ滮���͵�����/��ֹ���أ� ǿ�ƹ滮����������Ϊ����ģ�ɨ�裩���ԡ� ���������Ŀǰ�Ƚ�ԭʼ���������á��ּ�Section 13.3����

SET enable_nestloop = off;
EXPLAIN SELECT * FROM tenk1 t1, tenk2 t2 WHERE t1.unique1 < 50 AND t1.unique2 = t2.unique2;

                               QUERY PLAN
--------------------------------------------------------------------------
 Hash Join  (cost=179.45..563.06 rows=49 width=296)
   Hash Cond: ("outer".unique2 = "inner".unique2)
   ->  Seq Scan on tenk2 t2  (cost=0.00..333.00 rows=10000 width=148)
   ->  Hash  (cost=179.33..179.33 rows=49 width=148)
         ->  Index Scan using tenk1_unique1 on tenk1 t1
                                    (cost=0.00..179.33 rows=49 width=148)
               Index Cond: (unique1 < 50)

����滮��Ȼ��ͼ��ͬ��������ɨ���tenk1 ����ȡ������Ȥ�� 50 �У� �����Dz���һ�����ڴ����ɢ�У���ϣ�����Ȼ���tenk2 ��һ��˳��ɨ�裬��ÿһ��tenk2��¼��������ɢ�У���ϣ���� Ѱ�ҿ���ƥ��t1.unique2 = t2.unique2 ���С� ��ȡtenk1�ͽ���ɢ�б��Ǵ�ɢ�����ӵ�ȫ������������ ��Ϊ�����ڿ�ʼ��ȡtenk2 ֮ǰ�����ܻ���κ�����С� ������ӵ��ܵ�Ԥ��ʱ��ͬ���������൱�صļ��ɢ�У���ϣ���� 10000 �ε� CPU ʱ�䡣��������ע�⣬��������Ҫ�� 179.33 �� 10000�� ɢ�У���ϣ�����������滮������ֻ��Ҫ����һ�Ρ�

���ǿ�����EXPLAIN ANALYZE���滮���Ĺ���ֵ��׼ȷ�ԡ� �������ʵ����ִ�иò�ѯȻ����ʾÿ���滮�ڵ���ʵ������ʱ��ĺ��Լ�����EXPLAIN��ʾ�Ĺ��ƿ����� ���磬���ǿ���������������ȡһ�������

EXPLAIN ANALYZE SELECT * FROM tenk1 t1, tenk2 t2 WHERE t1.unique1 < 50 AND t1.unique2 = t2.unique2;

                                   QUERY PLAN
-------------------------------------------------------------------------------
 Nested Loop  (cost=0.00..327.02 rows=49 width=296)
                                 (actual time=1.18..29.82 rows=50 loops=1)
   ->  Index Scan using tenk1_unique1 on tenk1 t1
                  (cost=0.00..179.33 rows=49 width=148)
                                 (actual time=0.63..8.91 rows=50 loops=1)
         Index Cond: (unique1 < 50)
   ->  Index Scan using tenk2_unique2 on tenk2 t2
                  (cost=0.00..3.01 rows=1 width=148)
                                 (actual time=0.29..0.32 rows=1 loops=50)
         Index Cond: ("outer".unique2 = t2.unique2)
 Total runtime: 31.60 msec

��ע�� "actual time" ��ֵ������ʵʱ��ĺ���Ƶģ� �� "cost" ����ֵ�����������ץȡ�ĵ�Ԫ�Ƶģ� ������Ǻܿ��ܲ�һ�¡�����Ҫ���ĵ����������ֵ�Ƿ�һ�¡�

��һЩ��ѯ�滮�һ���ӹ滮�ڵ�ܿ������ж�Ρ� ���磬�������Ƕ��ѭ���Ĺ滮��ڲ������ɨ���Ƕ�ÿ�������ִ��һ�εġ� ����������£�"loops" ����ýڵ�ִ�е�����Ŀ�� ����ʾ��ʵ��ʱ�������Ŀ��ÿ��ִ�е�ƽ��ֵ�� ��ô����ԭ��������Щ�����뿪��Ԥ����ʾ�����־��пɱ��ԡ� Ҫ���� "loops" ֵ���ܻ���ڸýڵ�ʱ�仨�ѵ���ʱ�䡣

EXPLAIN ANALYZE ��ʾ�� "Total runtime" ����ִ���������͹رյ�ʱ�䣬 �Լ����ڴ��������ϵ�ʱ�䡣����������������д�����߹滮��ʱ�䡣 ����SELECT��ѯ��������ʱ��ͨ��ֻ�DZȴӶ���滮�ڵ�㱨��������ʱ����΢��Щ�� ����INSERT��UPDATE���� DELETE ��ѯ�� ������ʱ����ܻ�����������Ϊ�����������ڴ��������ϵ�ʱ�䡣 ����Щ��ѯ�����滮�ڵ��ʱ��ʵ�����ǻ��ڼ������к�/��λ�����ϵ�ʱ�䣬���Dz���������ִ�иĶ��ϵ�ʱ�䡣

���EXPLAIN�Ľ����������ʵ�ʲ��Ե����֮�ⲻ���Ƶ�������������� ������ʲô�ö�û�У����磬��һ��С������ߵı��ϵĽ�����������ڴ�� �滮���Ŀ������㲻�����Եģ�������ܿ��ܶԴ�Щ����СЩ�ı�ѡ��ͬ�Ĺ滮�� һ�����˵�������һ��ֻռ��һ������ҳ��ı��������ı��ϣ���������û����������ʹ�ã� �㼸�������ǵõ�˳��ɨ��滮���滮��֪���������κ����������Ҫ����һ������ҳ��Ķ�ȡ�� ���������󼸸�����ҳ���ȡ�Բ���������û������ġ�