#	exclusive	inclusive	rows x	rows	loops	node
1.	912.589	28,814.803	↓ 25.9	71,587	1	→ Unique (cost=1,047,041.16..1,076,839.86 rows=2,768 width=32) (actual time=18,129.807..28,814.803 rows=71,587 loops=1)
2.						CTE hidden_external_event_ids
3.	1.822	7.404	↑ 12.7	1,758	1	→ Nested Loop (cost=0.43..90,999.65 rows=22,239 width=4) (actual time=0.034..7.404 rows=1,758 loops=1)
4.	0.620	0.620	↑ 1.0	2,481	1	→ Seq Scan on hidden_externals (cost=0.00..40.81 rows=2,481 width=8) (actual time=0.009..0.620 rows=2,481 loops=1)
5.	4.962	4.962	↑ 54.0	1	2,481	→ Index Only Scan using events_persons_org_id_person_id_calendar_id_event_id_key on events_persons events_persons_1 (cost=0.43..36.12 rows=54 width=12) (actual time=0.001..0.002 rows=1 loops=2,481) Index Cond: ((organization_id = hidden_externals.organization_id) AND (person_id = hidden_externals.external_id)) Heap Fetches: 1758
6.						CTE last_events_persons
7.	475.712	6,131.157	↓ 25.0	69,146	1	→ Unique (cost=305,476.07..310,745.74 rows=2,768 width=20) (actual time=4,956.499..6,131.157 rows=69,146 loops=1)
8.	2,439.430	5,655.445	↓ 1.9	2,034,352	1	→ Sort (cost=305,476.07..308,110.90 rows=1,053,934 width=20) (actual time=4,956.496..5,655.445 rows=2,034,352 loops=1) Sort Key: events_persons_2.person_id, events_persons_2.start_time DESC Sort Method: external merge Disk: 67608kB
9.	983.697	3,216.015	↓ 1.9	2,034,352	1	→ Merge Anti Join (cost=2,050.96..200,043.92 rows=1,053,934 width=20) (actual time=9.001..3,216.015 rows=2,034,352 loops=1) Merge Cond: (events_persons_2.event_id = hidden_external_event_ids.event_id)
10.	2,222.700	2,222.700	↑ 1.0	2,081,265	1	→ Index Scan using events_persons_event_id_person_id_key on events_persons events_persons_2 (cost=0.43..186,585.82 rows=2,107,868 width=20) (actual time=0.030..2,222.700 rows=2,081,265 loops=1) Filter: (start_time <= now()) Rows Removed by Filter: 2273092
11.	1.272	9.618	↑ 12.7	1,758	1	→ Sort (cost=2,050.53..2,106.12 rows=22,239 width=4) (actual time=8.968..9.618 rows=1,758 loops=1) Sort Key: hidden_external_event_ids.event_id Sort Method: quicksort Memory: 131kB
12.	8.346	8.346	↑ 12.7	1,758	1	→ CTE Scan on hidden_external_event_ids (cost=0.00..444.78 rows=22,239 width=4) (actual time=0.037..8.346 rows=1,758 loops=1)
13.						CTE next_events_persons
14.	504.663	6,488.908	↑ 2.5	1,101	1	→ Unique (cost=313,675.87..319,292.09 rows=2,768 width=20) (actual time=5,186.136..6,488.908 rows=1,101 loops=1)
15.	2,635.201	5,984.245	↓ 2.0	2,248,911	1	→ Sort (cost=313,675.87..316,483.98 rows=1,123,244 width=20) (actual time=5,186.133..5,984.245 rows=2,248,911 loops=1) Sort Key: events_persons_3.person_id, events_persons_3.start_time Sort Method: external merge Disk: 74736kB
16.	1,066.383	3,349.044	↓ 2.0	2,248,911	1	→ Merge Anti Join (cost=2,050.96..200,794.12 rows=1,123,244 width=20) (actual time=1.463..3,349.044 rows=2,248,911 loops=1) Merge Cond: (events_persons_3.event_id = hidden_external_event_ids_1.event_id)
17.	2,281.045	2,281.045	↓ 1.0	2,273,092	1	→ Index Scan using events_persons_event_id_person_id_key on events_persons events_persons_3 (cost=0.43..186,585.82 rows=2,246,487 width=20) (actual time=0.495..2,281.045 rows=2,273,092 loops=1) Filter: (start_time > now()) Rows Removed by Filter: 2081265
18.	1.187	1.616	↑ 12.7	1,757	1	→ Sort (cost=2,050.53..2,106.12 rows=22,239 width=4) (actual time=0.962..1.616 rows=1,757 loops=1) Sort Key: hidden_external_event_ids_1.event_id Sort Method: quicksort Memory: 131kB
19.	0.429	0.429	↑ 12.7	1,758	1	→ CTE Scan on hidden_external_event_ids hidden_external_event_ids_1 (cost=0.00..444.78 rows=22,239 width=4) (actual time=0.001..0.429 rows=1,758 loops=1)
20.	3,131.098	27,902.214	↓ 2.2	4,353,160	1	→ Merge Left Join (cost=326,003.68..350,893.20 rows=1,963,671 width=32) (actual time=18,129.806..27,902.214 rows=4,353,160 loops=1) Merge Cond: ((events_persons.person_id = next_events_persons.person_id) AND (events_persons.organization_id = next_events_persons.organization_id))
21.	3,311.477	17,389.921	↓ 2.2	4,353,160	1	→ Merge Left Join (cost=325,790.06..340,689.41 rows=1,963,671 width=20) (actual time=11,639.666..17,389.921 rows=4,353,160 loops=1) Merge Cond: ((events_persons.person_id = last_events_persons.person_id) AND (events_persons.organization_id = last_events_persons.organization_id))
22.	3,503.297	6,956.395	↓ 2.2	4,353,160	1	→ Sort (cost=325,576.45..330,485.63 rows=1,963,671 width=8) (actual time=5,453.127..6,956.395 rows=4,353,160 loops=1) Sort Key: events_persons.person_id, events_persons.organization_id Sort Method: external merge Disk: 76552kB
23.	2,391.066	3,453.098	↓ 2.2	4,353,160	1	→ Hash Join (cost=118.45..120,322.54 rows=1,963,671 width=8) (actual time=1.301..3,453.098 rows=4,353,160 loops=1) Hash Cond: (events_persons.organization_id = organizations.id)
24.	1,060.748	1,060.748	↓ 1.0	4,354,357	1	→ Seq Scan on events_persons (cost=0.00..84,238.55 rows=4,354,355 width=8) (actual time=0.008..1,060.748 rows=4,354,357 loops=1)
25.	0.391	1.284	↑ 1.0	1,329	1	→ Hash (cost=101.84..101.84 rows=1,329 width=4) (actual time=1.284..1.284 rows=1,329 loops=1) Buckets: 2048 Batches: 1 Memory Usage: 63kB
26.	0.893	0.893	↑ 1.0	1,329	1	→ Seq Scan on organizations (cost=0.00..101.84 rows=1,329 width=4) (actual time=0.008..0.893 rows=1,329 loops=1) Filter: (type = ANY ('{0,1}'::integer[])) Rows Removed by Filter: 1618
27.	956.198	7,122.049	↓ 1,560.3	4,319,021	1	→ Sort (cost=213.62..220.54 rows=2,768 width=20) (actual time=6,186.531..7,122.049 rows=4,319,021 loops=1) Sort Key: last_events_persons.person_id, last_events_persons.organization_id Sort Method: quicksort Memory: 8475kB
28.	6,165.851	6,165.851	↓ 25.0	69,146	1	→ CTE Scan on last_events_persons (cost=0.00..55.36 rows=2,768 width=20) (actual time=4,956.503..6,165.851 rows=69,146 loops=1)
29.	891.545	7,381.195	↓ 1,450.6	4,015,294	1	→ Sort (cost=213.62..220.54 rows=2,768 width=20) (actual time=6,490.135..7,381.195 rows=4,015,294 loops=1) Sort Key: next_events_persons.person_id, next_events_persons.organization_id Sort Method: quicksort Memory: 135kB
30.	6,489.650	6,489.650	↑ 2.5	1,101	1	→ CTE Scan on next_events_persons (cost=0.00..55.36 rows=2,768 width=20) (actual time=5,186.140..6,489.650 rows=1,101 loops=1)

                                                                                                      QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Unique  (cost=1047041.16..1076839.86 rows=2768 width=32) (actual time=18129.807..28814.803 rows=71587 loops=1)
   CTE hidden_external_event_ids
     ->  Nested Loop  (cost=0.43..90999.65 rows=22239 width=4) (actual time=0.034..7.404 rows=1758 loops=1)
           ->  Seq Scan on hidden_externals  (cost=0.00..40.81 rows=2481 width=8) (actual time=0.009..0.620 rows=2481 loops=1)
           ->  Index Only Scan using events_persons_org_id_person_id_calendar_id_event_id_key on events_persons events_persons_1  (cost=0.43..36.12 rows=54 width=12) (actual time=0.001..0.002 rows=1 loops=2481)
                 Index Cond: ((organization_id = hidden_externals.organization_id) AND (person_id = hidden_externals.external_id))
                 Heap Fetches: 1758
   CTE last_events_persons
     ->  Unique  (cost=305476.07..310745.74 rows=2768 width=20) (actual time=4956.499..6131.157 rows=69146 loops=1)
           ->  Sort  (cost=305476.07..308110.90 rows=1053934 width=20) (actual time=4956.496..5655.445 rows=2034352 loops=1)
                 Sort Key: events_persons_2.person_id, events_persons_2.start_time DESC
                 Sort Method: external merge  Disk: 67608kB
                 ->  Merge Anti Join  (cost=2050.96..200043.92 rows=1053934 width=20) (actual time=9.001..3216.015 rows=2034352 loops=1)
                       Merge Cond: (events_persons_2.event_id = hidden_external_event_ids.event_id)
                       ->  Index Scan using events_persons_event_id_person_id_key on events_persons events_persons_2  (cost=0.43..186585.82 rows=2107868 width=20) (actual time=0.030..2222.700 rows=2081265 loops=1)
                             Filter: (start_time <= now())
                             Rows Removed by Filter: 2273092
                       ->  Sort  (cost=2050.53..2106.12 rows=22239 width=4) (actual time=8.968..9.618 rows=1758 loops=1)
                             Sort Key: hidden_external_event_ids.event_id
                             Sort Method: quicksort  Memory: 131kB
                             ->  CTE Scan on hidden_external_event_ids  (cost=0.00..444.78 rows=22239 width=4) (actual time=0.037..8.346 rows=1758 loops=1)
   CTE next_events_persons
     ->  Unique  (cost=313675.87..319292.09 rows=2768 width=20) (actual time=5186.136..6488.908 rows=1101 loops=1)
           ->  Sort  (cost=313675.87..316483.98 rows=1123244 width=20) (actual time=5186.133..5984.245 rows=2248911 loops=1)
                 Sort Key: events_persons_3.person_id, events_persons_3.start_time
                 Sort Method: external merge  Disk: 74736kB
                 ->  Merge Anti Join  (cost=2050.96..200794.12 rows=1123244 width=20) (actual time=1.463..3349.044 rows=2248911 loops=1)
                       Merge Cond: (events_persons_3.event_id = hidden_external_event_ids_1.event_id)
                       ->  Index Scan using events_persons_event_id_person_id_key on events_persons events_persons_3  (cost=0.43..186585.82 rows=2246487 width=20) (actual time=0.495..2281.045 rows=2273092 loops=1)
                             Filter: (start_time > now())
                             Rows Removed by Filter: 2081265
                       ->  Sort  (cost=2050.53..2106.12 rows=22239 width=4) (actual time=0.962..1.616 rows=1757 loops=1)
                             Sort Key: hidden_external_event_ids_1.event_id
                             Sort Method: quicksort  Memory: 131kB
                             ->  CTE Scan on hidden_external_event_ids hidden_external_event_ids_1  (cost=0.00..444.78 rows=22239 width=4) (actual time=0.001..0.429 rows=1758 loops=1)
   ->  Merge Left Join  (cost=326003.68..350893.20 rows=1963671 width=32) (actual time=18129.806..27902.214 rows=4353160 loops=1)
         Merge Cond: ((events_persons.person_id = next_events_persons.person_id) AND (events_persons.organization_id = next_events_persons.organization_id))
         ->  Merge Left Join  (cost=325790.06..340689.41 rows=1963671 width=20) (actual time=11639.666..17389.921 rows=4353160 loops=1)
               Merge Cond: ((events_persons.person_id = last_events_persons.person_id) AND (events_persons.organization_id = last_events_persons.organization_id))
               ->  Sort  (cost=325576.45..330485.63 rows=1963671 width=8) (actual time=5453.127..6956.395 rows=4353160 loops=1)
                     Sort Key: events_persons.person_id, events_persons.organization_id
                     Sort Method: external merge  Disk: 76552kB
                     ->  Hash Join  (cost=118.45..120322.54 rows=1963671 width=8) (actual time=1.301..3453.098 rows=4353160 loops=1)
                           Hash Cond: (events_persons.organization_id = organizations.id)
                           ->  Seq Scan on events_persons  (cost=0.00..84238.55 rows=4354355 width=8) (actual time=0.008..1060.748 rows=4354357 loops=1)
                           ->  Hash  (cost=101.84..101.84 rows=1329 width=4) (actual time=1.284..1.284 rows=1329 loops=1)
                                 Buckets: 2048  Batches: 1  Memory Usage: 63kB
                                 ->  Seq Scan on organizations  (cost=0.00..101.84 rows=1329 width=4) (actual time=0.008..0.893 rows=1329 loops=1)
                                       Filter: (type = ANY ('{0,1}'::integer[]))
                                       Rows Removed by Filter: 1618
               ->  Sort  (cost=213.62..220.54 rows=2768 width=20) (actual time=6186.531..7122.049 rows=4319021 loops=1)
                     Sort Key: last_events_persons.person_id, last_events_persons.organization_id
                     Sort Method: quicksort  Memory: 8475kB
                     ->  CTE Scan on last_events_persons  (cost=0.00..55.36 rows=2768 width=20) (actual time=4956.503..6165.851 rows=69146 loops=1)
         ->  Sort  (cost=213.62..220.54 rows=2768 width=20) (actual time=6490.135..7381.195 rows=4015294 loops=1)
               Sort Key: next_events_persons.person_id, next_events_persons.organization_id
               Sort Method: quicksort  Memory: 135kB
               ->  CTE Scan on next_events_persons  (cost=0.00..55.36 rows=2768 width=20) (actual time=5186.140..6489.650 rows=1101 loops=1)
 Planning time: 0.790 ms
 Execution time: 28867.999 ms
