explain.depesz.com

PostgreSQL's explain analyze made readable

Result: XH5O

Settings
# exclusive inclusive rows x rows loops node
1. 0.002 167,222.206 ↓ 0.0 0 1

Limit (cost=190,650.31..190,650.33 rows=8 width=1,507) (actual time=167,222.206..167,222.206 rows=0 loops=1)

2.          

CTE filtered_orders

3. 0.006 167,221.989 ↑ 1.0 10 1

Limit (cost=190,605.76..190,605.78 rows=10 width=589) (actual time=167,221.982..167,221.989 rows=10 loops=1)

4. 160.768 167,221.983 ↑ 15,770.9 10 1

Sort (cost=190,605.76..191,000.03 rows=157,709 width=589) (actual time=167,221.981..167,221.983 rows=10 loops=1)

  • Sort Key: o_1.date_time_create DESC
  • Sort Method: top-N heapsort Memory: 34kB
5. 3,682.239 167,061.215 ↓ 1.6 252,376 1

HashAggregate (cost=184,043.54..185,620.63 rows=157,709 width=589) (actual time=166,817.852..167,061.215 rows=252,376 loops=1)

  • Group Key: o_1.id, o_1.deleted, o_1.id_ek5, o_1.number, o_1.online_shop, o_1.date_time_create
6. 219.437 163,378.976 ↓ 2.0 318,959 1

Append (cost=0.56..181,677.91 rows=157,709 width=589) (actual time=0.047..163,378.976 rows=318,959 loops=1)

7. 150,007.853 150,007.853 ↓ 1.7 243,148 1

Index Scan Backward using idx_order_sender_code_number_department on "order" o_1 (cost=0.56..163,684.60 rows=144,053 width=248) (actual time=0.046..150,007.853 rows=243,148 loops=1)

  • Index Cond: (((sender_contragent_code)::text = '60d0be99-bb38-4b4e-9d5f-b300b9be49ba'::text) AND (deleted = false) AND (date_time_create >= '2019-09-04 00:00:00'::timestamp without time zone) AND (date_time_create <= '2019-10-04 23:59:59'::timestamp without time zone))
  • Filter: (NOT deleted)
8. 5,282.833 5,282.833 ↓ 2.1 9,221 1

Index Scan Backward using idx_order_receiver_code_number_department on "order" o_2 (cost=0.56..5,039.97 rows=4,418 width=248) (actual time=1.773..5,282.833 rows=9,221 loops=1)

  • Index Cond: (((receiver_contragent_code)::text = '60d0be99-bb38-4b4e-9d5f-b300b9be49ba'::text) AND (deleted = false) AND (date_time_create >= '2019-09-04 00:00:00'::timestamp without time zone) AND (date_time_create <= '2019-10-04 23:59:59'::timestamp without time zone))
  • Filter: (NOT deleted)
9. 7,868.853 7,868.853 ↓ 7.2 66,590 1

Index Scan Backward using idx_order_payer_code_number_department on "order" o_3 (cost=0.56..10,587.70 rows=9,238 width=248) (actual time=0.185..7,868.853 rows=66,590 loops=1)

  • Index Cond: (((payer_contragent_code)::text = '60d0be99-bb38-4b4e-9d5f-b300b9be49ba'::text) AND (deleted = false) AND (date_time_create >= '2019-09-04 00:00:00'::timestamp without time zone) AND (date_time_create <= '2019-10-04 23:59:59'::timestamp without time zone))
  • Filter: (NOT deleted)
10. 0.052 167,222.204 ↓ 0.0 0 1

Sort (cost=44.53..44.55 rows=8 width=1,507) (actual time=167,222.204..167,222.204 rows=0 loops=1)

  • Sort Key: o.date_time_create DESC
  • Sort Method: quicksort Memory: 25kB
11. 0.000 167,222.152 ↓ 0.0 0 1

Nested Loop Left Join (cost=2.62..44.41 rows=8 width=1,507) (actual time=167,222.152..167,222.152 rows=0 loops=1)

12. 0.001 167,222.152 ↓ 0.0 0 1

Nested Loop Left Join (cost=2.20..39.79 rows=8 width=1,640) (actual time=167,222.152..167,222.152 rows=0 loops=1)

13. 0.002 167,222.151 ↓ 0.0 0 1

Hash Left Join (cost=1.78..35.32 rows=8 width=1,619) (actual time=167,222.151..167,222.151 rows=0 loops=1)

  • Hash Cond: (o.straight_ek5_id = so.id_ek5)
