explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Bjlf : Optimization for: Optimization for: Optimization for: Optimization for: Optimization for: plan #x38Q; plan #BUZl; plan #BPsk; plan #x1gn; plan #VRP7

Settings

Optimization path:

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 0.021 9,565.105 ↑ 1.0 1 1

Limit (cost=1,182,317.58..1,182,317.59 rows=1 width=8) (actual time=9,565.089..9,565.105 rows=1 loops=1)

2.          

CTE filtered_orders

3. 2,028.774 9,074.098 ↑ 1.3 785,003 1

HashAggregate (cost=1,150,023.47..1,159,960.12 rows=993,665 width=17) (actual time=8,136.563..9,074.098 rows=785,003 loops=1)

  • Group Key: o1.id, o1.deleted
4. 82.489 7,045.324 ↑ 1.0 978,382 1

Append (cost=0.56..1,145,055.14 rows=993,665 width=17) (actual time=0.068..7,045.324 rows=978,382 loops=1)

5. 972.554 972.554 ↑ 1.4 193,379 1

Index Scan using idx_order_sender_code_date_deleted_ex on "order" o1 (cost=0.56..296,115.94 rows=261,535 width=17) (actual time=0.067..972.554 rows=193,379 loops=1)

  • Index Cond: (((sender_contragent_code)::text = '60d0be99-bb38-4b4e-9d5f-b300b9be49ba'::text) AND (deleted = false) AND (date_time_create >= '2019-08-07 00:00:00'::timestamp without time zone) AND (date_time_create <= '2019-09-07 23:59:59'::timestamp without time zone))
  • Filter: (NOT deleted)
6. 171.435 171.435 ↑ 1.6 7,747 1

Index Scan using idx_order_receiver_code on "order" o1_1 (cost=0.56..53,901.08 rows=12,259 width=17) (actual time=13.368..171.435 rows=7,747 loops=1)

  • Index Cond: ((receiver_contragent_code)::text = '60d0be99-bb38-4b4e-9d5f-b300b9be49ba'::text)
  • Filter: ((NOT deleted) AND (date_time_create >= '2019-08-07 00:00:00'::timestamp without time zone) AND (date_time_create <= '2019-09-07 23:59:59'::timestamp without time zone))
  • Rows Removed by Filter: 28,852
7. 5,055.475 5,818.846 ↓ 1.1 777,256 1

Bitmap Heap Scan on "order" o1_2 (cost=28,446.60..780,133.14 rows=719,871 width=17) (actual time=1,118.781..5,818.846 rows=777,256 loops=1)

  • Recheck Cond: (((sender_contragent_code)::text = '60d0be99-bb38-4b4e-9d5f-b300b9be49ba'::text) AND (date_time_create >= '2019-04-07 00:00:00'::timestamp without time zone) AND (date_time_create <= '2019-09-07 23:59:59'::timestamp without time zone))
  • Filter: (NOT deleted)
  • Heap Blocks: exact=694,408
8. 763.371 763.371 ↓ 1.1 777,570 1

Bitmap Index Scan on idx_order_sender_code_date_deleted_ex (cost=0.00..28,266.63 rows=719,871 width=0) (actual time=763.371..763.371 rows=777,570 loops=1)

  • Index Cond: (((sender_contragent_code)::text = '60d0be99-bb38-4b4e-9d5f-b300b9be49ba'::text) AND (deleted = false) AND (date_time_create >= '2019-04-07 00:00:00'::timestamp without time zone) AND (date_time_create <= '2019-09-07 23:59:59'::timestamp without time zone))
9. 72.030 9,565.084 ↑ 1.0 1 1

Aggregate (cost=22,357.46..22,357.47 rows=1 width=8) (actual time=9,565.080..9,565.084 rows=1 loops=1)

10. 9,493.054 9,493.054 ↑ 1.3 785,003 1

CTE Scan on filtered_orders (cost=0.00..19,873.30 rows=993,665 width=0) (actual time=8,136.566..9,493.054 rows=785,003 loops=1)

Planning time : 1.733 ms
Execution time : 9,663.288 ms