explain.depesz.com

PostgreSQL's explain analyze made readable

Result: jRfF

Settings
# exclusive inclusive rows x rows loops node
1. 0.015 264.340 ↑ 1.0 10 1

Limit (cost=25,527.66..25,527.69 rows=10 width=232) (actual time=264.325..264.340 rows=10 loops=1)

2. 3.699 264.325 ↑ 1,222.5 11 1

Sort (cost=25,527.66..25,561.28 rows=13,448 width=232) (actual time=264.324..264.325 rows=11 loops=1)

  • Sort Key: r."MOST_CRITICAL", (count(ctc."COMPANY_TO_ID") OVER (?)) DESC, r."COUNT_IMPORTANT_NEWS" DESC NULLS LAST, r."COUNT_READ_IMPORTANT_NEWS" DESC NULLS LAST, c."NAME", c."ID
  • Sort Method: top-N heapsort Memory: 30kB
3. 7.065 260.626 ↓ 1.0 13,449 1

WindowAgg (cost=24,992.47..25,227.81 rows=13,448 width=232) (actual time=252.595..260.626 rows=13,449 loops=1)

4. 5.817 253.561 ↓ 1.0 13,449 1

Sort (cost=24,992.47..25,026.09 rows=13,448 width=306) (actual time=252.585..253.561 rows=13,449 loops=1)

  • Sort Key: ctc."COMPANY_FROM_ID
  • Sort Method: quicksort Memory: 2,966kB
5. 3.565 247.744 ↓ 1.0 13,449 1

Hash Left Join (cost=21,155.56..22,138.27 rows=13,448 width=306) (actual time=233.266..247.744 rows=13,449 loops=1)

  • Hash Cond: ((c."ID")::text = (r."COMPANY_ID")::text)
6. 3.864 17.255 ↓ 1.0 13,449 1

Hash Left Join (cost=550.25..1,497.64 rows=13,448 width=286) (actual time=6.326..17.255 rows=13,449 loops=1)

  • Hash Cond: ((c."ID")::text = (ctc."COMPANY_FROM_ID")::text)
7. 4.380 13.339 ↑ 1.0 13,448 1

Hash Join (cost=549.20..1,446.14 rows=13,448 width=106) (actual time=5.147..13.339 rows=13,448 loops=1)

  • Hash Cond: ((c."INN")::text = ("USER_TO_COMPANY"."COMPANY_INN")::text)
8. 3.839 3.839 ↑ 1.0 13,460 1

Seq Scan on "COMPANIES" c (cost=0.00..861.60 rows=13,460 width=106) (actual time=0.006..3.839 rows=13,460 loops=1)

9. 2.554 5.120 ↑ 1.0 13,448 1

Hash (cost=381.10..381.10 rows=13,448 width=11) (actual time=5.119..5.120 rows=13,448 loops=1)

  • Buckets: 16,384 Batches: 1 Memory Usage: 694kB
10. 2.566 2.566 ↑ 1.0 13,448 1

Seq Scan on "USER_TO_COMPANY" (cost=0.00..381.10 rows=13,448 width=11) (actual time=0.004..2.566 rows=13,448 loops=1)

  • Filter: (("USER_ID")::text = 'ac680c09724b36bd3e5f81c2a8caa41a'::text)
11. 0.025 0.052 ↓ 3.0 6 1

