explain.depesz.com

PostgreSQL's explain analyze made readable

Result: eMvk

Settings
# exclusive inclusive rows x rows loops node
1. 1,580.403 9,864.700 ↑ 5.2 82,933 1

GroupAggregate (cost=456,991.08..482,691.48 rows=428,340 width=204) (actual time=8,032.542..9,864.700 rows=82,933 loops=1)

  • Group Key: (to_char((orders.cdate)::timestamp with time zone, 'YYYYMM'::text)), visits.id_shop
2. 3,043.223 8,284.297 ↓ 5.4 2,322,028 1

Sort (cost=456,991.08..458,061.93 rows=428,340 width=67) (actual time=8,032.504..8,284.297 rows=2,322,028 loops=1)

  • Sort Key: (to_char((orders.cdate)::timestamp with time zone, 'YYYYMM'::text)), visits.id_shop
  • Sort Method: quicksort Memory: 264117kB
3. 1,070.336 5,241.074 ↓ 5.4 2,322,028 1

Hash Join (cost=379,626.19..416,923.31 rows=428,340 width=67) (actual time=3,556.543..5,241.074 rows=2,322,028 loops=1)

  • Hash Cond: (client_products.id_product = custom_form_params.id_system)
4. 614.914 4,169.395 ↓ 6.8 2,344,636 1

Hash Right Join (cost=379,483.18..411,626.69 rows=344,300 width=43) (actual time=3,555.190..4,169.395 rows=2,344,636 loops=1)

  • Hash Cond: ((production_price.id_production = production.id) AND (production_price.price_list_id = groups.price_list_id))
  • Join Filter: ((orders.cdate >= production_price.date_from) AND (orders.cdate <= production_price.date_to))
5. 1.847 1.847 ↑ 1.0 31,996 1

Seq Scan on production_price (cost=0.00..555.96 rows=31,996 width=22) (actual time=0.009..1.847 rows=31,996 loops=1)

6. 427.392 3,552.634 ↓ 6.8 2,344,636 1

Hash (cost=374,318.68..374,318.68 rows=344,300 width=41) (actual time=3,552.634..3,552.634 rows=2,344,636 loops=1)

  • Buckets: 4194304 (originally 524288) Batches: 1 (originally 1) Memory Usage: 206787kB
7. 361.299 3,125.242 ↓ 6.8 2,344,636 1

Hash Join (cost=131,550.99..374,318.68 rows=344,300 width=41) (actual time=320.908..3,125.242 rows=2,344,636 loops=1)

  • Hash Cond: (client_tt.id_client_tt = tmp_shops_base_5d9b2f064011b8_20014869.tt_id)
8. 367.316 2,757.127 ↓ 1.0 2,370,298 1

Hash Join (cost=130,471.64..367,260.54 rows=2,277,597 width=45) (actual time=313.901..2,757.127 rows=2,370,298 loops=1)

  • Hash Cond: (client_products.id_product = production.id)
9. 391.537 2,389.362 ↓ 1.0 2,370,298 1

Hash Join (cost=130,397.75..361,181.91 rows=2,277,597 width=31) (actual time=313.444..2,389.362 rows=2,370,298 loops=1)

  • Hash Cond: (order_values.id_product = client_products.id_uproduct)
10. 1,124.004 1,987.787 ↓ 1.0 2,370,298 1

Hash Join (cost=128,745.72..353,550.64 rows=2,277,597 width=31) (actual time=303.229..1,987.787 rows=2,370,298 loops=1)

  • Hash Cond: (order_values.id_order = orders.id)
11. 562.341 562.341 ↑ 1.0 10,040,799 1

Seq Scan on order_values (cost=0.00..164,375.99 rows=10,040,799 width=19) (actual time=0.005..562.341 rows=10,040,799 loops=1)

12. 61.226 301.442 ↓ 1.1 282,561 1

Hash (cost=125,400.52..125,400.52 rows=267,616 width=20) (actual time=301.442..301.442 rows=282,561 loops=1)

  • Buckets: 524288 Batches: 1 Memory Usage: 18445kB
13. 0.000 240.216 ↓ 1.1 282,561 1

Gather (cost=65,622.15..125,400.52 rows=267,616 width=20) (actual time=182.519..240.216 rows=282,561 loops=1)

  • Workers Planned: 4
  • Workers Launched: 4
14. 12.562 260.638 ↑ 1.2 56,512 5

Hash Join (cost=64,622.15..97,638.92 rows=66,904 width=20) (actual time=178.059..260.638 rows=56,512 loops=5)

  • Hash Cond: (order_client_tt.id_group = groups.id)
15. 20.258 248.001 ↑ 1.2 56,512 5

Parallel Hash Join (cost=64,616.51..97,447.46 rows=66,904 width=20) (actual time=177.832..248.001 rows=56,512 loops=5)

  • Hash Cond: (orders.id_shop = order_client_tt.id_client_tt)
16. 19.081 191.025 ↑ 1.2 56,512 5

Parallel Hash Join (cost=43,401.10..76,056.43 rows=66,904 width=20) (actual time=140.327..191.025 rows=56,512 loops=5)

  • Hash Cond: (visits.id_shop = client_tt.id_client_tt)
17. 55.172 142.846 ↑ 1.2 56,512 5

Parallel Hash Join (cost=22,185.69..54,665.39 rows=66,904 width=16) (actual time=110.843..142.846 rows=56,512 loops=5)

  • Hash Cond: (orders.id_visit = visits.id)
