explain.depesz.com

PostgreSQL's explain analyze made readable

Result: O07L

Settings
# exclusive inclusive rows x rows loops node
1. 33.563 112,118.198 ↓ 530.8 8,493 1

GroupAggregate (cost=8,556,536.85..8,556,538.33 rows=16 width=48) (actual time=112,080.989..112,118.198 rows=8,493 loops=1)

  • Group Key: c.id, cpi.debt, ct.date
  • Filter: ((max(cs.date_add) < ct.date) OR (max(cs.date_add) IS NULL))
  • Rows Removed by Filter: 26
  • AND (NOT (hashed SubPlan 8)) AND (NOT (hashed SubPlan 9)) AND (NOT (hashed SubPlan 10)))
2.          

CTE tmp_clients_term_x

3. 116.867 419.483 ↓ 1.0 107,900 1

Hash Left Join (cost=11,248.44..52,194.03 rows=106,548 width=8) (actual time=82.456..419.483 rows=107,900 loops=1)

  • Hash Cond: ((cft.id = ct_1.field_id) AND (c_1.id = ct_1.client_id))
4. 51.420 220.283 ↓ 1.0 107,900 1

Nested Loop Left Join (cost=0.00..38,788.00 rows=106,548 width=10) (actual time=0.080..220.283 rows=107,900 loops=1)

5. 168.863 168.863 ↓ 1.0 107,900 1

Seq Scan on clients c_1 (cost=0.00..37,454.67 rows=106,548 width=4) (actual time=0.059..168.863 rows=107,900 loops=1)

  • Filter: (NOT deleted)
  • Rows Removed by Filter: 146,521
6. 0.000 0.000 ↑ 1.0 1 107,900

Materialize (cost=0.00..1.48 rows=1 width=6) (actual time=0.000..0.000 rows=1 loops=107,900)

7. 0.016 0.016 ↑ 1.0 1 1

Seq Scan on fields cft (cost=0.00..1.48 rows=1 width=6) (actual time=0.012..0.016 rows=1 loops=1)

  • Filter: (id = 20)
  • Rows Removed by Filter: 37
8. 0.076 82.333 ↓ 3.0 122 1

