explain.depesz.com

PostgreSQL's explain analyze made readable

Result: jxS

Settings
# exclusive inclusive rows x rows loops node
1. 0.010 633.873 ↑ 1.0 10 1

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

2. 0.009 633.863 ↑ 6,129.5 11 1

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

3. 328.146 633.854 ↑ 5,618.8 12 1

Sort (cost=1,276,778.18..1,276,946.74 rows=67,425 width=232) (actual time=633.852..633.854 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.939 305.708 ↑ 5.0 13,449 1

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

5. 5.700 298.769 ↑ 5.0 13,449 1

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

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

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

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

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

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

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

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

10. 148.060 148.060 ↑ 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.011..0.011 rows=1 loops=13,460)

  • Index Cond: (("COMPANY_INN")::text = (c."INN")::text)
  • Filter: (("USER_ID")::text = 'ac680c09724b36bd3e5f81c2a8caa41a'::text)
11. 0.007 0.007 ↓ 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.003..0.007 rows=6 loops=1)

  • Heap Fetches: 6
12. 17.416 102.478 ↑ 65.1 1,037 1

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

  • Group Key: "TEXT_VIEW"."COMPANY_ID
13. 35.282 49.804 ↑ 14.2 5,381 1

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

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

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

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

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

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

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

17. 1.461 9.878 ↑ 1.0 4,294 1

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

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

Seq Scan on "TEXT_VIEW" (cost=0.00..893.77 rows=4,377 width=110) (actual time=0.023..3.556 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.336 4.861 ↑ 1.0 13,448 1

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

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

  • Filter: (("USER_ID")::text = 'ac680c09724b36bd3e5f81c2a8caa41a'::text)
21. 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
22. 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.007..0.015 rows=35 loops=1)

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

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

  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
24. 0.012 0.012 ↓ 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.012 rows=28 loops=1)

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

SubPlan (for GroupAggregate)

26. 1.037 35.258 ↑ 2.0 1 1,037

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

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

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

  • Join Filter: ((ce."EVENT_ID")::text = (e."ID")::text)
28. 4.110 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)
29. 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
30. 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))
31. 0.016 0.038 ↑ 1.0 35 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
32. 0.022 0.022 ↑ 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.022 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 : 3.477 ms
Execution time : 634.656 ms