Why is Index Only Scan not being used?

Question

PostgreSQL

Question
Why is Index Only Scan not used in the query (See the query plan and the query itself at the end)?

SELECT version();
-[ RECORD 1 ]-------------------------------------------------------------------------------------
version | PostgreSQL 9.5.4 on x86_64-pc-linux-gnu, compiled by gcc (Debian 4.9.2-10) 4.9.2, 64-bit

Table structure

\d oli_products;
                      Table "public.oli_products"
    Column    |           Type           |          Modifiers
--------------+--------------------------+------------------------------
 oli_id       | uuid                     | not null
 order_id     | uuid                     | not null
 product_id   | uuid                     | not null
 product_data | jsonb                    | not null default '{}'::jsonb
 list_price   | bigint                   | not null
 sell_price   | bigint                   | not null
 count        | integer                  | not null
 status       | text                     | not null
 status_code  | text                     |
 updated_at   | timestamp with time zone | not null default now()
 created_at   | timestamp with time zone | not null
Indexes:
    "oli_products_pkey" PRIMARY KEY, btree (oli_id)
    "olip_oid_idx" btree (order_id, oli_id, status)
Check constraints:
    "oli_products_list_price_check" CHECK (list_price >= 0)
    "oli_products_sell_price_check" CHECK (sell_price >= 0)

\d oli_productschecks;
                Table "public.oli_productschecks"
   Column    |           Type           |       Modifiers
-------------+--------------------------+------------------------
 oli_id      | uuid                     | not null
 order_id    | uuid                     | not null
 provider_id | uuid                     | not null
 task_id     | uuid                     |
 list_price  | bigint                   | not null
 sell_price  | bigint                   | not null
 count       | integer                  | not null
 status      | text                     | not null
 status_code | text                     |
 updated_at  | timestamp with time zone | not null default now()
 created_at  | timestamp with time zone | not null
Indexes:
    "oli_productschecks_pkey" PRIMARY KEY, btree (oli_id)
    "olipc_oid_idx" btree (order_id, oli_id, status)
Check constraints:
    "oli_productschecks_list_price_check" CHECK (list_price >= 0)
    "oli_productschecks_sell_price_check" CHECK (sell_price >= 0)

SQL query

SELECT order_id, oli_id, status, count(status) FROM (
    SELECT order_id, oli_id, status FROM oli_products
    WHERE order_id = '28148411-78cf-4368-b7d9-c65d9d7ffd8e' 

    UNION ALL 
    
    SELECT order_id, oli_id, status FROM oli_productschecks
    WHERE order_id = '28148411-78cf-4368-b7d9-c65d9d7ffd8e'
    ) as order_line_items
    
  GROUP BY order_id, oli_id, status

I pre-force to use the index
SET enable_seqscan TO off;
Query Plan

< status FROM oli_productschecks^JWHERE order_id = '28148411-78cf-4368-b7d9-c65d9d7ffd8e'^J) as order_line_items^JGROUP BY order_id, oli_id, status;
-[ RECORD 1 ]--------------------------------------------------------------------------------------------------------------------------------------------------
QUERY PLAN | HashAggregate  (cost=14.56..14.63 rows=7 width=41) (actual time=0.034..0.036 rows=5 loops=1)
-[ RECORD 2 ]--------------------------------------------------------------------------------------------------------------------------------------------------
QUERY PLAN |   Group Key: oli_products.order_id, oli_products.oli_id, oli_products.status
-[ RECORD 3 ]--------------------------------------------------------------------------------------------------------------------------------------------------
QUERY PLAN |   ->  Append  (cost=4.19..14.49 rows=7 width=41) (actual time=0.018..0.024 rows=7 loops=1)
-[ RECORD 4 ]--------------------------------------------------------------------------------------------------------------------------------------------------
QUERY PLAN |         ->  Bitmap Heap Scan on oli_products  (cost=4.19..6.26 rows=6 width=37) (actual time=0.018..0.020 rows=5 loops=1)
-[ RECORD 5 ]--------------------------------------------------------------------------------------------------------------------------------------------------
QUERY PLAN |               Recheck Cond: (order_id = '28148411-78cf-4368-b7d9-c65d9d7ffd8e'::uuid)
-[ RECORD 6 ]--------------------------------------------------------------------------------------------------------------------------------------------------
QUERY PLAN |               Heap Blocks: exact=1
-[ RECORD 7 ]--------------------------------------------------------------------------------------------------------------------------------------------------
QUERY PLAN |               ->  Bitmap Index Scan on olip_oid_idx  (cost=0.00..4.19 rows=6 width=0) (actual time=0.012..0.012 rows=5 loops=1)
-[ RECORD 8 ]--------------------------------------------------------------------------------------------------------------------------------------------------
QUERY PLAN |                     Index Cond: (order_id = '28148411-78cf-4368-b7d9-c65d9d7ffd8e'::uuid)
-[ RECORD 9 ]--------------------------------------------------------------------------------------------------------------------------------------------------
QUERY PLAN |         ->  Index Only Scan using olipc_oid_idx on oli_productschecks  (cost=0.14..8.15 rows=1 width=64) (actual time=0.004..0.004 rows=2 loops=1)
-[ RECORD 10 ]-------------------------------------------------------------------------------------------------------------------------------------------------
QUERY PLAN |               Index Cond: (order_id = '28148411-78cf-4368-b7d9-c65d9d7ffd8e'::uuid)
-[ RECORD 11 ]-------------------------------------------------------------------------------------------------------------------------------------------------
QUERY PLAN |               Heap Fetches: 2
-[ RECORD 12 ]-------------------------------------------------------------------------------------------------------------------------------------------------
QUERY PLAN | Planning time: 5.174 ms
-[ RECORD 13 ]-------------------------------------------------------------------------------------------------------------------------------------------------
QUERY PLAN | Execution time: 0.469 ms

Answer 1

https://wiki.postgresql.org/wiki/Index-only_scans
Попробуйте сделать VACUUM ANALYZE