#	exclusive	inclusive	rows x	rows	loops	node
1.	1,294.849	284,561.344	↑ 2,216.7	12,793	1	→ Nested Loop Left Join (cost=3,992.62..46,621,792,021.73 rows=28,357,842 width=1,419) (actual time=74.668..284,561.344 rows=12,793 loops=1)
2.	53.019	2,588.075	↑ 2,216.7	12,793	1	→ Nested Loop Left Join (cost=2,372.34..668,759,795.99 rows=28,357,842 width=1,441) (actual time=52.993..2,588.075 rows=12,793 loops=1)
3.	30.750	2,253.610	↑ 2,216.7	12,793	1	→ Hash Left Join (cost=2,349.23..12,701,121.32 rows=28,357,842 width=1,439) (actual time=52.960..2,253.610 rows=12,793 loops=1) Hash Cond: (prog."sourceId" = s.id)
4.	29.499	2,222.800	↑ 2,216.7	12,793	1	→ Hash Left Join (cost=2,342.30..12,623,006.67 rows=28,357,842 width=1,442) (actual time=52.887..2,222.800 rows=12,793 loops=1) Hash Cond: (tpp."programId" = prog.id)
5.	32.792	2,193.020	↑ 2,216.7	12,793	1	→ Hash Left Join (cost=2,311.27..12,547,652.60 rows=28,357,842 width=1,337) (actual time=52.600..2,193.020 rows=12,793 loops=1) Hash Cond: (c."teamPropertyProgramId" = ctpp.id)
6.	27.288	2,158.776	↑ 2,216.7	12,793	1	→ Hash Left Join (cost=2,257.72..12,346,622.36 rows=28,357,842 width=1,229) (actual time=51.142..2,158.776 rows=12,793 loops=1) Hash Cond: (cqs."userId" = cqsu.id)
7.	126.605	2,131.409	↑ 2,216.7	12,793	1	→ Hash Left Join (cost=2,243.74..12,269,828.38 rows=28,357,842 width=1,231) (actual time=51.050..2,131.409 rows=12,793 loops=1) Hash Cond: (c.id = cqs."communicationId")
8.	18.700	1,970.941	↑ 2,216.7	12,793	1	→ Hash Left Join (cost=174.54..3,829,425.94 rows=28,357,842 width=1,179) (actual time=2.087..1,970.941 rows=12,793 loops=1) Hash Cond: (c."userId" = u.id)
9.	374.523	1,952.124	↑ 2,216.7	12,793	1	→ Hash Join (cost=160.56..3,752,519.04 rows=28,357,842 width=1,181) (actual time=1.966..1,952.124 rows=12,793 loops=1) Hash Cond: (p."assignedPropertyId" = prop.id) Join Filter: ((timezone(prop.timezone, c.created_at))::date > ((now())::date + '-1 mons'::interval)) Rows Removed by Join Filter: 338857
10.	227.204	1,577.584	↑ 242.1	351,727	1	→ Nested Loop (cost=158.43..3,405,515.50 rows=85,153,003 width=1,171) (actual time=1.744..1,577.584 rows=351,727 loops=1)
11.	37.615	139.880	↑ 1.0	48,420	1	→ Hash Left Join (cost=144.88..9,832.86 rows=48,972 width=449) (actual time=1.710..139.880 rows=48,420 loops=1) Hash Cond: (p."teamPropertyProgramId" = tpp.id)
12.	37.578	101.982	↑ 1.0	48,420	1	→ Hash Left Join (cost=131.95..9,690.05 rows=48,972 width=449) (actual time=1.422..101.982 rows=48,420 loops=1) Hash Cond: (p.id = lease."partyId")
13.	62.989	62.989	↑ 1.0	48,361	1	→ Seq Scan on "Party" p (cost=0.00..6,663.72 rows=48,972 width=441) (actual time=0.003..62.989 rows=48,361 loops=1)
14.	0.521	1.415	↓ 1.0	1,735	1	→ Hash (cost=110.36..110.36 rows=1,727 width=24) (actual time=1.415..1.415 rows=1,735 loops=1) Buckets: 2048 Batches: 1 Memory Usage: 111kB
15.	0.894	0.894	↓ 1.0	1,735	1	→ Seq Scan on "Lease" lease (cost=0.00..110.36 rows=1,727 width=24) (actual time=0.004..0.894 rows=1,735 loops=1) Filter: ((status)::text = 'executed'::text) Rows Removed by Filter: 1112
16.	0.129	0.283	↓ 3.1	409	1	→ Hash (cost=11.30..11.30 rows=130 width=32) (actual time=0.283..0.283 rows=409 loops=1) Buckets: 1024 Batches: 1 Memory Usage: 34kB
17.	0.154	0.154	↓ 3.1	409	1	→ Seq Scan on "TeamPropertyProgram" tpp (cost=0.00..11.30 rows=130 width=32) (actual time=0.009..0.154 rows=409 loops=1)
18.	823.140	1,210.500	↑ 248.4	7	48,420	→ Bitmap Heap Scan on "Communication" c (cost=13.55..51.95 rows=1,739 width=783) (actual time=0.012..0.025 rows=7 loops=48,420) Recheck Cond: (parties @> ARRAY[(p.id)::character varying]) Heap Blocks: exact=286577
19.	387.360	387.360	↑ 248.4	7	48,420	→ Bitmap Index Scan on communication_parties_gin_idx (cost=0.00..13.12 rows=1,739 width=0) (actual time=0.008..0.008 rows=7 loops=48,420) Index Cond: (parties @> ARRAY[(p.id)::character varying])
20.	0.006	0.017	↑ 1.0	6	1	→ Hash (cost=2.06..2.06 rows=6 width=42) (actual time=0.016..0.017 rows=6 loops=1) Buckets: 1024 Batches: 1 Memory Usage: 9kB
21.	0.011	0.011	↑ 1.0	6	1	→ Seq Scan on "Property" prop (cost=0.00..2.06 rows=6 width=42) (actual time=0.006..0.011 rows=6 loops=1)
22.	0.036	0.117	↑ 1.0	88	1	→ Hash (cost=12.88..12.88 rows=88 width=30) (actual time=0.117..0.117 rows=88 loops=1) Buckets: 1024 Batches: 1 Memory Usage: 14kB
23.	0.081	0.081	↑ 1.0	88	1	→ Seq Scan on "Users" u (cost=0.00..12.88 rows=88 width=30) (actual time=0.007..0.081 rows=88 loops=1)
24.	17.847	33.863	↑ 1.0	39,369	1	→ Hash (cost=1,026.31..1,026.31 rows=39,831 width=84) (actual time=33.863..33.863 rows=39,369 loops=1) Buckets: 32768 Batches: 2 Memory Usage: 2233kB
25.	16.016	16.016	↑ 1.0	39,369	1	→ Seq Scan on "CallQueueStatistics" cqs (cost=0.00..1,026.31 rows=39,831 width=84) (actual time=0.008..16.016 rows=39,369 loops=1)
26.	0.036	0.079	↑ 1.0	88	1	→ Hash (cost=12.88..12.88 rows=88 width=30) (actual time=0.079..0.079 rows=88 loops=1) Buckets: 1024 Batches: 1 Memory Usage: 14kB
27.	0.043	0.043	↑ 1.0	88	1	→ Seq Scan on "Users" cqsu (cost=0.00..12.88 rows=88 width=30) (actual time=0.005..0.043 rows=88 loops=1)
28.	0.174	1.452	↓ 3.1	409	1	→ Hash (cost=51.92..51.92 rows=130 width=140) (actual time=1.452..1.452 rows=409 loops=1) Buckets: 1024 Batches: 1 Memory Usage: 52kB
29.	0.218	1.278	↓ 3.1	409	1	→ Hash Left Join (cost=21.99..51.92 rows=130 width=140) (actual time=0.394..1.278 rows=409 loops=1) Hash Cond: (ctpp."propertyId" = commprop.id)
30.	0.229	1.049	↓ 3.1	409	1	→ Hash Left Join (cost=19.86..49.43 rows=130 width=150) (actual time=0.378..1.049 rows=409 loops=1) Hash Cond: (cprog."sourceId" = cs.id)
31.	0.227	0.740	↓ 3.1	409	1	→ Hash Right Join (cost=12.93..42.13 rows=130 width=153) (actual time=0.292..0.740 rows=409 loops=1) Hash Cond: (cprog.id = ctpp."programId")
32.	0.238	0.238	↓ 1.0	361	1	→ Seq Scan on "Programs" cprog (cost=0.00..26.57 rows=357 width=137) (actual time=0.011..0.238 rows=361 loops=1)
33.	0.139	0.275	↓ 3.1	409	1	→ Hash (cost=11.30..11.30 rows=130 width=48) (actual time=0.275..0.275 rows=409 loops=1) Buckets: 1024 Batches: 1 Memory Usage: 40kB
34.	0.136	0.136	↓ 3.1	409	1	→ Seq Scan on "TeamPropertyProgram" ctpp (cost=0.00..11.30 rows=130 width=48) (actual time=0.005..0.136 rows=409 loops=1)
35.	0.034	0.080	↑ 1.0	86	1	→ Hash (cost=5.86..5.86 rows=86 width=29) (actual time=0.080..0.080 rows=86 loops=1) Buckets: 1024 Batches: 1 Memory Usage: 14kB
36.	0.046	0.046	↑ 1.0	86	1	→ Seq Scan on "Sources" cs (cost=0.00..5.86 rows=86 width=29) (actual time=0.013..0.046 rows=86 loops=1)
37.	0.004	0.011	↑ 1.0	6	1	→ Hash (cost=2.06..2.06 rows=6 width=22) (actual time=0.011..0.011 rows=6 loops=1) Buckets: 1024 Batches: 1 Memory Usage: 9kB
38.	0.007	0.007	↑ 1.0	6	1	→ Seq Scan on "Property" commprop (cost=0.00..2.06 rows=6 width=22) (actual time=0.004..0.007 rows=6 loops=1)
39.	0.138	0.281	↓ 1.0	361	1	→ Hash (cost=26.57..26.57 rows=357 width=137) (actual time=0.281..0.281 rows=361 loops=1) Buckets: 1024 Batches: 1 Memory Usage: 46kB
40.	0.143	0.143	↓ 1.0	361	1	→ Seq Scan on "Programs" prog (cost=0.00..26.57 rows=357 width=137) (actual time=0.002..0.143 rows=361 loops=1)
41.	0.031	0.060	↑ 1.0	86	1	→ Hash (cost=5.86..5.86 rows=86 width=29) (actual time=0.060..0.060 rows=86 loops=1) Buckets: 1024 Batches: 1 Memory Usage: 14kB
42.	0.029	0.029	↑ 1.0	86	1	→ Seq Scan on "Sources" s (cost=0.00..5.86 rows=86 width=29) (actual time=0.003..0.029 rows=86 loops=1)
43.	76.758	281.446	↑ 1.0	1	12,793	→ Aggregate (cost=23.11..23.12 rows=1 width=32) (actual time=0.022..0.022 rows=1 loops=12,793)
44.	204.688	204.688	↑ 1.0	1	12,793	→ Seq Scan on "Teams" t (cost=0.00..23.10 rows=1 width=16) (actual time=0.010..0.016 rows=1 loops=12,793) Filter: (c.teams @> ARRAY[id]) Rows Removed by Filter: 7
45.	89.551	280,678.420	↑ 1.0	1	12,793	→ Aggregate (cost=1,620.28..1,620.29 rows=1 width=32) (actual time=21.939..21.940 rows=1 loops=12,793)
46.	280,588.869	280,588.869	↑ 241.0	1	12,793	→ Seq Scan on "Person" p_1 (cost=0.00..1,619.67 rows=241 width=11) (actual time=18.789..21.933 rows=1 loops=12,793) Filter: (c.persons @> ARRAY[(id)::character varying]) Rows Removed by Filter: 49212

