explain.depesz.com

PostgreSQL's explain analyze made readable

Result: tth5

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

Limit (cost=1,265,495.75..1,265,495.77 rows=10 width=232) (actual time=413.964..413.975 rows=10 loops=1)

2. 2.926 413.963 ↑ 6,129.5 11 1

Sort (cost=1,265,495.74..1,265,664.31 rows=67,425 width=232) (actual time=413.963..413.963 rows=11 loops=1)

  • Sort Key: ((SubPlan 1)), (count(ctc."COMPANY_TO_ID") OVER (?)) DESC, (count(DISTINCT "TEXT_VIEW"."TEXT_ID")) DESC NULLS LAST, (count(DISTINCT "TRU"."TEXT_ID")) DESC NULLS LAST, c."NAME", c."ID
  • Sort Method: top-N heapsort Memory: 29kB
3. 6.247 411.037 ↑ 5.0 13,449 1

WindowAgg (cost=1,262,812.42..1,263,992.36 rows=67,425 width=232) (actual time=403.828..411.037 rows=13,449 loops=1)

4. 5.363 404.790 ↑ 5.0 13,449 1

Sort (cost=1,262,812.42..1,262,980.98 rows=67,425 width=306) (actual time=403.820..404.790 rows=13,449 loops=1)

  • Sort Key: ctc."COMPANY_FROM_ID
  • Sort Method: quicksort Memory: 2966kB
5. 14.635 399.427 ↑ 5.0 13,449 1

Merge Left Join (cost=19,834.85..1,247,723.60 rows=67,425 width=306) (actual time=50.086..399.427 rows=13,449 loops=1)

  • Merge Cond: ((c."ID")::text = ("TEXT_VIEW"."COMPANY_ID")::text)
6. 13.447 289.156 ↓ 1.0 13,449 1

Merge Left Join (cost=0.82..9,625.22 rows=13,448 width=286) (actual time=1.712..289.156 rows=13,449 loops=1)

  • Merge Cond: ((c."ID")::text = (ctc."COMPANY_FROM_ID")::text)
7. 5.973 275.158 ↑ 1.0 13,448 1

Nested Loop (cost=0.70..9,579.42 rows=13,448 width=106) (actual time=1.170..275.158 rows=13,448 loops=1)

8. 107.665 107.665 ↑ 1.0 13,460 1

Index Scan using "IDX_COMPANIES_ID" on "COMPANIES" c (cost=0.41..4,142.23 rows=13,460 width=106) (actual time=0.022..107.665 rows=13,460 loops=1)

9. 161.520 161.520 ↑ 1.0 1 13,460

Index Scan using "IDX_USER_TO_COMPANY_INN" on "USER_TO_COMPANY" (cost=0.29..0.40 rows=1 width=11) (actual time=0.012..0.012 rows=1 loops=13,460)

  • Index Cond: (("COMPANY_INN")::text = (c."INN")::text)
  • Filter: (("USER_ID")::text = 'ac680c09724b36bd3e5f81c2a8caa41a'::text)
10. 0.551 0.551 ↓ 3.0 6 1

Index Only Scan using "IDX_COMPANY_TO_COMPANY" on "COMPANY_TO_COMPANY" ctc (cost=0.13..12.16 rows=2 width=180) (actual time=0.537..0.551 rows=6 loops=1)

  • Heap Fetches: 7
11. 15.509 95.636 ↑ 65.1 1,037 1

GroupAggregate (cost=19,834.02..1,236,546.95 rows=67,485 width=56) (actual time=48.370..95.636 rows=1,037 loops=1)

  • Group Key: "TEXT_VIEW"."COMPANY_ID
12. 34.226 49.017 ↑ 14.2 5,381 1

Sort (cost=19,834.02..20,025.52 rows=76,598 width=102) (actual time=48.225..49.017 rows=5,381 loops=1)

  • Sort Key: "TEXT_VIEW"."COMPANY_ID
  • Sort Method: quicksort Memory: 948kB
13. 1.097 14.791 ↑ 14.2 5,381 1

Hash Left Join (cost=552.54..9,430.50 rows=76,598 width=102) (actual time=6.455..14.791 rows=5,381 loops=1)

  • Hash Cond: (("TEXT_VIEW"."TEXT_ID")::text = ("TRU"."TEXT_ID")::text)
14. 1.497 13.639 ↑ 14.2 5,381 1

Hash Join (cost=551.51..9,228.41 rows=76,598 width=69) (actual time=6.381..13.639 rows=5,381 loops=1)

  • Hash Cond: (((unnest("TEXT_VIEW"."EVENTS_IDS")))::text = ("USER_ROLES_TO_EVENTS"."EVENT_ID")::text)
15. 1.994 12.120 ↑ 76.5 5,722 1

ProjectSet (cost=549.20..3,675.79 rows=437,700 width=101) (actual time=6.351..12.120 rows=5,722 loops=1)

16. 1.458 10.126 ↑ 1.0 4,294 1

Hash Join (cost=549.20..1,454.46 rows=4,377 width=99) (actual time=6.327..10.126 rows=4,294 loops=1)

  • Hash Cond: (("TEXT_VIEW"."INN")::text = ("USER_TO_COMPANY_1"."COMPANY_INN")::text)
17. 2.405 2.405 ↑ 1.0 4,294 1

Seq Scan on "TEXT_VIEW" (cost=0.00..893.77 rows=4,377 width=110) (actual time=0.022..2.405 rows=4,294 loops=1)

  • Filter: ((NOT "IS_DOUBLE") AND ("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: 6291
18. 3.123 6.263 ↑ 1.0 13,448 1

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

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

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

Hash (cost=1.88..1.88 rows=35 width=3) (actual time=0.022..0.022 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. 0.021 0.055 ↓ 28.0 28 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
23. 0.034 0.034 ↓ 28.0 28 1

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

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

SubPlan (for GroupAggregate)

25. 1.037 31.110 ↑ 2.0 1 1,037

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

  • Group Key: ce."COMPANY_ID
26. 2.435 30.073 ↓ 2.0 4 1,037

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

  • Join Filter: ((ce."EVENT_ID")::text = (e."ID")::text)
27. 3.074 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)
28. 2.074 13.481 ↓ 1.3 4 1,037

Bitmap Heap Scan on "COUNTERS_EVENTS" ce (cost=4.32..14.45 rows=3 width=38) (actual time=0.012..0.013 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
29. 11.407 11.407 ↓ 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.011..0.011 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))
30. 0.014 0.037 ↑ 1.0 35 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
31. 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
32. 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 : 2.246 ms
Execution time : 414.670 ms