explain.depesz.com

PostgreSQL's explain analyze made readable

Result: BPsk : Optimization for: Optimization for: plan #x38Q; plan #BUZl

Settings

Optimization path:

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 0.001 0.797 ↑ 10.0 1 1

Limit (cost=2,610.36..2,610.38 rows=10 width=2,961) (actual time=0.797..0.797 rows=1 loops=1)

2.          

CTE filtered_orders

3. 0.009 0.101 ↑ 463.0 1 1

HashAggregate (cost=678.55..683.18 rows=463 width=549) (actual time=0.100..0.101 rows=1 loops=1)

  • Group Key: o1.id, o1.deleted, o1.id_ek5, o1.number
4. 0.001 0.092 ↑ 231.5 2 1

Append (cost=0.56..673.92 rows=463 width=549) (actual time=0.032..0.092 rows=2 loops=1)

5. 0.032 0.032 ↑ 211.0 1 1

Index Scan using idx_order_sender_code_date_deleted_ex on "order" o1 (cost=0.56..243.44 rows=211 width=43) (actual time=0.032..0.032 rows=1 loops=1)

  • Index Cond: (((sender_contragent_code)::text = '56090b33d83467dd84bd15ce'::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. 0.047 0.047 ↓ 0.0 0 1

Index Scan using idx_order_receiver_code on "order" o1_1 (cost=0.56..180.10 rows=41 width=43) (actual time=0.047..0.047 rows=0 loops=1)

  • Index Cond: ((receiver_contragent_code)::text = '56090b33d83467dd84bd15ce'::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: 13
7. 0.012 0.012 ↑ 211.0 1 1

Index Scan using idx_order_sender_code_date_deleted_ex on "order" o1_2 (cost=0.56..243.44 rows=211 width=43) (actual time=0.012..0.012 rows=1 loops=1)

  • Index Cond: (((sender_contragent_code)::text = '56090b33d83467dd84bd15ce'::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)
8. 0.024 0.796 ↑ 469.0 1 1

Sort (cost=1,927.18..1,928.35 rows=469 width=2,961) (actual time=0.796..0.796 rows=1 loops=1)

  • Sort Key: o.date_time_create DESC
  • Sort Method: quicksort Memory: 25kB
9. 0.241 0.772 ↑ 469.0 1 1

Nested Loop Left Join (cost=1,347.04..1,917.04 rows=469 width=2,961) (actual time=0.706..0.772 rows=1 loops=1)

10. 0.057 0.519 ↑ 466.0 1 1

Nested Loop Left Join (cost=1,346.61..1,642.50 rows=466 width=3,428) (actual time=0.454..0.519 rows=1 loops=1)

11. 0.003 0.456 ↑ 463.0 1 1

Nested Loop Left Join (cost=1,346.17..1,377.87 rows=463 width=3,542) (actual time=0.390..0.456 rows=1 loops=1)

  • Join Filter: (o.order_additional_status_code = delivery_detail_additional_status.code)
  • Rows Removed by Join Filter: 27
12. 0.013 0.425 ↑ 463.0 1 1

Hash Left Join (cost=1,346.03..1,368.57 rows=463 width=3,026) (actual time=0.360..0.425 rows=1 loops=1)

  • Hash Cond: (o.tariff_type_code = tariff_type.code)
13. 0.003 0.240 ↑ 463.0 1 1

Nested Loop Left Join (cost=1,319.12..1,340.42 rows=463 width=2,993) (actual time=0.175..0.240 rows=1 loops=1)

  • Join Filter: (o.order_status_code = order_status.code)
  • Rows Removed by Join Filter: 25
14. 0.011 0.155 ↑ 463.0 1 1

Hash Right Join (cost=1,318.98..1,329.99 rows=463 width=1,957) (actual time=0.152..0.155 rows=1 loops=1)

  • Hash Cond: (so.id_ek5 = o.straight_ek5_id)
15. 0.001 0.001 ↑ 463.0 1 1

CTE Scan on filtered_orders so (cost=0.00..9.26 rows=463 width=532) (actual time=0.000..0.001 rows=1 loops=1)

16. 0.004 0.143 ↑ 463.0 1 1

Hash (cost=1,313.19..1,313.19 rows=463 width=1,457) (actual time=0.143..0.143 rows=1 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
17. 0.015 0.139 ↑ 463.0 1 1

Hash Right Join (cost=1,302.19..1,313.19 rows=463 width=1,457) (actual time=0.137..0.139 rows=1 loops=1)

  • Hash Cond: (ro.id_ek5 = o.reverse_ek5_id)
18. 0.000 0.000 ↑ 463.0 1 1

CTE Scan on filtered_orders ro (cost=0.00..9.26 rows=463 width=532) (actual time=0.000..0.000 rows=1 loops=1)

19. 0.008 0.124 ↑ 463.0 1 1

Hash (cost=1,296.40..1,296.40 rows=463 width=957) (actual time=0.124..0.124 rows=1 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
20. 0.003 0.116 ↑ 463.0 1 1

Nested Loop (cost=0.56..1,296.40 rows=463 width=957) (actual time=0.114..0.116 rows=1 loops=1)

21. 0.102 0.102 ↑ 463.0 1 1

CTE Scan on filtered_orders t (cost=0.00..9.26 rows=463 width=16) (actual time=0.101..0.102 rows=1 loops=1)

22. 0.011 0.011 ↑ 1.0 1 1

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

  • Index Cond: (id = t.id)
23. 0.011 0.082 ↓ 26.0 26 1

Materialize (cost=0.14..3.49 rows=1 width=1,040) (actual time=0.021..0.082 rows=26 loops=1)

24. 0.029 0.071 ↓ 26.0 26 1

Nested Loop Left Join (cost=0.14..3.49 rows=1 width=1,040) (actual time=0.018..0.071 rows=26 loops=1)

  • Join Filter: (order_status."groupCode" = order_status_group.code)
  • Rows Removed by Join Filter: 29
25. 0.016 0.016 ↓ 26.0 26 1

Index Scan using "idx-order_status-lang" on order_status (cost=0.14..2.36 rows=1 width=524) (actual time=0.009..0.016 rows=26 loops=1)

  • Index Cond: ((lang)::text = 'rus'::text)
26. 0.026 0.026 ↓ 2.0 2 26

Seq Scan on order_status_group (cost=0.00..1.11 rows=1 width=520) (actual time=0.001..0.001 rows=2 loops=26)

  • Filter: ((lang)::text = 'rus'::text)
  • Rows Removed by Filter: 3
27. 0.056 0.172 ↑ 1.0 282 1

Hash (cost=23.38..23.38 rows=282 width=41) (actual time=0.172..0.172 rows=282 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 36kB
28. 0.116 0.116 ↑ 1.0 282 1

Index Scan using "idx-tariff_type-lang" on tariff_type (cost=0.28..23.38 rows=282 width=41) (actual time=0.011..0.116 rows=282 loops=1)

  • Index Cond: ((lang)::text = 'rus'::text)
29. 0.009 0.028 ↓ 27.0 27 1

Materialize (cost=0.14..2.37 rows=1 width=520) (actual time=0.014..0.028 rows=27 loops=1)

30. 0.019 0.019 ↓ 27.0 27 1

Index Scan using "idx-delivery_detail_additional_status-lang" on delivery_detail_additional_status (cost=0.14..2.36 rows=1 width=520) (actual time=0.011..0.019 rows=27 loops=1)

  • Index Cond: ((lang)::text = 'rus'::text)
31. 0.006 0.006 ↑ 1.0 1 1

Index Scan using "idx-city-code-lang" on city rc (cost=0.43..0.57 rows=1 width=29) (actual time=0.006..0.006 rows=1 loops=1)

  • Index Cond: ((code = (((o.receiver)::json #>> '{address,cityCode}'::text[]))::integer) AND ((lang)::text = 'rus'::text))
32. 0.012 0.012 ↑ 1.0 1 1

Index Scan using "idx-city-code-lang" on city sc (cost=0.43..0.57 rows=1 width=29) (actual time=0.012..0.012 rows=1 loops=1)

  • Index Cond: ((code = (((o.sender)::json #>> '{address,cityCode}'::text[]))::integer) AND ((lang)::text = 'rus'::text))
Planning time : 9.752 ms
Execution time : 1.188 ms