explain.depesz.com

PostgreSQL's explain analyze made readable

Result: smHY

Settings
# exclusive inclusive rows x rows loops node
1. 0.009 305.579 ↑ 1.0 10 1

Limit (cost=25,839.30..25,839.32 rows=10 width=142) (actual time=305.570..305.579 rows=10 loops=1)

2. 5.266 305.570 ↑ 1,222.5 11 1

Sort (cost=25,839.30..25,872.92 rows=13,448 width=142) (actual time=305.569..305.570 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. 1.651 300.304 ↑ 1.0 13,448 1

Hash Left Join (cost=10,654.89..25,539.44 rows=13,448 width=142) (actual time=259.436..300.304 rows=13,448 loops=1)

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

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

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

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

6. 3.425 8.927 ↑ 1.0 13,448 1

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

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

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

  • Filter: (("USER_ID")::text = 'ac680c09724b36bd3e5f81c2a8caa41a'::text)
8. 0.402 249.484 ↓ 2.1 1,037 1

Hash (cost=10,099.53..10,099.53 rows=493 width=56) (actual time=249.484..249.484 rows=1,037 loops=1)

  • Buckets: 2048 (originally 1024) Batches: 1 (originally 1) Memory Usage: 111kB
9. 0.257 249.082 ↓ 2.1 1,037 1

Subquery Scan on r (cost=1,206.11..10,099.53 rows=493 width=56) (actual time=198.254..249.082 rows=1,037 loops=1)

10. 15.916 248.825 ↓ 2.1 1,037 1

GroupAggregate (cost=1,206.11..10,094.60 rows=493 width=56) (actual time=198.253..248.825 rows=1,037 loops=1)

  • Group Key: "TEXT_VIEW"."COMPANY_ID
11. 31.296 198.688 ↓ 9.6 5,381 1

Sort (cost=1,206.11..1,207.51 rows=560 width=102) (actual time=198.001..198.688 rows=5,381 loops=1)

  • Sort Key: "TEXT_VIEW"."COMPANY_ID
  • Sort Method: quicksort Memory: 948kB
12. 13.544 167.392 ↓ 9.6 5,381 1

Nested Loop Left Join (cost=2.72..1,180.54 rows=560 width=102) (actual time=2.879..167.392 rows=5,381 loops=1)

  • Join Filter: (("TRU"."TEXT_ID")::text = ("TEXT_VIEW"."TEXT_ID")::text)
  • Rows Removed by Join Filter: 147729
13. 1.924 143.086 ↓ 9.6 5,381 1

Hash Join (cost=2.72..1,171.13 rows=560 width=69) (actual time=2.810..143.086 rows=5,381 loops=1)

  • Hash Cond: (((unnest("TEXT_VIEW"."EVENTS_IDS")))::text = ("USER_ROLES_TO_EVENTS"."EVENT_ID")::text)
14. 2.595 141.105 ↓ 1.8 5,742 1

ProjectSet (cost=0.41..1,128.24 rows=3,200 width=101) (actual time=2.684..141.105 rows=5,742 loops=1)

15. 0.807 138.510 ↓ 134.8 4,314 1

Nested Loop (cost=0.41..1,112.00 rows=32 width=99) (actual time=2.666..138.510 rows=4,314 loops=1)

16. 12.452 12.452 ↓ 135.0 4,319 1

Seq Scan on "TEXT_VIEW" (cost=0.00..974.24 rows=32 width=110) (actual time=0.034..12.452 rows=4,319 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) AND (("TEXT_TO_COMPANY_ID")::text = ("MASTER_GUID")::text))
  • Rows Removed by Filter: 6266
17. 125.251 125.251 ↑ 1.0 1 4,319

Index Only Scan using "IDX_USER_TO_COMPANY_ID_INN" on "USER_TO_COMPANY" "USER_TO_COMPANY_1" (cost=0.41..4.30 rows=1 width=11) (actual time=0.029..0.029 rows=1 loops=4,319)

  • Index Cond: (("USER_ID" = 'ac680c09724b36bd3e5f81c2a8caa41a'::text) AND ("COMPANY_INN" = ("TEXT_VIEW"."INN")::text))
  • Heap Fetches: 0
18. 0.016 0.057 ↑ 1.0 35 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
19. 0.041 0.041 ↑ 1.0 35 1

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

  • Filter: (("USER_ROLE_ID")::text = '0'::text)
  • Rows Removed by Filter: 35
20. 10.730 10.762 ↓ 27.0 27 5,381

Materialize (cost=0.00..1.02 rows=1 width=33) (actual time=0.000..0.002 rows=27 loops=5,381)

21. 0.032 0.032 ↓ 28.0 28 1

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

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

SubPlan (for GroupAggregate)

23. 1.037 34.221 ↑ 2.0 1 1,037

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

  • Group Key: ce."COMPANY_ID
24. 2.435 33.184 ↓ 2.0 4 1,037

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

  • Join Filter: ((ce."EVENT_ID")::text = (e."ID")::text)
25. 3.085 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)
26. 3.111 16.592 ↓ 1.3 4 1,037

Bitmap Heap Scan on "COUNTERS_EVENTS" ce (cost=4.32..14.45 rows=3 width=38) (actual time=0.015..0.016 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
27. 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))
28. 0.006 0.026 ↑ 1.0 35 1

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

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

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

SubPlan (for Hash Left Join)

32. 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)

33. 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 : 8.195 ms
Execution time : 308.077 ms