explain.depesz.com

PostgreSQL's explain analyze made readable

Result: gq6g : 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: 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; plan #yhLv

Settings

Optimization path:

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 0.010 669.300 ↑ 1.0 1 1

Aggregate (cost=260,204.48..260,204.49 rows=1 width=8) (actual time=669.300..669.300 rows=1 loops=1)

2.          

CTE filtered_orders

3. 0.011 668.798 ↑ 1.0 20 1

Limit (cost=260,148.38..260,148.43 rows=20 width=621) (actual time=668.788..668.798 rows=20 loops=1)

4. 21.418 668.787 ↑ 9,969.8 20 1

Sort (cost=260,148.38..260,646.87 rows=199,395 width=621) (actual time=668.785..668.787 rows=20 loops=1)

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

Subquery Scan on t_1 (cost=249,359.19..254,842.55 rows=199,395 width=621) (actual time=584.742..647.369 rows=158,830 loops=1)

6. 151.733 632.956 ↑ 1.3 158,830 1

HashAggregate (cost=249,359.19..251,353.14 rows=199,395 width=589) (actual time=584.739..632.956 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. 12.020 481.223 ↑ 1.3 158,838 1

Append (cost=242,550.47..246,368.26 rows=199,395 width=589) (actual time=424.203..481.223 rows=158,838 loops=1)

8. 29.033 469.056 ↑ 1.3 158,823 1

Subquery Scan on *SELECT* 1 (cost=242,550.47..245,039.46 rows=199,119 width=249) (actual time=424.200..469.056 rows=158,823 loops=1)

9. 73.073 440.023 ↑ 1.3 158,823 1

Sort (cost=242,550.47..243,048.27 rows=199,119 width=281) (actual time=424.196..440.023 rows=158,823 loops=1)

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

Index Scan using idx_order_sender_code_number_department on "order" o_1 (cost=0.56..225,024.75 rows=199,119 width=281) (actual time=0.089..366.950 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.003 0.095 ↑ 6.6 7 1

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

12. 0.013 0.092 ↑ 6.6 7 1

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

  • Sort Key: (((o_2.online_shop)::json ->> 'numberDeparture'::text)) DESC
  • Sort Method: quicksort Memory: 26kB
13. 0.079 0.079 ↑ 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.046..0.079 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.052 ↑ 28.8 8 1

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

15. 0.007 0.049 ↑ 28.8 8 1

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

  • Sort Key: (((o_3.online_shop)::json ->> 'numberDeparture'::text)) DESC
  • Sort Method: quicksort Memory: 26kB
16. 0.042 0.042 ↑ 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.029..0.042 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.018 669.290 ↑ 1.0 20 1

Nested Loop (cost=0.56..56.00 rows=20 width=2,051) (actual time=668.887..669.290 rows=20 loops=1)

18. 668.812 668.812 ↑ 1.0 20 1

CTE Scan on filtered_orders t (cost=0.00..0.40 rows=20 width=16) (actual time=668.793..668.812 rows=20 loops=1)

19. 0.460 0.460 ↑ 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.023..0.023 rows=1 loops=20)

  • Index Cond: (id = t.id)
Planning time : 3.830 ms
Execution time : 677.099 ms