explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 6pMd

Settings
# exclusive inclusive rows x rows loops node
1. 0.011 293.091 ↑ 1.0 10 1

Limit (cost=37,565.50..37,565.52 rows=10 width=142) (actual time=293.081..293.091 rows=10 loops=1)

2. 3.970 293.080 ↑ 1,222.5 11 1

Sort (cost=37,565.50..37,599.12 rows=13,448 width=142) (actual time=293.080..293.080 rows=11 loops=1)

  • Sort Key: r."MOST_CRITICAL", ((SubPlan 1)) 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. 3.445 289.110 ↑ 1.0 13,448 1

Hash Left Join (cost=22,381.09..37,265.64 rows=13,448 width=142) (actual time=251.410..289.110 rows=13,448 loops=1)

  • Hash Cond: ((c."ID")::text = (r."COMPANY_ID")::text)
4. 4.268 12.315 ↑ 1.0 13,448 1

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

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

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

6. 2.329 4.911 ↑ 1.0 13,448 1

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

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

  • Filter: (("USER_ID")::text = 'ac680c09724b36bd3e5f81c2a8caa41a'::text)
8. 1.418 246.454 ↓ 74.1 1,037 1

Hash (cost=21,831.71..21,831.71 rows=14 width=56) (actual time=246.453..246.454 rows=1,037 loops=1)

  • Buckets: 2048 (originally 1024) Batches: 1 (originally 1) Memory Usage: 111kB
9. 0.195 245.036 ↓ 74.1 1,037 1

Subquery Scan on r (cost=21,579.18..21,831.71 rows=14 width=56) (actual time=196.737..245.036 rows=1,037 loops=1)

10. 15.414 244.841 ↓ 74.1 1,037 1

GroupAggregate (cost=21,579.18..21,831.57 rows=14 width=56) (actual time=196.736..244.841 rows=1,037 loops=1)

  • Group Key: "TEXT_VIEW"."COMPANY_ID
11. 36.421 197.280 ↓ 384.4 5,381 1

Sort (cost=21,579.18..21,579.22 rows=14 width=102) (actual time=196.582..197.280 rows=5,381 loops=1)

  • Sort Key: "TEXT_VIEW"."COMPANY_ID
  • Sort Method: quicksort Memory: 948kB
12. 1.138 160.859 ↓ 384.4 5,381 1

Hash Left Join (cost=8,748.49..21,578.91 rows=14 width=102) (actual time=124.048..160.859 rows=5,381 loops=1)

  • Hash Cond: (("TEXT_VIEW"."TEXT_ID")::text = ("TRU"."TEXT_ID")::text)
13. 26.576 159.681 ↓ 384.4 5,381 1

Hash Join (cost=8,747.47..21,577.85 rows=14 width=69) (actual time=124.000..159.681 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))
14. 2.005 14.414 ↑ 14.6 7,670 1

Hash Join (cost=551.51..12,795.46 rows=111,702 width=69) (actual time=5.158..14.414 rows=7,670 loops=1)

  • Hash Cond: (((unnest("TEXT_VIEW"."EVENTS_IDS")))::text = ("USER_ROLES_TO_EVENTS"."EVENT_ID")::text)
15. 2.816 12.384 ↑ 77.1 8,284 1

ProjectSet (cost=549.20..4,699.10 rows=638,300 width=101) (actual time=5.101..12.384 rows=8,284 loops=1)

16. 2.055 9.568 ↑ 1.0 6,377 1

Hash Join (cost=549.20..1,459.73 rows=6,383 width=99) (actual time=5.098..9.568 rows=6,377 loops=1)

  • Hash Cond: (("TEXT_VIEW"."INN")::text = ("USER_TO_COMPANY_1"."COMPANY_INN")::text)
17. 2.442 2.442 ↑ 1.0 6,382 1

Seq Scan on "TEXT_VIEW" (cost=0.00..893.77 rows=6,383 width=110) (actual time=0.017..2.442 rows=6,382 loops=1)

  • Filter: (("TEXT_DATE" >= '2019-11-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: 4203
18. 2.445 5.071 ↑ 1.0 13,448 1

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

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

  • Filter: (("USER_ID")::text = 'ac680c09724b36bd3e5f81c2a8caa41a'::text)
20. 0.010 0.025 ↑ 1.0 35 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
21. 0.015 0.015 ↑ 1.0 35 1

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

  • Filter: (("USER_ROLE_ID")::text = '0'::text)
  • Rows Removed by Filter: 35
22. 55.927 118.691 ↓ 182.6 127,092 1

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

  • Buckets: 65536 (originally 1024) Batches: 4 (originally 1) Memory Usage: 3585kB
23. 62.764 62.764 ↓ 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.010..62.764 rows=127,092 loops=1)

  • Filter: (("ID")::text = ("MASTER_GUID")::text)
  • Rows Removed by Filter: 12069
24. 0.023 0.040 ↓ 28.0 28 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
25. 0.017 0.017 ↓ 28.0 28 1

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

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

SubPlan (for GroupAggregate)

27. 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
28. 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)
29. 2.023 16.592 ↓ 1.3 4 1,037

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

  • Hash Cond: ((ce."EVENT_ID")::text = ("USER_ROLES_TO_EVENTS_1"."EVENT_ID")::text)
30. 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-11-01 00:00:00'::timestamp without time zone) AND ("DATE" <= '2020-01-30 00:00:00'::timestamp without time zone))
  • Heap Blocks: exact=1993
31. 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-11-01 00:00:00'::timestamp without time zone) AND ("DATE" <= '2020-01-30 00:00:00'::timestamp without time zone))
32. 0.026 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
33. 0.025 0.025 ↑ 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.017..0.025 rows=35 loops=1)

  • Filter: (("USER_ROLE_ID")::text = '0'::text)
  • Rows Removed by Filter: 35
34. 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
35.          

SubPlan (for Hash Left Join)

36. 13.448 26.896 ↑ 1.0 1 13,448

Aggregate (cost=1.03..1.04 rows=1 width=8) (actual time=0.002..0.002 rows=1 loops=13,448)

37. 13.448 13.448 ↓ 0.0 0 13,448

Seq Scan on "COMPANY_TO_COMPANY" ctc (cost=0.00..1.02 rows=1 width=90) (actual time=0.001..0.001 rows=0 loops=13,448)

  • Filter: (("COMPANY_FROM_ID")::text = (c."ID")::text)
  • Rows Removed by Filter: 6
Planning time : 5.853 ms
Execution time : 295.378 ms