18. 38.429 38.429 ↑ 1.2 235,958 5

Parallel Seq Scan on orders (cost=0.00..31,705.47 rows=294,947 width=16) (actual time=0.012..38.429 rows=235,958 loops=5)

19. 13.538 49.245 ↑ 1.6 52,701 5

Parallel Hash (cost=21,123.16..21,123.16 rows=85,002 width=8) (actual time=49.245..49.245 rows=52,701 loops=5)

  • Buckets: 524288 Batches: 1 Memory Usage: 14496kB
20. 35.707 35.707 ↑ 1.6 52,701 5

Parallel Seq Scan on visits (cost=0.00..21,123.16 rows=85,002 width=8) (actual time=20.299..35.707 rows=52,701 loops=5)

  • Filter: (cdate >= '2019-07-01'::date)
  • Rows Removed by Filter: 179633
21. 14.366 29.098 ↑ 1.6 53,001 5

Parallel Hash (cost=20,146.85..20,146.85 rows=85,485 width=4) (actual time=29.098..29.098 rows=53,001 loops=5)

  • Buckets: 524288 Batches: 1 Memory Usage: 14560kB
22. 14.732 14.732 ↑ 1.6 53,001 5

Parallel Seq Scan on client_tt (cost=0.00..20,146.85 rows=85,485 width=4) (actual time=0.008..14.732 rows=53,001 loops=5)

23. 14.598 36.718 ↑ 1.6 53,001 5

Parallel Hash (cost=20,146.85..20,146.85 rows=85,485 width=8) (actual time=36.718..36.718 rows=53,001 loops=5)

  • Buckets: 524288 Batches: 1 Memory Usage: 14592kB
24. 22.120 22.120 ↑ 1.6 53,001 5

Parallel Seq Scan on client_tt order_client_tt (cost=0.00..20,146.85 rows=85,485 width=8) (actual time=0.010..22.120 rows=53,001 loops=5)

25. 0.015 0.075 ↑ 1.0 73 5

Hash (cost=4.73..4.73 rows=73 width=8) (actual time=0.075..0.075 rows=73 loops=5)

  • Buckets: 1024 Batches: 1 Memory Usage: 11kB
26. 0.060 0.060 ↑ 1.0 73 5

Seq Scan on groups (cost=0.00..4.73 rows=73 width=8) (actual time=0.016..0.060 rows=73 loops=5)

27. 4.947 10.038 ↑ 1.0 46,046 1

Hash (cost=1,076.46..1,076.46 rows=46,046 width=8) (actual time=10.038..10.038 rows=46,046 loops=1)

  • Buckets: 65536 Batches: 1 Memory Usage: 2311kB
28. 5.091 5.091 ↑ 1.0 46,046 1

Seq Scan on client_products (cost=0.00..1,076.46 rows=46,046 width=8) (actual time=0.006..5.091 rows=46,046 loops=1)

29. 0.116 0.449 ↑ 1.0 973 1

Hash (cost=61.73..61.73 rows=973 width=14) (actual time=0.449..0.449 rows=973 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 54kB
30. 0.333 0.333 ↑ 1.0 973 1

Seq Scan on production (cost=0.00..61.73 rows=973 width=14) (actual time=0.005..0.333 rows=973 loops=1)

31. 4.100 6.816 ↑ 1.0 40,060 1

Hash (cost=578.60..578.60 rows=40,060 width=4) (actual time=6.816..6.816 rows=40,060 loops=1)

  • Buckets: 65536 Batches: 1 Memory Usage: 1921kB
32. 2.716 2.716 ↑ 1.0 40,060 1

Seq Scan on tmp_shops_base_5d9b2f064011b8_20014869 (cost=0.00..578.60 rows=40,060 width=4) (actual time=0.008..2.716 rows=40,060 loops=1)

33. 0.123 1.343 ↑ 1.3 966 1

Hash (cost=127.88..127.88 rows=1,210 width=4) (actual time=1.343..1.343 rows=966 loops=1)

  • Buckets: 2048 Batches: 1 Memory Usage: 50kB
34. 0.368 1.220 ↓ 1.2 1,468 1

Hash Join (cost=29.85..127.88 rows=1,210 width=4) (actual time=0.638..1.220 rows=1,468 loops=1)

  • Hash Cond: (custom_form_params.id = tmp_allowed_params_5d9b2f06afbce1_39835683.id)
35. 0.230 0.230 ↑ 1.0 2,421 1

Seq Scan on custom_form_params (cost=0.00..78.21 rows=2,421 width=8) (actual time=0.006..0.230 rows=2,421 loops=1)

36. 0.146 0.622 ↓ 7.3 1,468 1

Hash (cost=27.35..27.35 rows=200 width=4) (actual time=0.622..0.622 rows=1,468 loops=1)

  • Buckets: 2048 (originally 1024) Batches: 1 (originally 1) Memory Usage: 68kB
37. 0.379 0.476 ↓ 7.3 1,468 1

HashAggregate (cost=25.35..27.35 rows=200 width=4) (actual time=0.337..0.476 rows=1,468 loops=1)

  • Group Key: tmp_allowed_params_5d9b2f06afbce1_39835683.id
38. 0.097 0.097 ↑ 1.0 1,468 1

Seq Scan on tmp_allowed_params_5d9b2f06afbce1_39835683 (cost=0.00..21.68 rows=1,468 width=4) (actual time=0.007..0.097 rows=1,468 loops=1)

Planning time : 9.811 ms
Execution time : 9,882.333 ms