explain.depesz.com

PostgreSQL's explain analyze made readable

Result: C0Kh

Settings
# exclusive inclusive rows x rows loops node
1. 1,568.030 11,845.098 ↑ 5.5 82,933 1

GroupAggregate (cost=465,684.97..492,820.57 rows=452,260 width=204) (actual time=10,029.507..11,845.098 rows=82,933 loops=1)

  • Group Key: (to_char((orders.cdate)::timestamp with time zone, 'YYYYMM'::text)), visits.id_shop
2. 3,017.305 10,277.068 ↓ 5.1 2,322,028 1

Sort (cost=465,684.97..466,815.62 rows=452,260 width=67) (actual time=10,029.472..10,277.068 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,089.637 7,259.763 ↓ 5.1 2,322,028 1

Hash Join (cost=383,843.96..423,202.40 rows=452,260 width=67) (actual time=5,552.537..7,259.763 rows=2,322,028 loops=1)

  • Hash Cond: (client_products.id_product = custom_form_params.id_system)
4. 617.002 6,168.574 ↓ 6.4 2,344,636 1

Hash Right Join (cost=383,700.95..417,617.99 rows=363,527 width=43) (actual time=5,550.973..6,168.574 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.802 1.802 ↑ 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.802 rows=31,996 loops=1)

6. 446.587 5,549.770 ↓ 6.4 2,344,636 1

Hash (cost=378,248.05..378,248.05 rows=363,527 width=41) (actual time=5,549.770..5,549.770 rows=2,344,636 loops=1)

  • Buckets: 4194304 (originally 524288) Batches: 1 (originally 1) Memory Usage: 206787kB
7. 426.635 5,103.183 ↓ 6.4 2,344,636 1

Hash Join (cost=142,932.04..378,248.05 rows=363,527 width=41) (actual time=726.587..5,103.183 rows=2,344,636 loops=1)

  • Hash Cond: (client_tt.id_client_tt = tmp_shops_base_5d9b2f064011b8_20014869.tt_id)
8. 417.543 4,669.142 ↑ 1.0 2,370,298 1

Hash Join (cost=141,852.69..370,856.04 rows=2,404,786 width=45) (actual time=719.150..4,669.142 rows=2,370,298 loops=1)

  • Hash Cond: (client_products.id_product = production.id)
9. 341.638 4,251.135 ↑ 1.0 2,370,298 1

Hash Join (cost=141,778.80..364,442.08 rows=2,404,786 width=31) (actual time=718.682..4,251.135 rows=2,370,298 loops=1)

  • Hash Cond: (order_client_tt.id_group = groups.id)
10. 403.022 3,909.455 ↑ 1.0 2,370,298 1

Hash Join (cost=141,773.15..357,757.39 rows=2,404,786 width=31) (actual time=718.637..3,909.455 rows=2,370,298 loops=1)

  • Hash Cond: (order_values.id_product = client_products.id_uproduct)
11. 381.029 3,495.945 ↑ 1.0 2,370,298 1

Hash Join (cost=140,121.12..349,792.22 rows=2,404,786 width=31) (actual time=708.128..3,495.945 rows=2,370,298 loops=1)

  • Hash Cond: (orders.id_shop = order_client_tt.id_client_tt)
12. 406.942 3,035.955 ↑ 1.0 2,370,298 1

Hash Join (cost=114,866.55..318,224.99 rows=2,404,786 width=31) (actual time=629.023..3,035.955 rows=2,370,298 loops=1)

  • Hash Cond: (visits.id_shop = client_tt.id_client_tt)
13. 368.508 2,547.408 ↑ 1.0 2,370,298 1

Hash Join (cost=89,611.98..286,657.76 rows=2,404,786 width=27) (actual time=546.546..2,547.408 rows=2,370,298 loops=1)

  • Hash Cond: (orders.id_visit = visits.id)
14. 1,039.265 1,829.640 ↑ 1.0 2,370,298 1

Hash Join (cost=47,035.36..237,768.55 rows=2,404,786 width=27) (actual time=190.795..1,829.640 rows=2,370,298 loops=1)

  • Hash Cond: (order_values.id_order = orders.id)
15. 601.225 601.225 ↑ 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.007..601.225 rows=10,040,799 loops=1)

