explain.depesz.com

PostgreSQL's explain analyze made readable

Result: XYSv

Settings
# exclusive inclusive rows x rows loops node
1. 0.014 648.696 ↑ 1.0 10 1

Limit (cost=1,276,778.20..1,276,778.43 rows=10 width=232) (actual time=648.679..648.696 rows=10 loops=1)

2. 0.011 648.682 ↑ 6,129.5 11 1

Unique (cost=1,276,778.18..1,278,295.24 rows=67,425 width=232) (actual time=648.671..648.682 rows=11 loops=1)

3. 325.418 648.671 ↑ 5,618.8 12 1

Sort (cost=1,276,778.18..1,276,946.74 rows=67,425 width=232) (actual time=648.668..648.671 rows=12 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", c."INN", c."NEWS_SUPPLIED
  • Sort Method: quicksort Memory: 3280kB
4. 6.555 323.253 ↑ 5.0 13,449 1

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

5. 6.284 316.698 ↑ 5.0 13,449 1

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

  • Sort Key: ctc."COMPANY_FROM_ID
  • Sort Method: quicksort Memory: 2966kB
6. 16.468 310.414 ↑ 5.0 13,449 1

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

  • Merge Cond: ((c."ID")::text = ("TEXT_VIEW"."COMPANY_ID")::text)
7. 15.533 192.703 ↓ 1.0 13,449 1

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

  • Merge Cond: ((c."ID")::text = (ctc."COMPANY_FROM_ID")::text)
8. 4.231 177.161 ↑ 1.0 13,448 1

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

9. 11.410 11.410 ↑ 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.007..11.410 rows=13,460 loops=1)

10. 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)
11. 0.009 0.009 ↓ 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.005..0.009 rows=6 loops=1)

  • Heap Fetches: 6
12. 16.811 101.243 ↑ 65.1 1,037 1

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

  • Group Key: "TEXT_VIEW"."COMPANY_ID
13. 36.276 48.137 ↑ 14.2 5,381 1

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

  • Sort Key: "TEXT_VIEW"."COMPANY_ID
  • Sort Method: quicksort Memory: 948kB
14. 0.969 11.861 ↑ 14.2 5,381 1

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

  • Hash Cond: (("TEXT_VIEW"."TEXT_ID")::text = ("TRU"."TEXT_ID")::text)
15. 1.246 10.868 ↑ 14.2 5,381 1

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

  • Hash Cond: (((unnest("TEXT_VIEW"."EVENTS_IDS")))::text = ("USER_ROLES_TO_EVENTS"."EVENT_ID")::text)
16. 1.737 9.602 ↑ 76.5 5,722 1

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

17. 1.098 7.865 ↑ 1.0 4,294 1

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

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

Seq Scan on "TEXT_VIEW" (cost=0.00..893.77 rows=4,377 width=110) (actual time=0.024..2.273 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
19. 2.141 4.494 ↑ 1.0 13,448 1

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

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

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

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

  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
22. 0.013 0.013 ↑ 1.0 35 1

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

  • Filter: (("USER_ROLE_ID")::text = '0'::text)
  • Rows Removed by Filter: 35
23. 0.013 0.024 ↓ 28.0 28 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
24. 0.011 0.011 ↓ 28.0 28 1

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

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

SubPlan (for GroupAggregate)

26. 2.074 36.295 ↑ 2.0 1 1,037

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

  • Group Key: ce."COMPANY_ID
27. 0.000 34.221 ↓ 2.0 4 1,037

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

  • Join Filter: ((ce."EVENT_ID")::text = (e."ID")::text)
28. 4.103 19.703 ↓ 1.3 4 1,037

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

  • Hash Cond: ((ce."EVENT_ID")::text = ("USER_ROLES_TO_EVENTS_1"."EVENT_ID")::text)
29. 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=1993
30. 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))
31. 0.021 0.045 ↑ 1.0 35 1

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

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

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