explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Hzr

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

Limit (cost=39,083.12..39,083.14 rows=10 width=142) (actual time=330.537..330.549 rows=10 loops=1)

2. 4.068 330.535 ↑ 1,222.5 11 1

Sort (cost=39,083.12..39,116.74 rows=13,448 width=142) (actual time=330.534..330.535 rows=11 loops=1)

  • Sort Key: ((SubPlan 2)), ((SubPlan 1)) 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. 5.911 326.467 ↑ 1.0 13,448 1

Hash Join (cost=22,594.66..38,783.26 rows=13,448 width=142) (actual time=241.100..326.467 rows=13,448 loops=1)

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

Hash Right Join (cost=22,045.46..24,246.42 rows=13,460 width=126) (actual time=235.661..288.255 rows=13,460 loops=1)

  • Hash Cond: (("TEXT_VIEW"."COMPANY_ID")::text = (c."ID")::text)
5. 15.550 276.557 ↓ 8.5 1,037 1

GroupAggregate (cost=21,015.61..23,215.03 rows=122 width=56) (actual time=227.305..276.557 rows=1,037 loops=1)

  • Group Key: "TEXT_VIEW"."COMPANY_ID
6. 33.156 227.823 ↓ 44.1 5,381 1

Sort (cost=21,015.61..21,015.91 rows=122 width=102) (actual time=227.126..227.823 rows=5,381 loops=1)

  • Sort Key: "TEXT_VIEW"."COMPANY_ID
  • Sort Method: quicksort Memory: 948kB
7. 1.269 194.667 ↓ 44.1 5,381 1

Hash Left Join (cost=8,746.75..21,011.38 rows=122 width=102) (actual time=130.546..194.667 rows=5,381 loops=1)

  • Hash Cond: (("TEXT_VIEW"."TEXT_ID")::text = ("TRU"."TEXT_ID")::text)
8. 1.507 193.379 ↓ 44.1 5,381 1

Hash Join (cost=8,745.73..21,010.04 rows=122 width=69) (actual time=130.521..193.379 rows=5,381 loops=1)

  • Hash Cond: (((unnest("TEXT_VIEW"."EVENTS_IDS")))::text = ("USER_ROLES_TO_EVENTS"."EVENT_ID")::text)
9. 53.426 191.841 ↓ 8.2 5,742 1

Hash Join (cost=8,743.41..21,005.86 rows=696 width=101) (actual time=130.472..191.841 rows=5,742 loops=1)

  • Hash Cond: (("TEXT_VIEW"."TEXT_TO_COMPANY_ID")::text = (ttc."ID")::text)
10. 2.993 13.416 ↑ 77.1 8,284 1

ProjectSet (cost=549.20..4,753.10 rows=638,300 width=144) (actual time=5.120..13.416 rows=8,284 loops=1)

11. 2.441 10.423 ↑ 1.0 6,377 1

Hash Join (cost=549.20..1,513.73 rows=6,383 width=142) (actual time=5.116..10.423 rows=6,377 loops=1)

  • Hash Cond: (("TEXT_VIEW"."INN")::text = ("USER_TO_COMPANY_1"."COMPANY_INN")::text)
12. 2.987 2.987 ↑ 1.0 6,382 1

Seq Scan on "TEXT_VIEW" (cost=0.00..947.77 rows=6,383 width=153) (actual time=0.006..2.987 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
13. 2.434 4.995 ↑ 1.0 13,448 1

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

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

  • Filter: (("USER_ID")::text = 'ac680c09724b36bd3e5f81c2a8caa41a'::text)
15. 66.705 124.999 ↓ 182.6 127,092 1

Hash (cost=8,185.51..8,185.51 rows=696 width=88) (actual time=124.998..124.999 rows=127,092 loops=1)

  • Buckets: 65536 (originally 1024) Batches: 8 (originally 1) Memory Usage: 3840kB
16. 58.294 58.294 ↓ 182.6 127,092 1

Seq Scan on "TEXT_TO_COMPANY" ttc (cost=0.00..8,185.51 rows=696 width=88) (actual time=0.008..58.294 rows=127,092 loops=1)

  • Filter: (("ID")::text = ("MASTER_GUID")::text)
  • Rows Removed by Filter: 12069
17. 0.010 0.031 ↑ 1.0 35 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
18. 0.021 0.021 ↑ 1.0 35 1

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

  • Filter: (("USER_ROLE_ID")::text = '0'::text)
  • Rows Removed by Filter: 35
19. 0.005 0.019 ↓ 28.0 28 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
20. 0.014 0.014 ↓ 28.0 28 1

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

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

SubPlan (for GroupAggregate)

22. 1.037 33.184 ↑ 2.0 1 1,037

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

  • Group Key: ce."COMPANY_ID
23. 3.472 32.147 ↓ 2.0 4 1,037

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

  • Join Filter: ((ce."EVENT_ID")::text = (e."ID")::text)
24. 2.004 17.629 ↓ 1.3 4 1,037

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

  • Hash Cond: ((ce."EVENT_ID")::text = ("USER_ROLES_TO_EVENTS_1"."EVENT_ID")::text)
25. 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
26. 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))
27. 0.041 0.070 ↑ 1.0 35 1

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

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

  • Filter: (("USER_ROLE_ID")::text = '0'::text)
  • Rows Removed by Filter: 35
29. 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
30. 4.582 8.276 ↑ 1.0 13,460 1

Hash (cost=861.60..861.60 rows=13,460 width=106) (actual time=8.276..8.276 rows=13,460 loops=1)

  • Buckets: 16384 Batches: 1 Memory Usage: 1968kB
31. 3.694 3.694 ↑ 1.0 13,460 1

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

32. 2.690 5.405 ↑ 1.0 13,448 1

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

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

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

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

SubPlan (for Hash Join)

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

36. 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 : 5.723 ms
Execution time : 332.098 ms