Вопрос
Почему в запросе (Смотри в конце план запроса и сам запрос) не используется Index Only Scan?
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
Структура таблиц
\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 запрос
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
Предварительно заставляю использовать индекс
SET enable_seqscan TO off;
План запроса
< 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
Средний