(60 rows)

Per node type stats

node type	count	sum of times	% of query
CTE Scan	4	12,664.276 ms	44.0 %
Hash	1	0.391 ms	0.0 %
Hash Join	1	2,391.066 ms	8.3 %
Index Only Scan	1	4.962 ms	0.0 %
Index Scan	2	4,503.745 ms	15.6 %
Merge Anti Join	2	2,050.080 ms	7.1 %
Merge Left Join	2	6,442.575 ms	22.4 %
Nested Loop	1	1.822 ms	0.0 %
Seq Scan	3	1,062.261 ms	3.7 %
Sort	7	10,428.130 ms	36.2 %
Unique	3	1,892.964 ms	6.6 %

Per table stats

Table name	Scan count	Total time	% of query
scan type	count	sum of times	% of table
events_persons	4	5,569.455 ms	19.3 %
Index Only Scan	1	4.962 ms	0.1 %
Index Scan	2	4,503.745 ms	80.9 %
Seq Scan	1	1,060.748 ms	19.0 %
hidden_externals	1	0.620 ms	0.0 %
Seq Scan	1	0.620 ms	100.0 %
organizations	1	0.893 ms	0.0 %
Seq Scan	1	0.893 ms	100.0 %

explain.depesz.com

PostgreSQL's explain analyze made readable

Result: KC0W

Color mode:

Visible columns:

Per node type stats

Per table stats