16. 51.741 189.150 ↑ 1.0 282,561 1

Hash (cost=43,503.35..43,503.35 rows=282,561 width=16) (actual time=189.150..189.150 rows=282,561 loops=1)

  • Buckets: 524288 Batches: 1 Memory Usage: 17342kB
17. 137.409 137.409 ↑ 1.0 282,561 1

Seq Scan on orders (cost=0.00..43,503.35 rows=282,561 width=16) (actual time=88.936..137.409 rows=282,561 loops=1)

  • Filter: (cdate >= '2019-07-01'::date)
  • Rows Removed by Filter: 897227
18. 216.013 349.260 ↑ 1.0 1,161,672 1

Hash (cost=28,055.72..28,055.72 rows=1,161,672 width=8) (actual time=349.260..349.260 rows=1,161,672 loops=1)

  • Buckets: 2097152 Batches: 1 Memory Usage: 61762kB
19. 133.247 133.247 ↑ 1.0 1,161,672 1

Seq Scan on visits (cost=0.00..28,055.72 rows=1,161,672 width=8) (actual time=0.009..133.247 rows=1,161,672 loops=1)

20. 40.323 81.605 ↑ 1.0 265,003 1

Hash (cost=21,942.03..21,942.03 rows=265,003 width=4) (actual time=81.605..81.605 rows=265,003 loops=1)

  • Buckets: 524288 Batches: 1 Memory Usage: 13413kB
21. 41.282 41.282 ↑ 1.0 265,003 1

Seq Scan on client_tt (cost=0.00..21,942.03 rows=265,003 width=4) (actual time=0.006..41.282 rows=265,003 loops=1)

22. 34.486 78.961 ↑ 1.0 265,003 1

Hash (cost=21,942.03..21,942.03 rows=265,003 width=8) (actual time=78.961..78.961 rows=265,003 loops=1)

  • Buckets: 524288 Batches: 1 Memory Usage: 14448kB
23. 44.475 44.475 ↑ 1.0 265,003 1

Seq Scan on client_tt order_client_tt (cost=0.00..21,942.03 rows=265,003 width=8) (actual time=0.004..44.475 rows=265,003 loops=1)

24. 4.997 10.488 ↑ 1.0 46,046 1

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

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

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

26. 0.008 0.042 ↑ 1.0 73 1

Hash (cost=4.73..4.73 rows=73 width=8) (actual time=0.042..0.042 rows=73 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 11kB
27. 0.034 0.034 ↑ 1.0 73 1

Seq Scan on groups (cost=0.00..4.73 rows=73 width=8) (actual time=0.005..0.034 rows=73 loops=1)

28. 0.116 0.464 ↑ 1.0 973 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 54kB
29. 0.348 0.348 ↑ 1.0 973 1

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

30. 4.336 7.406 ↑ 1.0 40,060 1

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

  • Buckets: 65536 Batches: 1 Memory Usage: 1921kB
31. 3.070 3.070 ↑ 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..3.070 rows=40,060 loops=1)

32. 0.137 1.552 ↑ 1.3 966 1

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

  • Buckets: 2048 Batches: 1 Memory Usage: 50kB
33. 0.436 1.415 ↓ 1.2 1,468 1

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

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

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

35. 0.174 0.721 ↓ 7.3 1,468 1

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

  • Buckets: 2048 (originally 1024) Batches: 1 (originally 1) Memory Usage: 68kB
36. 0.441 0.547 ↓ 7.3 1,468 1

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

  • Group Key: tmp_allowed_params_5d9b2f06afbce1_39835683.id
37. 0.106 0.106 ↑ 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.006..0.106 rows=1,468 loops=1)

Planning time : 11.203 ms
Execution time : 11,868.128 ms