explain.depesz.com

PostgreSQL's explain analyze made readable

Result: yhLv : Optimization for: Optimization for: Optimization for: Optimization for: Optimization for: Optimization for: Optimization for: Optimization for: Optimization for: Optimization for: Optimization for: Optimization for: Optimization for: Optimization for: Optimization for: Optimization for: Optimization for: Optimization for: Optimization for: Optimization for: Optimization for: Optimization for: Optimization for: Optimization for: plan #8tg; plan #9rN1; plan #KO6D; plan #Jn51; plan #5S8s; plan #rQtg; plan #e8ot; plan #x1v9; plan #60x7; plan #OmB0s; plan #kT8M; plan #hBBo; plan #PBsg; plan #jWRF; plan #vUhJ; plan #guMs; plan #tNcR; plan #AIoi; plan #7xDy; plan #i9ct; plan #aXXT; plan #W38d; plan #bZt4; plan #ECjq

Settings

Optimization path:

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 0.030 48,430.055 ↑ 1.0 1 1

Aggregate (cost=260,203.38..260,203.39 rows=1 width=8) (actual time=48,430.054..48,430.055 rows=1 loops=1)

2.          

CTE filtered_orders

3. 0.030 48,417.478 ↑ 1.0 20 1

Limit (cost=260,147.28..260,147.33 rows=20 width=621) (actual time=48,417.442..48,417.478 rows=20 loops=1)

4. 22.145 48,417.448 ↑ 9,969.8 20 1

Sort (cost=260,147.28..260,645.77 rows=199,395 width=621) (actual time=48,417.439..48,417.448 rows=20 loops=1)

  • Sort Key: (((t_1.online_shop)::json ->> 'numberDeparture'::text)) DESC
  • Sort Method: top-N heapsort Memory: 27kB
5. 14.885 48,395.303 ↑ 1.3 158,830 1

Subquery Scan on t_1 (cost=249,358.09..254,841.45 rows=199,395 width=621) (actual time=48,328.270..48,395.303 rows=158,830 loops=1)

6. 140.778 48,380.418 ↑ 1.3 158,830 1

HashAggregate (cost=249,358.09..251,352.04 rows=199,395 width=589) (actual time=48,328.268..48,380.418 rows=158,830 loops=1)

  • Group Key: "*SELECT* 1".id, "*SELECT* 1".deleted, "*SELECT* 1".id_ek5, "*SELECT* 1".number, "*SELECT* 1".online_shop, "*SELECT* 1".date_time_create
7. 10.801 48,239.640 ↑ 1.3 158,838 1

Append (cost=242,549.37..246,367.16 rows=199,395 width=589) (actual time=48,190.613..48,239.640 rows=158,838 loops=1)

8. 23.703 48,225.739 ↑ 1.3 158,823 1

Subquery Scan on *SELECT* 1 (cost=242,549.37..245,038.36 rows=199,119 width=249) (actual time=48,190.610..48,225.739 rows=158,823 loops=1)

9. 395.600 48,202.036 ↑ 1.3 158,823 1

Sort (cost=242,549.37..243,047.17 rows=199,119 width=281) (actual time=48,190.600..48,202.036 rows=158,823 loops=1)

  • Sort Key: (((o_1.online_shop)::json ->> 'numberDeparture'::text)) DESC
  • Sort Method: quicksort Memory: 28,482kB
10. 47,806.436 47,806.436 ↑ 1.3 158,823 1

Index Scan using idx_order_sender_code_number_department on "order" o_1 (cost=0.56..225,023.65 rows=199,119 width=281) (actual time=1.098..47,806.436 rows=158,823 loops=1)

  • Index Cond: (((sender_contragent_code)::text = '559e77c152183b66ba17f085'::text) AND (deleted = false) AND (date_time_create >= '2019-08-12 00:00:00'::timestamp without time zone) AND (date_time_create <= '2019-09-14 23:59:59'::timestamp without time zone))
  • Filter: (NOT deleted)
11. 0.004 2.241 ↑ 6.6 7 1

Subquery Scan on *SELECT* 2 (cost=55.03..55.60 rows=46 width=249) (actual time=2.238..2.241 rows=7 loops=1)

12. 0.033 2.237 ↑ 6.6 7 1

Sort (cost=55.03..55.14 rows=46 width=281) (actual time=2.236..2.237 rows=7 loops=1)

  • Sort Key: (((o_2.online_shop)::json ->> 'numberDeparture'::text)) DESC
  • Sort Method: quicksort Memory: 26kB
13. 2.204 2.204 ↑ 6.6 7 1

Index Scan using idx_order_receiver_code_number_department on "order" o_2 (cost=0.56..53.76 rows=46 width=281) (actual time=0.725..2.204 rows=7 loops=1)

  • Index Cond: (((receiver_contragent_code)::text = '559e77c152183b66ba17f085'::text) AND (deleted = false) AND (date_time_create >= '2019-08-12 00:00:00'::timestamp without time zone) AND (date_time_create <= '2019-09-14 23:59:59'::timestamp without time zone))
  • Filter: (NOT deleted)
14. 0.003 0.859 ↑ 28.8 8 1

Subquery Scan on *SELECT* 3 (cost=273.35..276.23 rows=230 width=249) (actual time=0.857..0.859 rows=8 loops=1)

15. 0.018 0.856 ↑ 28.8 8 1

Sort (cost=273.35..273.93 rows=230 width=281) (actual time=0.856..0.856 rows=8 loops=1)

  • Sort Key: (((o_3.online_shop)::json ->> 'numberDeparture'::text)) DESC
  • Sort Method: quicksort Memory: 26kB
16. 0.838 0.838 ↑ 28.8 8 1

Index Scan using idx_order_payer_code_number_department on "order" o_3 (cost=0.56..264.33 rows=230 width=281) (actual time=0.822..0.838 rows=8 loops=1)

  • Index Cond: (((payer_contragent_code)::text = '559e77c152183b66ba17f085'::text) AND (deleted = false) AND (date_time_create >= '2019-08-12 00:00:00'::timestamp without time zone) AND (date_time_create <= '2019-09-14 23:59:59'::timestamp without time zone))
  • Filter: (NOT deleted)
17. 0.035 48,430.025 ↑ 1.0 20 1

Nested Loop (cost=0.56..56.00 rows=20 width=2,051) (actual time=48,418.252..48,430.025 rows=20 loops=1)

18. 48,417.510 48,417.510 ↑ 1.0 20 1

CTE Scan on filtered_orders t (cost=0.00..0.40 rows=20 width=16) (actual time=48,417.450..48,417.510 rows=20 loops=1)

19. 12.480 12.480 ↑ 1.0 1 20

Index Scan using "idx-order_id" on "order" o (cost=0.56..2.78 rows=1 width=2,071) (actual time=0.624..0.624 rows=1 loops=20)

  • Index Cond: (id = t.id)
Planning time : 8.121 ms
Execution time : 48,446.544 ms