explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 3yOW

Settings
# exclusive inclusive rows x rows loops node
1. 0.016 671.401 ↑ 1.0 10 1

Limit (cost=27,376.91..27,377.13 rows=10 width=232) (actual time=671.382..671.401 rows=10 loops=1)

2. 0.026 671.385 ↑ 1,222.5 11 1

Unique (cost=27,376.89..27,679.47 rows=13,448 width=232) (actual time=671.373..671.385 rows=11 loops=1)

3. 408.433 671.359 ↑ 1,120.7 12 1

Sort (cost=27,376.89..27,410.51 rows=13,448 width=232) (actual time=671.357..671.359 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. 7.072 262.926 ↓ 1.0 13,449 1

WindowAgg (cost=26,219.34..26,454.68 rows=13,448 width=232) (actual time=254.846..262.926 rows=13,449 loops=1)

5. 5.773 255.854 ↓ 1.0 13,449 1

Sort (cost=26,219.34..26,252.96 rows=13,448 width=306) (actual time=254.834..255.854 rows=13,449 loops=1)

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

Hash Left Join (cost=22,382.43..23,365.14 rows=13,448 width=306) (actual time=236.727..250.081 rows=13,449 loops=1)

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

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

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

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

  • Hash Cond: ((c."INN")::text = ("USER_TO_COMPANY"."COMPANY_INN")::text)
9. 3.668 3.668 ↑ 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.668 rows=13,460 loops=1)

10. 3.057 5.827 ↑ 1.0 13,448 1

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

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

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

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

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

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

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

14. 0.260 230.824 ↓ 74.1 1,037 1

Hash (cost=21,832.01..21,832.01 rows=14 width=56) (actual time=230.823..230.824 rows=1,037 loops=1)

  • Buckets: 2048 (originally 1024) Batches: 1 (originally 1) Memory Usage: 111kB
15. 0.157 230.564 ↓ 74.1 1,037 1

Subquery Scan on r (cost=21,831.84..21,832.01 rows=14 width=56) (actual time=230.341..230.564 rows=1,037 loops=1)

16. 0.576 230.407 ↓ 74.1 1,037 1

Sort (cost=21,831.84..21,831.87 rows=14 width=56) (actual time=230.339..230.407 rows=1,037 loops=1)

  • Sort Key: ((SubPlan 1)), (count(DISTINCT "TEXT_VIEW"."TEXT_ID")) DESC
  • Sort Method: quicksort Memory: 194kB
17. 17.840 229.831 ↓ 74.1 1,037 1

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

  • Group Key: "TEXT_VIEW"."COMPANY_ID
18. 34.299 175.696 ↓ 384.4 5,381 1

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

  • Sort Key: "TEXT_VIEW"."COMPANY_ID
  • Sort Method: quicksort Memory: 948kB
19. 1.147 141.397 ↓ 384.4 5,381 1

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

  • Hash Cond: (("TEXT_VIEW"."TEXT_ID")::text = ("TRU"."TEXT_ID")::text)
20. 30.443 140.190 ↓ 384.4 5,381 1

Hash Join (cost=8,747.47..21,577.85 rows=14 width=69) (actual time=100.443..140.190 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))
21. 2.024 14.762 ↑ 14.6 7,670 1

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

  • Hash Cond: (((unnest("TEXT_VIEW"."EVENTS_IDS")))::text = ("USER_ROLES_TO_EVENTS"."EVENT_ID")::text)
22. 2.833 12.714 ↑ 77.1 8,284 1

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

23. 2.210 9.881 ↑ 1.0 6,377 1

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

  • Hash Cond: (("TEXT_VIEW"."INN")::text = ("USER_TO_COMPANY_1"."COMPANY_INN")::text)
24. 2.517 2.517 ↑ 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.517 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
25. 2.538 5.154 ↑ 1.0 13,448 1

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

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

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

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

  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
28. 0.014 0.014 ↑ 1.0 35 1

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

  • Filter: (("USER_ROLE_ID")::text = '0'::text)
  • Rows Removed by Filter: 35
29. 49.623 94.985 ↓ 182.6 127,092 1

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

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

  • Filter: (("ID")::text = ("MASTER_GUID")::text)
  • Rows Removed by Filter: 12069
31. 0.036 0.060 ↓ 28.0 28 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
32. 0.024 0.024 ↓ 28.0 28 1

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

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

SubPlan (for GroupAggregate)

34. 2.074 36.295 ↑ 2.0 1 1,037

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

  • Group Key: ce."COMPANY_ID
35. 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)
36. 3.069 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)
37. 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-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
38. 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-11-01 00:00:00'::timestamp without time zone) AND ("DATE" <= '2020-01-30 00:00:00'::timestamp without time zone))
39. 0.019 0.042 ↑ 1.0 35 1

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

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

  • Filter: (("USER_ROLE_ID")::text = '0'::text)
  • Rows Removed by Filter: 35
41. 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 : 6.152 ms
Execution time : 673.450 ms