Hash (cost=1.02..1.02 rows=2 width=180) (actual time=0.052..0.052 rows=6 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
12. 0.027 0.027 ↓ 3.0 6 1

Seq Scan on "COMPANY_TO_COMPANY" ctc (cost=0.00..1.02 rows=2 width=180) (actual time=0.026..0.027 rows=6 loops=1)

13. 0.574 226.924 ↓ 74.1 1,037 1

Hash (cost=20,605.14..20,605.14 rows=14 width=56) (actual time=226.924..226.924 rows=1,037 loops=1)

  • Buckets: 2,048 (originally 1024) Batches: 1 (originally 1) Memory Usage: 111kB
14. 0.234 226.350 ↓ 74.1 1,037 1

Subquery Scan on r (cost=20,352.61..20,605.14 rows=14 width=56) (actual time=171.563..226.350 rows=1,037 loops=1)

15. 18.600 226.116 ↓ 74.1 1,037 1

GroupAggregate (cost=20,352.61..20,605.00 rows=14 width=56) (actual time=171.562..226.116 rows=1,037 loops=1)

  • Group Key: "TEXT_VIEW"."COMPANY_ID
16. 32.338 172.258 ↓ 384.4 5,381 1

Sort (cost=20,352.61..20,352.64 rows=14 width=102) (actual time=171.431..172.258 rows=5,381 loops=1)

  • Sort Key: "TEXT_VIEW"."COMPANY_ID
  • Sort Method: quicksort Memory: 948kB
17. 1.121 139.920 ↓ 384.4 5,381 1

Hash Left Join (cost=8,748.49..20,352.34 rows=14 width=102) (actual time=104.797..139.920 rows=5,381 loops=1)

  • Hash Cond: (("TEXT_VIEW"."TEXT_ID")::text = ("TRU"."TEXT_ID")::text)
18. 25.642 138.771 ↓ 384.4 5,381 1

Hash Join (cost=8,747.47..20,351.28 rows=14 width=69) (actual time=104.763..138.771 rows=5,381 loops=1)

  • Hash Cond: ((("TEXT_VIEW"."TEXT_ID")::text = (ttc."TEXT_ID")::text) AND (("TEXT_VIEW"."COMPANY_ID")::text = (ttc."COMPANY_ID")::text))
19. 2.007 14.842 ↑ 13.5 7,670 1

Hash Join (cost=551.51..11,612.90 rows=103,320 width=69) (actual time=6.214..14.842 rows=7,670 loops=1)

  • Hash Cond: (((unnest("TEXT_VIEW"."EVENTS_IDS")))::text = ("USER_ROLES_TO_EVENTS"."EVENT_ID")::text)
20. 2.764 12.808 ↑ 71.4 8,264 1

ProjectSet (cost=549.20..4,123.94 rows=590,400 width=101) (actual time=6.182..12.808 rows=8,264 loops=1)

21. 2.170 10.044 ↓ 1.1 6,357 1

Hash Join (cost=549.20..1,127.66 rows=5,904 width=99) (actual time=6.177..10.044 rows=6,357 loops=1)

  • Hash Cond: (("TEXT_VIEW"."INN")::text = ("USER_TO_COMPANY_1"."COMPANY_INN")::text)
22. 1.868 1.868 ↓ 1.1 6,357 1

Seq Scan on "TEXT_VIEW" (cost=0.00..562.97 rows=5,904 width=110) (actual time=0.053..1.868 rows=6,357 loops=1)

  • Filter: (("TEXT_DATE" >= '2019-12-01 00:00:00'::timestamp without time zone) AND ("TEXT_DATE" <= '2020-01-30 00:00:00'::timestamp without time zone))
  • Rows Removed by Filter: 4,228
23. 3.168 6.006 ↑ 1.0 13,448 1

Hash (cost=381.10..381.10 rows=13,448 width=11) (actual time=6.006..6.006 rows=13,448 loops=1)

  • Buckets: 16,384 Batches: 1 Memory Usage: 694kB
24. 2.838 2.838 ↑ 1.0 13,448 1

Seq Scan on "USER_TO_COMPANY" "USER_TO_COMPANY_1" (cost=0.00..381.10 rows=13,448 width=11) (actual time=0.005..2.838 rows=13,448 loops=1)

  • Filter: (("USER_ID")::text = 'ac680c09724b36bd3e5f81c2a8caa41a'::text)
25. 0.011 0.027 ↑ 1.0 35 1

Hash (cost=1.88..1.88 rows=35 width=3) (actual time=0.027..0.027 rows=35 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 10kB
26. 0.016 0.016 ↑ 1.0 35 1

Seq Scan on "USER_ROLES_TO_EVENTS" (cost=0.00..1.88 rows=35 width=3) (actual time=0.009..0.016 rows=35 loops=1)

  • Filter: (("USER_ROLE_ID")::text = '0'::text)
  • Rows Removed by Filter: 35
27. 49.403 98.287 ↓ 182.6 127,092 1

Hash (cost=8,185.51..8,185.51 rows=696 width=55) (actual time=98.286..98.287 rows=127,092 loops=1)

  • Buckets: 65,536 (originally 1024) Batches: 4 (originally 1) Memory Usage: 3,585kB
28. 48.884 48.884 ↓ 182.6 127,092 1

Seq Scan on "TEXT_TO_COMPANY" ttc (cost=0.00..8,185.51 rows=696 width=55) (actual time=0.036..48.884 rows=127,092 loops=1)

  • Filter: (("ID")::text = ("MASTER_GUID")::text)
  • Rows Removed by Filter: 12,069
29. 0.015 0.028 ↓ 28.0 28 1

Hash (cost=1.01..1.01 rows=1 width=33) (actual time=0.028..0.028 rows=28 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 10kB
30. 0.013 0.013 ↓ 28.0 28 1

Seq Scan on "TEXT_READ_BY_USER" "TRU" (cost=0.00..1.01 rows=1 width=33) (actual time=0.008..0.013 rows=28 loops=1)

  • Filter: (("USER_ID")::text = 'ac680c09724b36bd3e5f81c2a8caa41a'::text)
31.          

SubPlan (for GroupAggregate)

32. 1.037 35.258 ↑ 2.0 1 1,037

GroupAggregate (cost=6.78..18.01 rows=2 width=40) (actual time=0.034..0.034 rows=1 loops=1,037)

  • Group Key: ce."COMPANY_ID
33. 0.827 34.221 ↓ 2.0 4 1,037

Nested Loop (cost=6.78..17.98 rows=2 width=40) (actual time=0.021..0.033 rows=4 loops=1,037)

  • Join Filter: ((ce."EVENT_ID")::text = (e."ID")::text)
34. 3.072 18.666 ↓ 1.3 4 1,037

Hash Join (cost=6.63..16.77 rows=3 width=41) (actual time=0.016..0.018 rows=4 loops=1,037)

  • Hash Cond: ((ce."EVENT_ID")::text = ("USER_ROLES_TO_EVENTS_1"."EVENT_ID")::text)
35. 2.074 15.555 ↓ 1.3 4 1,037

Bitmap Heap Scan on "COUNTERS_EVENTS" ce (cost=4.32..14.45 rows=3 width=38) (actual time=0.014..0.015 rows=4 loops=1,037)

  • Recheck Cond: ((("COMPANY_ID")::text = ("TEXT_VIEW"."COMPANY_ID")::text) AND ("DATE" >= '2019-12-01 00:00:00'::timestamp without time zone) AND ("DATE" <= '2020-01-30 00:00:00'::timestamp without time zone))
  • Heap Blocks: exact=1,993
36. 13.481 13.481 ↓ 1.3 4 1,037

Bitmap Index Scan on "IDX_COUNTERS_EVENTS_COMPANY_DATE" (cost=0.00..4.32 rows=3 width=0) (actual time=0.013..0.013 rows=4 loops=1,037)

  • Index Cond: ((("COMPANY_ID")::text = ("TEXT_VIEW"."COMPANY_ID")::text) AND ("DATE" >= '2019-12-01 00:00:00'::timestamp without time zone) AND ("DATE" <= '2020-01-30 00:00:00'::timestamp without time zone))
37. 0.017 0.039 ↑ 1.0 35 1

Hash (cost=1.88..1.88 rows=35 width=3) (actual time=0.039..0.039 rows=35 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 10kB
38. 0.022 0.022 ↑ 1.0 35 1

Seq Scan on "USER_ROLES_TO_EVENTS" "USER_ROLES_TO_EVENTS_1" (cost=0.00..1.88 rows=35 width=3) (actual time=0.015..0.022 rows=35 loops=1)

  • Filter: (("USER_ROLE_ID")::text = '0'::text)
  • Rows Removed by Filter: 35
39. 14.728 14.728 ↑ 1.0 1 3,682

Index Only Scan using "IDX_EVENTS_ID_STATUS" on "EVENTS" e (cost=0.14..0.39 rows=1 width=7) (actual time=0.004..0.004 rows=1 loops=3,682)

  • Index Cond: ("ID" = ("USER_ROLES_TO_EVENTS_1"."EVENT_ID")::text)
  • Heap Fetches: 0
Planning time : 4.907 ms
Execution time : 265.750 ms