explain.depesz.com

PostgreSQL's explain analyze made readable

Result: XqoH

Settings
# exclusive inclusive rows x rows loops node
1. 0.012 580.441 ↑ 1.0 10 1

Limit (cost=25,952.73..25,952.95 rows=10 width=232) (actual time=580.426..580.441 rows=10 loops=1)

2. 0.009 580.429 ↑ 1,222.5 11 1

Unique (cost=25,952.71..26,255.29 rows=13,448 width=232) (actual time=580.420..580.429 rows=11 loops=1)

3. 321.065 580.420 ↑ 1,120.7 12 1

Sort (cost=25,952.71..25,986.33 rows=13,448 width=232) (actual time=580.418..580.420 rows=12 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", c."INN", c."NEWS_SUPPLIED
  • Sort Method: quicksort Memory: 3280kB
4. 6.466 259.355 ↓ 1.0 13,449 1

WindowAgg (cost=24,795.16..25,030.50 rows=13,448 width=232) (actual time=252.034..259.355 rows=13,449 loops=1)

5. 4.900 252.889 ↓ 1.0 13,449 1

Sort (cost=24,795.16..24,828.78 rows=13,448 width=306) (actual time=252.020..252.889 rows=13,449 loops=1)

  • Sort Key: ctc."COMPANY_FROM_ID
  • Sort Method: quicksort Memory: 2966kB
6. 3.410 247.989 ↓ 1.0 13,449 1

Hash Left Join (cost=20,958.25..21,940.96 rows=13,448 width=306) (actual time=236.605..247.989 rows=13,449 loops=1)

  • Hash Cond: ((c."ID")::text = (r."COMPANY_ID")::text)
7. 2.226 13.574 ↓ 1.0 13,449 1

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

  • Hash Cond: ((c."ID")::text = (ctc."COMPANY_FROM_ID")::text)
8. 3.622 11.336 ↑ 1.0 13,448 1

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

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

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

10. 2.229 4.686 ↑ 1.0 13,448 1

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

  • Buckets: 16384 Batches: 1 Memory Usage: 694kB
11. 2.457 2.457 ↑ 1.0 13,448 1

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

  • Filter: (("USER_ID")::text = 'ac680c09724b36bd3e5f81c2a8caa41a'::text)
12. 0.007 0.012 ↓ 3.0 6 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
13. 0.005 0.005 ↓ 3.0 6 1

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

14. 0.459 231.005 ↓ 79.8 1,037 1

Hash (cost=20,407.85..20,407.85 rows=13 width=56) (actual time=231.005..231.005 rows=1,037 loops=1)

  • Buckets: 2048 (originally 1024) Batches: 1 (originally 1) Memory Usage: 111kB
15. 0.196 230.546 ↓ 79.8 1,037 1

Subquery Scan on r (cost=20,173.35..20,407.85 rows=13 width=56) (actual time=181.087..230.546 rows=1,037 loops=1)

16. 16.572 230.350 ↓ 79.8 1,037 1

GroupAggregate (cost=20,173.35..20,407.72 rows=13 width=56) (actual time=181.085..230.350 rows=1,037 loops=1)

  • Group Key: "TEXT_VIEW"."COMPANY_ID
17. 32.782 181.631 ↓ 413.9 5,381 1

Sort (cost=20,173.35..20,173.38 rows=13 width=102) (actual time=180.916..181.631 rows=5,381 loops=1)

  • Sort Key: "TEXT_VIEW"."COMPANY_ID
  • Sort Method: quicksort Memory: 948kB
18. 1.123 148.849 ↓ 413.9 5,381 1

Hash Left Join (cost=8,569.26..20,173.11 rows=13 width=102) (actual time=111.968..148.849 rows=5,381 loops=1)

  • Hash Cond: (("TEXT_VIEW"."TEXT_ID")::text = ("TRU"."TEXT_ID")::text)
19. 28.760 147.670 ↓ 413.9 5,381 1

Hash Join (cost=8,568.24..20,172.05 rows=13 width=69) (actual time=111.881..147.670 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))
20. 2.007 15.889 ↑ 13.5 7,670 1

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

  • Hash Cond: (((unnest("TEXT_VIEW"."EVENTS_IDS")))::text = ("USER_ROLES_TO_EVENTS"."EVENT_ID")::text)
21. 2.843 13.834 ↑ 71.4 8,264 1

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

22. 2.074 10.991 ↓ 1.1 6,357 1

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

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

Seq Scan on "TEXT_VIEW" (cost=0.00..562.97 rows=5,904 width=110) (actual time=0.044..1.920 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: 4228
24. 3.689 6.997 ↑ 1.0 13,448 1

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

  • Buckets: 16384 Batches: 1 Memory Usage: 694kB
25. 3.308 3.308 ↑ 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..3.308 rows=13,448 loops=1)

  • Filter: (("USER_ID")::text = 'ac680c09724b36bd3e5f81c2a8caa41a'::text)
26. 0.021 0.048 ↑ 1.0 35 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
27. 0.027 0.027 ↑ 1.0 35 1

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

  • Filter: (("USER_ROLE_ID")::text = '0'::text)
  • Rows Removed by Filter: 35
28. 52.511 103.021 ↓ 203.3 127,092 1

Hash (cost=8,007.35..8,007.35 rows=625 width=55) (actual time=103.021..103.021 rows=127,092 loops=1)

  • Buckets: 65536 (originally 1024) Batches: 4 (originally 1) Memory Usage: 3585kB
29. 50.510 50.510 ↓ 203.3 127,092 1

Seq Scan on "TEXT_TO_COMPANY" ttc (cost=0.00..8,007.35 rows=625 width=55) (actual time=0.895..50.510 rows=127,092 loops=1)

  • Filter: (("ID")::text = ("MASTER_GUID")::text)
  • Rows Removed by Filter: 12069
30. 0.029 0.056 ↓ 28.0 28 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
31. 0.027 0.027 ↓ 28.0 28 1

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

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

SubPlan (for GroupAggregate)

33. 1.037 32.147 ↑ 2.0 1 1,037

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

  • Group Key: ce."COMPANY_ID
34. 3.472 31.110 ↓ 2.0 4 1,037

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

  • Join Filter: ((ce."EVENT_ID")::text = (e."ID")::text)
35. 2.023 16.592 ↓ 1.3 4 1,037

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

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

Bitmap Heap Scan on "COUNTERS_EVENTS" ce (cost=4.32..14.45 rows=3 width=38) (actual time=0.013..0.014 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=1993
37. 12.444 12.444 ↓ 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.012..0.012 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))
38. 0.024 0.051 ↑ 1.0 35 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
39. 0.027 0.027 ↑ 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.019..0.027 rows=35 loops=1)

  • Filter: (("USER_ROLE_ID")::text = '0'::text)
  • Rows Removed by Filter: 35
40. 11.046 11.046 ↑ 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.003..0.003 rows=1 loops=3,682)

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