14. 0.001 167,222.149 ↓ 0.0 0 1

Hash Left Join (cost=1.45..34.96 rows=8 width=1,119) (actual time=167,222.149..167,222.149 rows=0 loops=1)

  • Hash Cond: (o.reverse_ek5_id = ro.id_ek5)
15. 0.001 167,222.148 ↓ 0.0 0 1

Nested Loop Left Join (cost=1.13..34.59 rows=8 width=619) (actual time=167,222.148..167,222.148 rows=0 loops=1)

16. 0.000 167,222.147 ↓ 0.0 0 1

Nested Loop Left Join (cost=0.98..33.29 rows=8 width=566) (actual time=167,222.147..167,222.147 rows=0 loops=1)

  • Join Filter: (order_status."groupCode" = order_status_group.code)
17. 0.000 167,222.147 ↓ 0.0 0 1

Nested Loop Left Join (cost=0.98..32.03 rows=8 width=554) (actual time=167,222.147..167,222.147 rows=0 loops=1)

18. 0.000 167,222.147 ↓ 0.0 0 1

Nested Loop (cost=0.71..29.65 rows=8 width=521) (actual time=167,222.147..167,222.147 rows=0 loops=1)

19. 0.020 167,222.118 ↑ 1.0 10 1

Nested Loop (cost=0.56..28.00 rows=10 width=484) (actual time=167,222.031..167,222.118 rows=10 loops=1)

20. 167,222.008 167,222.008 ↑ 1.0 10 1

CTE Scan on filtered_orders t (cost=0.00..0.20 rows=10 width=16) (actual time=167,221.996..167,222.008 rows=10 loops=1)

21. 0.090 0.090 ↑ 1.0 1 10

Index Scan using "idx-order_id" on "order" o (cost=0.56..2.78 rows=1 width=500) (actual time=0.009..0.009 rows=1 loops=10)

  • Index Cond: (id = t.id)
22. 0.030 0.030 ↓ 0.0 0 10

Index Scan using "idx-order_status-code-lang" on order_status (cost=0.14..0.17 rows=1 width=41) (actual time=0.003..0.003 rows=0 loops=10)

  • Index Cond: ((code = o.order_status_code) AND ((lang)::text = 'rus'::text))
  • Filter: ("groupCode" = 2)
  • Rows Removed by Filter: 1
23. 0.000 0.000 ↓ 0.0 0

Index Scan using "idx-tariff_type-code-lang" on tariff_type (cost=0.28..0.30 rows=1 width=41) (never executed)

  • Index Cond: ((o.tariff_type_code = code) AND ((lang)::text = 'rus'::text))
24. 0.000 0.000 ↓ 0.0 0

Materialize (cost=0.00..1.14 rows=1 width=16) (never executed)

25. 0.000 0.000 ↓ 0.0 0

Seq Scan on order_status_group (cost=0.00..1.14 rows=1 width=16) (never executed)

  • Filter: ((code = 2) AND ((lang)::text = 'rus'::text))
26. 0.000 0.000 ↓ 0.0 0

Index Scan using "idx-delivery_detail_additional_status-code-lang" on delivery_detail_additional_status (cost=0.14..0.16 rows=1 width=57) (never executed)

  • Index Cond: ((o.order_additional_status_code = code) AND ((lang)::text = 'rus'::text))
27. 0.000 0.000 ↓ 0.0 0

Hash (cost=0.20..0.20 rows=10 width=532) (never executed)

28. 0.000 0.000 ↓ 0.0 0

CTE Scan on filtered_orders ro (cost=0.00..0.20 rows=10 width=532) (never executed)

29. 0.000 0.000 ↓ 0.0 0

Hash (cost=0.20..0.20 rows=10 width=532) (never executed)

30. 0.000 0.000 ↓ 0.0 0

CTE Scan on filtered_orders so (cost=0.00..0.20 rows=10 width=532) (never executed)

31. 0.000 0.000 ↓ 0.0 0

Index Scan using "idx-city-code-lang" on city rc (cost=0.42..0.56 rows=1 width=29) (never executed)

  • Index Cond: ((code = o.receiver_city_code) AND ((lang)::text = 'rus'::text))
32. 0.000 0.000 ↓ 0.0 0

Index Scan using "idx-city-code-lang" on city sc (cost=0.42..0.56 rows=1 width=29) (never executed)

  • Index Cond: ((code = o.sender_city_code) AND ((lang)::text = 'rus'::text))
Planning time : 21.396 ms