Nested Loop Left Join  (cost=3992.62..46621792021.73 rows=28357842 width=1419) (actual time=74.668..284561.344 rows=12793 loops=1)
  ->  Nested Loop Left Join  (cost=2372.34..668759795.99 rows=28357842 width=1441) (actual time=52.993..2588.075 rows=12793 loops=1)
        ->  Hash Left Join  (cost=2349.23..12701121.32 rows=28357842 width=1439) (actual time=52.960..2253.610 rows=12793 loops=1)
              Hash Cond: (prog."sourceId" = s.id)
              ->  Hash Left Join  (cost=2342.30..12623006.67 rows=28357842 width=1442) (actual time=52.887..2222.800 rows=12793 loops=1)
                    Hash Cond: (tpp."programId" = prog.id)
                    ->  Hash Left Join  (cost=2311.27..12547652.60 rows=28357842 width=1337) (actual time=52.600..2193.020 rows=12793 loops=1)
                          Hash Cond: (c."teamPropertyProgramId" = ctpp.id)
                          ->  Hash Left Join  (cost=2257.72..12346622.36 rows=28357842 width=1229) (actual time=51.142..2158.776 rows=12793 loops=1)
                                Hash Cond: (cqs."userId" = cqsu.id)
                                ->  Hash Left Join  (cost=2243.74..12269828.38 rows=28357842 width=1231) (actual time=51.050..2131.409 rows=12793 loops=1)
                                      Hash Cond: (c.id = cqs."communicationId")
                                      ->  Hash Left Join  (cost=174.54..3829425.94 rows=28357842 width=1179) (actual time=2.087..1970.941 rows=12793 loops=1)
                                            Hash Cond: (c."userId" = u.id)
                                            ->  Hash Join  (cost=160.56..3752519.04 rows=28357842 width=1181) (actual time=1.966..1952.124 rows=12793 loops=1)
                                                  Hash Cond: (p."assignedPropertyId" = prop.id)
                                                  Join Filter: ((timezone(prop.timezone, c.created_at))::date > ((now())::date + '-1 mons'::interval))
                                                  Rows Removed by Join Filter: 338857
                                                  ->  Nested Loop  (cost=158.43..3405515.50 rows=85153003 width=1171) (actual time=1.744..1577.584 rows=351727 loops=1)
                                                        ->  Hash Left Join  (cost=144.88..9832.86 rows=48972 width=449) (actual time=1.710..139.880 rows=48420 loops=1)
                                                              Hash Cond: (p."teamPropertyProgramId" = tpp.id)
                                                              ->  Hash Left Join  (cost=131.95..9690.05 rows=48972 width=449) (actual time=1.422..101.982 rows=48420 loops=1)
                                                                    Hash Cond: (p.id = lease."partyId")
                                                                    ->  Seq Scan on "Party" p  (cost=0.00..6663.72 rows=48972 width=441) (actual time=0.003..62.989 rows=48361 loops=1)
                                                                    ->  Hash  (cost=110.36..110.36 rows=1727 width=24) (actual time=1.415..1.415 rows=1735 loops=1)
                                                                          Buckets: 2048  Batches: 1  Memory Usage: 111kB
                                                                          ->  Seq Scan on "Lease" lease  (cost=0.00..110.36 rows=1727 width=24) (actual time=0.004..0.894 rows=1735 loops=1)
                                                                                Filter: ((status)::text = 'executed'::text)
                                                                                Rows Removed by Filter: 1112
                                                              ->  Hash  (cost=11.30..11.30 rows=130 width=32) (actual time=0.283..0.283 rows=409 loops=1)
                                                                    Buckets: 1024  Batches: 1  Memory Usage: 34kB
                                                                    ->  Seq Scan on "TeamPropertyProgram" tpp  (cost=0.00..11.30 rows=130 width=32) (actual time=0.009..0.154 rows=409 loops=1)
                                                        ->  Bitmap Heap Scan on "Communication" c  (cost=13.55..51.95 rows=1739 width=783) (actual time=0.012..0.025 rows=7 loops=48420)
                                                              Recheck Cond: (parties @> ARRAY[(p.id)::character varying])
                                                              Heap Blocks: exact=286577
                                                              ->  Bitmap Index Scan on communication_parties_gin_idx  (cost=0.00..13.12 rows=1739 width=0) (actual time=0.008..0.008 rows=7 loops=48420)
                                                                    Index Cond: (parties @> ARRAY[(p.id)::character varying])
                                                  ->  Hash  (cost=2.06..2.06 rows=6 width=42) (actual time=0.016..0.017 rows=6 loops=1)
                                                        Buckets: 1024  Batches: 1  Memory Usage: 9kB
                                                        ->  Seq Scan on "Property" prop  (cost=0.00..2.06 rows=6 width=42) (actual time=0.006..0.011 rows=6 loops=1)
                                            ->  Hash  (cost=12.88..12.88 rows=88 width=30) (actual time=0.117..0.117 rows=88 loops=1)
                                                  Buckets: 1024  Batches: 1  Memory Usage: 14kB
                                                  ->  Seq Scan on "Users" u  (cost=0.00..12.88 rows=88 width=30) (actual time=0.007..0.081 rows=88 loops=1)
                                      ->  Hash  (cost=1026.31..1026.31 rows=39831 width=84) (actual time=33.863..33.863 rows=39369 loops=1)
                                            Buckets: 32768  Batches: 2  Memory Usage: 2233kB
                                            ->  Seq Scan on "CallQueueStatistics" cqs  (cost=0.00..1026.31 rows=39831 width=84) (actual time=0.008..16.016 rows=39369 loops=1)
                                ->  Hash  (cost=12.88..12.88 rows=88 width=30) (actual time=0.079..0.079 rows=88 loops=1)
                                      Buckets: 1024  Batches: 1  Memory Usage: 14kB
                                      ->  Seq Scan on "Users" cqsu  (cost=0.00..12.88 rows=88 width=30) (actual time=0.005..0.043 rows=88 loops=1)
                          ->  Hash  (cost=51.92..51.92 rows=130 width=140) (actual time=1.452..1.452 rows=409 loops=1)
                                Buckets: 1024  Batches: 1  Memory Usage: 52kB
                                ->  Hash Left Join  (cost=21.99..51.92 rows=130 width=140) (actual time=0.394..1.278 rows=409 loops=1)
                                      Hash Cond: (ctpp."propertyId" = commprop.id)
                                      ->  Hash Left Join  (cost=19.86..49.43 rows=130 width=150) (actual time=0.378..1.049 rows=409 loops=1)
                                            Hash Cond: (cprog."sourceId" = cs.id)
                                            ->  Hash Right Join  (cost=12.93..42.13 rows=130 width=153) (actual time=0.292..0.740 rows=409 loops=1)
                                                  Hash Cond: (cprog.id = ctpp."programId")
                                                  ->  Seq Scan on "Programs" cprog  (cost=0.00..26.57 rows=357 width=137) (actual time=0.011..0.238 rows=361 loops=1)
                                                  ->  Hash  (cost=11.30..11.30 rows=130 width=48) (actual time=0.275..0.275 rows=409 loops=1)
                                                        Buckets: 1024  Batches: 1  Memory Usage: 40kB
                                                        ->  Seq Scan on "TeamPropertyProgram" ctpp  (cost=0.00..11.30 rows=130 width=48) (actual time=0.005..0.136 rows=409 loops=1)
                                            ->  Hash  (cost=5.86..5.86 rows=86 width=29) (actual time=0.080..0.080 rows=86 loops=1)
                                                  Buckets: 1024  Batches: 1  Memory Usage: 14kB
                                                  ->  Seq Scan on "Sources" cs  (cost=0.00..5.86 rows=86 width=29) (actual time=0.013..0.046 rows=86 loops=1)
                                      ->  Hash  (cost=2.06..2.06 rows=6 width=22) (actual time=0.011..0.011 rows=6 loops=1)
                                            Buckets: 1024  Batches: 1  Memory Usage: 9kB
                                            ->  Seq Scan on "Property" commprop  (cost=0.00..2.06 rows=6 width=22) (actual time=0.004..0.007 rows=6 loops=1)
                    ->  Hash  (cost=26.57..26.57 rows=357 width=137) (actual time=0.281..0.281 rows=361 loops=1)
                          Buckets: 1024  Batches: 1  Memory Usage: 46kB
                          ->  Seq Scan on "Programs" prog  (cost=0.00..26.57 rows=357 width=137) (actual time=0.002..0.143 rows=361 loops=1)
              ->  Hash  (cost=5.86..5.86 rows=86 width=29) (actual time=0.060..0.060 rows=86 loops=1)
                    Buckets: 1024  Batches: 1  Memory Usage: 14kB
                    ->  Seq Scan on "Sources" s  (cost=0.00..5.86 rows=86 width=29) (actual time=0.003..0.029 rows=86 loops=1)
        ->  Aggregate  (cost=23.11..23.12 rows=1 width=32) (actual time=0.022..0.022 rows=1 loops=12793)
              ->  Seq Scan on "Teams" t  (cost=0.00..23.10 rows=1 width=16) (actual time=0.010..0.016 rows=1 loops=12793)
                    Filter: (c.teams @> ARRAY[id])
                    Rows Removed by Filter: 7
  ->  Aggregate  (cost=1620.28..1620.29 rows=1 width=32) (actual time=21.939..21.940 rows=1 loops=12793)
        ->  Seq Scan on "Person" p_1  (cost=0.00..1619.67 rows=241 width=11) (actual time=18.789..21.933 rows=1 loops=12793)
              Filter: (c.persons @> ARRAY[(id)::character varying])
              Rows Removed by Filter: 49212
