
База и таблица
~~~~~~~~~~~~~~

=> create database db15;
CREATE DATABASE

=> \c db15
You are now connected to database "db15" as user "postgres".

=> create table t(a integer, b integer, s varchar(1000));
CREATE TABLE

=> insert into t(a,b,s) select (random()*99)::integer, (random()*999999)::integer, repeat('x',1000) from generate_series(1,1000000);
INSERT 0 1000000

=> create index on t(a);
CREATE INDEX

=> vacuum analyze t;
VACUUM

=> set enable_bitmapscan=off;
SET

Распределение данных:

=> select count(distinct a), count(distinct b) from t;
 count | count  
-------+--------
   100 | 632462
(1 row)



Выборка 1%
~~~~~~~~~~

=> explain analyze select * from t where a = 1;
                                                        QUERY PLAN                                                        
--------------------------------------------------------------------------------------------------------------------------
 Index Scan using t_a_idx on t  (cost=0.42..33494.90 rows=8567 width=1012) (actual time=1.355..954.892 rows=9949 loops=1)
   Index Cond: (a = 1)
 Planning time: 1.990 ms
 Execution time: 967.325 ms
(4 rows)


Запретим index scan.

=> set enable_indexscan=off;
SET

=> explain analyze select * from t where a = 1;
                                                 QUERY PLAN                                                 
------------------------------------------------------------------------------------------------------------
 Seq Scan on t  (cost=0.00..155357.94 rows=8567 width=1012) (actual time=2.129..3273.896 rows=9949 loops=1)
   Filter: (a = 1)
   Rows Removed by Filter: 990051
 Planning time: 0.059 ms
 Execution time: 3277.879 ms
(5 rows)


Для небольшой выборки доступ по индексу эффективнее.

=> set enable_indexscan=on;
SET


Выборка 50%
~~~~~~~~~~~

=> explain analyze select * from t where a < 50;
                                                   QUERY PLAN                                                   
----------------------------------------------------------------------------------------------------------------
 Seq Scan on t  (cost=0.00..155357.94 rows=499964 width=1012) (actual time=0.433..3748.945 rows=499609 loops=1)
   Filter: (a < 50)
   Rows Removed by Filter: 500391
 Planning time: 0.061 ms
 Execution time: 3884.198 ms
(5 rows)


Запретим seq scan.

=> set enable_seqscan=off;
SET

=> explain analyze select * from t where a < 50;
                                                           QUERY PLAN                                                            
---------------------------------------------------------------------------------------------------------------------------------
 Index Scan using t_a_idx on t  (cost=0.42..584524.04 rows=499964 width=1012) (actual time=1.036..59454.088 rows=499609 loops=1)
   Index Cond: (a < 50)
 Planning time: 0.086 ms
 Execution time: 59625.412 ms
(4 rows)


Для большой выборки полное сканирование может оказаться более выгодным.

=> set enable_seqscan=on;
SET


Порядок сортировки при создании индекса
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Порядок важен для многоколоночных индексов.
Построим индекс по столбцам a и b:

=> create index on t(a, b);
CREATE INDEX

Индекс может использоваться для такого запроса:

=> explain select * from t where a < 1 order by a, b;
                                 QUERY PLAN                                  
-----------------------------------------------------------------------------
 Index Scan using t_a_b_idx on t  (cost=0.42..21229.12 rows=5367 width=1012)
   Index Cond: (a < 1)
(2 rows)


И для такого:

=> explain select * from t where a < 1 order by a desc, b desc;
                                      QUERY PLAN                                      
--------------------------------------------------------------------------------------
 Index Scan Backward using t_a_b_idx on t  (cost=0.42..21229.12 rows=5367 width=1012)
   Index Cond: (a < 1)
(2 rows)


Но не для такого (сортировка в разных направлениях):

=> explain select * from t where a < 1 order by a, b desc;
                                   QUERY PLAN                                    
---------------------------------------------------------------------------------
 Sort  (cost=23925.15..23938.56 rows=5367 width=1012)
   Sort Key: a, b DESC
   ->  Index Scan using t_a_idx on t  (cost=0.42..21216.16 rows=5367 width=1012)
         Index Cond: (a < 1)
(4 rows)


Здесь приходится отдельно выполнять сортировку результатов.
В этом случае поможет другой индекс:

=> create index t_a_b_desc on t(a, b desc);
CREATE INDEX

=> explain select * from t where a < 1 order by a, b desc;
                                  QUERY PLAN                                  
------------------------------------------------------------------------------
 Index Scan using t_a_b_desc on t  (cost=0.42..21229.12 rows=5367 width=1012)
   Index Cond: (a < 1)
(2 rows)


А также для запроса с обратным порядком сортировки:

=> explain select * from t where a < 1 order by a desc, b;
                                      QUERY PLAN                                       
---------------------------------------------------------------------------------------
 Index Scan Backward using t_a_b_desc on t  (cost=0.42..21229.12 rows=5367 width=1012)
   Index Cond: (a < 1)
(2 rows)


=> \q