Hash (cost=11,247.84..11,247.84 rows=40 width=17) (actual time=82.332..82.333 rows=122 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 14kB
9. 82.257 82.257 ↓ 3.0 122 1

Seq Scan on "values" ct_1 (cost=0.00..11,247.84 rows=40 width=17) (actual time=12.002..82.257 rows=122 loops=1)

  • Filter: (field_id = 20)
  • Rows Removed by Filter: 529,319
10. 11.597 112,084.635 ↓ 413.4 19,843 1

Sort (cost=8,504,342.82..8,504,342.94 rows=48 width=20) (actual time=112,080.966..112,084.635 rows=19,843 loops=1)

  • Sort Key: c.id, cpi.debt, ct.date
  • Sort Method: quicksort Memory: 2,300kB
11. 13.043 112,073.038 ↓ 413.4 19,843 1

Hash Left Join (cost=8,382,720.67..8,504,341.48 rows=48 width=20) (actual time=111,381.954..112,073.038 rows=19,843 loops=1)

  • Hash Cond: (c.id = cpi.client_id)
12. 10.252 111,538.713 ↓ 413.4 19,843 1

Nested Loop Left Join (cost=8,364,524.73..8,486,145.42 rows=48 width=12) (actual time=110,860.021..111,538.713 rows=19,843 loops=1)

13. 23.308 111,451.790 ↓ 340.8 8,519 1

Merge Join (cost=8,364,524.31..8,485,803.54 rows=25 width=8) (actual time=110,859.928..111,451.790 rows=8,519 loops=1)

  • Merge Cond: (c.id = ct.id)
  • Join Filter: (c.input_date < ct.date)
  • Rows Removed by Join Filter: 1,034
14. 829.841 111,357.973 ↓ 53.4 9,553 1

Index Scan using clients_pkey on clients c (cost=8,353,495.98..8,474,241.08 rows=179 width=8) (actual time=110,813.127..111,357.973 rows=9,553 loops=1)

  • Filter: ((NOT deleted) AND t_kl AND (NOT (hashed SubPlan 2)) AND (NOT (hashed SubPlan 3)) AND (NOT (hashed SubPlan 4)) AND (NOT (hashed SubPlan 5)) AND (NOT (hashed SubPlan 6)) AND (NOT (hashed SubPlan 7))
  • Rows Removed by Filter: 244,868
15.          

SubPlan (for Index Scan)

16. 0.049 0.049 ↑ 1.0 5 1

Seq Scan on cl_status (cost=0.00..1.09 rows=5 width=4) (actual time=0.045..0.049 rows=5 loops=1)

  • Filter: (NOT bymoney)
  • Rows Removed by Filter: 4
17. 169.092 38,342.045 ↓ 1.1 86,718 1

Unique (cost=4,436,436.05..5,385,011.35 rows=76,339 width=4) (actual time=26,429.584..38,342.045 rows=86,718 loops=1)

18. 6,562.491 38,172.953 ↑ 6.6 1,788,230 1

Merge Left Join (cost=4,436,436.05..5,355,620.03 rows=11,756,526 width=4) (actual time=26,429.582..38,172.953 rows=1,788,230 loops=1)

  • Merge Cond: (cp.client_id = ct_2.id)
  • Filter: ((cp.data >= ct_2.date) OR ((cp.data >= '2018-11-27'::date) AND (cp.service_id = 8)))
  • Rows Removed by Filter: 23,460,594
19. 19,312.686 29,906.145 ↑ 1.0 25,248,824 1

Sort (cost=4,425,407.72..4,488,532.00 rows=25,249,714 width=10) (actual time=25,920.466..29,906.145 rows=25,248,824 loops=1)

  • Sort Key: cp.client_id
  • Sort Method: external merge Disk: 494,120kB
20. 10,593.459 10,593.459 ↑ 1.0 25,248,824 1

Seq Scan on cl_payments cp (cost=0.00..889,470.21 rows=25,249,714 width=10) (actual time=0.056..10,593.459 rows=25,248,824 loops=1)

  • Filter: (pay_id <> 55)
  • Rows Removed by Filter: 689,110
21. 1,236.147 1,704.317 ↓ 152.5 16,253,356 1

Sort (cost=11,028.33..11,294.70 rows=106,548 width=8) (actual time=509.069..1,704.317 rows=16,253,356 loops=1)

  • Sort Key: ct_2.id
  • Sort Method: quicksort Memory: 8,130kB
22. 468.170 468.170 ↓ 1.0 107,900 1

CTE Scan on tmp_clients_term_x ct_2 (cost=0.00..2,130.96 rows=106,548 width=8) (actual time=82.464..468.170 rows=107,900 loops=1)

23. 79.468 9,641.624 ↑ 1.1 86,477 1

Unique (cost=11,028.76..582,013.42 rows=94,960 width=4) (actual time=38.758..9,641.624 rows=86,477 loops=1)

24. 1,923.392 9,562.156 ↑ 4.1 616,108 1

Merge Join (cost=11,028.76..575,722.03 rows=2,516,556 width=4) (actual time=38.755..9,562.156 rows=616,108 loops=1)

  • Merge Cond: (iac.client_id = ct_3.id)
  • Join Filter: (iac.date >= ct_3.date)
  • Rows Removed by Join Filter: 4,331,443
25. 7,209.428 7,209.428 ↑ 1.0 6,707,242 1

Index Scan using active_clients_idx2 on active_clients iac (cost=0.43..415,753.08 rows=6,728,577 width=8) (actual time=0.081..7,209.428 rows=6,707,242 loops=1)

26. 418.297 429.336 ↓ 46.5 4,953,980 1

Sort (cost=11,028.33..11,294.70 rows=106,548 width=8) (actual time=38.664..429.336 rows=4,953,980 loops=1)

  • Sort Key: ct_3.id
  • Sort Method: quicksort Memory: 8,130kB
27. 11.039 11.039 ↓ 1.0 107,900 1

CTE Scan on tmp_clients_term_x ct_3 (cost=0.00..2,130.96 rows=106,548 width=8) (actual time=0.002..11.039 rows=107,900 loops=1)

28. 1.132 28.742 ↓ 58.0 8,004 1

Nested Loop (cost=1.71..542.07 rows=138 width=4) (actual time=0.083..28.742 rows=8,004 loops=1)

29. 0.019 0.041 ↑ 1.0 19 1

HashAggregate (cost=1.28..1.47 rows=19 width=4) (actual time=0.032..0.041 rows=19 loops=1)

  • Group Key: ksp.client_id
30. 0.022 0.022 ↑ 1.0 20 1

Seq Scan on ksp (cost=0.00..1.23 rows=20 width=4) (actual time=0.016..0.022 rows=20 loops=1)

  • Filter: self
  • Rows Removed by Filter: 3
31. 27.569 27.569 ↓ 60.1 421 19

Index Scan using main_storona2_idx on main (cost=0.42..28.38 rows=7 width=8) (actual time=0.023..1.451 rows=421 loops=19)

  • Index Cond: (storona2 = ksp.client_id)
  • Filter: actual
  • Rows Removed by Filter: 112
32. 62,239.345 62,239.345 ↑ 5.5 266,198 1

Seq Scan on inet_monthly_abonpay (cost=0.00..2,324,783.12 rows=1,459,920 width=4) (actual time=56,397.293..62,239.345 rows=266,198 loops=1)

  • Filter: (date_month((date + '6 mons'::interval), 'first'::character varying) > date_month(((now())::date)::timestamp without time zone))
  • Rows Removed by Filter: 4,116,208
33. 5.463 35.087 ↑ 1.2 7,460 1

HashAggregate (cost=2,392.02..2,484.45 rows=9,243 width=4) (actual time=33.895..35.087 rows=7,460 loops=1)

  • Group Key: subscriptions.client_id
34. 29.624 29.624 ↑ 1.1 17,155 1

Seq Scan on subscriptions (cost=0.00..2,345.52 rows=18,599 width=4) (actual time=0.115..29.624 rows=17,155 loops=1)

  • Filter: ((status_id <> 4) AND ((date_stop + '6 mons'::interval) >= now()))
  • Rows Removed by Filter: 40,018
35. 0.017 0.017 ↑ 1.0 20 1

Seq Scan on ksp ksp_1 (cost=0.00..1.23 rows=20 width=4) (actual time=0.012..0.017 rows=20 loops=1)

  • Filter: self
  • Rows Removed by Filter: 3
36. 34.732 34.732 ↓ 4.8 2,928 1

Seq Scan on inet_serv_block (cost=0.00..3,271.51 rows=609 width=4) (actual time=5.429..34.732 rows=2,928 loops=1)

  • Filter: ((tm_start <= now()) AND ((tm_end > now()) OR (tm_end IS NULL)) AND (tm_start >= (now() - '1 year'::interval)))
  • Rows Removed by Filter: 89,658
37. 1.786 206.491 ↑ 38.7 1,123 1

HashAggregate (cost=50,741.65..51,175.73 rows=43,408 width=4) (actual time=205.303..206.491 rows=1,123 loops=1)

  • Group Key: t1.client_id
38. 26.607 204.705 ↑ 34.7 1,250 1

Hash Join (cost=11,103.43..50,633.13 rows=43,408 width=4) (actual time=57.912..204.705 rows=1,250 loops=1)

  • Hash Cond: (cli.id = t1.client_id)
39. 120.731 120.731 ↑ 1.0 218,427 1

Seq Scan on clients cli (cost=0.00..37,454.67 rows=218,793 width=4) (actual time=0.014..120.731 rows=218,427 loops=1)

  • Filter: t_kl
  • Rows Removed by Filter: 35,994
40. 0.614 57.367 ↑ 30.0 1,677 1

Hash (cost=10,473.59..10,473.59 rows=50,387 width=4) (actual time=57.367..57.367 rows=1,677 loops=1)

  • Buckets: 65,536 Batches: 1 Memory Usage: 571kB
41. 44.958 56.753 ↑ 30.0 1,677 1

Bitmap Heap Scan on cl_payments_itog t1 (cost=3,352.42..10,473.59 rows=50,387 width=4) (actual time=12.259..56.753 rows=1,677 loops=1)

  • Recheck Cond: (service_id = ANY ('{93,77,3,25,60}'::integer[]))
  • Filter: (round(summa) < '0'::double precision)
  • Rows Removed by Filter: 150,569
  • Heap Blocks: exact=3,587
42. 11.795 11.795 ↓ 1.0 152,299 1

Bitmap Index Scan on cl_payments_itog_idx2_pkey (cost=0.00..3,339.82 rows=151,161 width=0) (actual time=11.795..11.795 rows=152,299 loops=1)

  • Index Cond: (service_id = ANY ('{93,77,3,25,60}'::integer[]))
43. 54.840 70.509 ↓ 1.0 107,891 1

Sort (cost=11,028.33..11,294.70 rows=106,548 width=8) (actual time=46.787..70.509 rows=107,891 loops=1)

  • Sort Key: ct.id
  • Sort Method: quicksort Memory: 8,130kB
44. 15.669 15.669 ↓ 1.0 107,900 1

CTE Scan on tmp_clients_term_x ct (cost=0.00..2,130.96 rows=106,548 width=8) (actual time=0.004..15.669 rows=107,900 loops=1)

45. 76.671 76.671 ↑ 1.5 2 8,519

Index Scan using cl_services_cl_idx on cl_services cs (cost=0.42..13.65 rows=3 width=8) (actual time=0.006..0.009 rows=2 loops=8,519)

  • Index Cond: (client_id = c.id)
46. 57.792 521.282 ↓ 1.3 213,498 1

Hash (cost=16,156.58..16,156.58 rows=163,149 width=12) (actual time=521.282..521.282 rows=213,498 loops=1)

  • Buckets: 262,144 Batches: 1 Memory Usage: 11,222kB
47. 31.089 463.490 ↓ 1.3 213,498 1

Subquery Scan on cpi (cost=12,893.60..16,156.58 rows=163,149 width=12) (actual time=353.853..463.490 rows=213,498 loops=1)

48. 350.095 432.401 ↓ 1.3 213,498 1

HashAggregate (cost=12,893.60..14,525.09 rows=163,149 width=12) (actual time=353.851..432.401 rows=213,498 loops=1)

  • Group Key: cl_payments_itog.client_id
49. 82.306 82.306 ↓ 1.0 599,252 1

Seq Scan on cl_payments_itog (cost=0.00..9,898.73 rows=598,973 width=12) (actual time=0.018..82.306 rows=599,252 loops=1)

Planning time : 20.470 ms
Execution time : 112,261.996 ms