Planning time: 2.919 ms
Execution time: 284570.894 ms

Per node type stats

node type	count	sum of times	% of query
Aggregate	2	166.309 ms	0.1 %
Bitmap Heap Scan	1	823.140 ms	0.3 %
Bitmap Index Scan	1	387.360 ms	0.1 %
Hash	12	19.095 ms	0.0 %
Hash Join	1	374.523 ms	0.1 %
Hash Left Join	10	341.274 ms	0.1 %
Hash Right Join	1	0.227 ms	0.0 %
Nested Loop	1	227.204 ms	0.1 %
Nested Loop Left Join	2	1,347.868 ms	0.5 %
Seq Scan	15	280,874.344 ms	98.7 %

Per table stats

Table name	Scan count	Total time	% of query
scan type	count	sum of times	% of table
"CallQueueStatistics"	1	16.016 ms	0.0 %
Seq Scan	1	16.016 ms	100.0 %
"Communication"	1	823.140 ms	0.3 %
Bitmap Heap Scan	1	823.140 ms	100.0 %
"Lease"	1	0.894 ms	0.0 %
Seq Scan	1	0.894 ms	100.0 %
"Party"	1	62.989 ms	0.0 %
Seq Scan	1	62.989 ms	100.0 %
"Person"	1	280,588.869 ms	98.6 %
Seq Scan	1	280,588.869 ms	100.0 %
"Programs"	2	0.381 ms	0.0 %
Seq Scan	2	0.381 ms	100.0 %
"Property"	2	0.018 ms	0.0 %
Seq Scan	2	0.018 ms	100.0 %
"Sources"	2	0.075 ms	0.0 %
Seq Scan	2	0.075 ms	100.0 %
"TeamPropertyProgram"	2	0.290 ms	0.0 %
Seq Scan	2	0.290 ms	100.0 %
"Teams"	1	204.688 ms	0.1 %
Seq Scan	1	204.688 ms	100.0 %
"Users"	2	0.124 ms	0.0 %
Seq Scan	2	0.124 ms	100.0 %

explain.depesz.com

PostgreSQL's explain analyze made readable

Result: EmEq

Color mode:

Visible columns:

Per node type stats

Per table stats