#	exclusive	inclusive	rows x	rows	loops	node
1.	0.011	4,702.620	↑ 1.0	20	1	→ Limit (cost=358,845.43..358,845.48 rows=20 width=16) (actual time=4,702.607..4,702.620 rows=20 loops=1) Buffers: shared hit=163370, temp read=6578 written=11630
2.	0.117	4,702.609	↑ 10.9	20	1	→ Sort (cost=358,845.43..358,845.98 rows=218 width=16) (actual time=4,702.605..4,702.609 rows=20 loops=1) Sort Key: (max(form_responses.created_time)) Sort Method: top-N heapsort Memory: 28kB Buffers: shared hit=163370, temp read=6578 written=11630
3.	1,098.463	4,702.492	↑ 1.8	122	1	→ GroupAggregate (cost=314,433.48..358,839.63 rows=218 width=16) (actual time=1,533.753..4,702.492 rows=122 loops=1) Group Key: form_responses.form_id Buffers: shared hit=163370, temp read=6578 written=11630
4.	717.920	3,602.931	↓ 1.0	1,599,469	1	→ Merge Join (cost=314,433.48..341,824.39 rows=1,544,575 width=16) (actual time=1,525.738..3,602.931 rows=1,599,469 loops=1) Merge Cond: (forms.id = form_responses.form_id) Buffers: shared hit=162266, temp read=5084 written=10136
5.	1.465	1.465	↑ 1.0	5,492	1	→ Index Only Scan using forms_pkey on forms (cost=0.28..355.43 rows=5,635 width=4) (actual time=0.009..1.465 rows=5,492 loops=1) Heap Fetches: 43 Buffers: shared hit=52
6.	746.885	2,883.546	↓ 1.0	1,599,469	1	→ Materialize (cost=314,432.02..322,154.89 rows=1,544,575 width=16) (actual time=1,525.488..2,883.546 rows=1,599,469 loops=1) Buffers: shared hit=162214, temp read=5084 written=10136
7.	1,382.879	2,136.661	↓ 1.0	1,599,469	1	→ Sort (cost=314,432.02..318,293.45 rows=1,544,575 width=16) (actual time=1,525.485..2,136.661 rows=1,599,469 loops=1) Sort Key: form_responses.form_id Sort Method: external merge Disk: 40632kB Buffers: shared hit=162214, temp read=5084 written=5084
8.	753.782	753.782	↓ 1.0	1,599,469	1	→ Index Only Scan using form_responses_form_id_mailing_id_covering_index on form_responses (cost=0.56..129,262.14 rows=1,544,575 width=16) (actual time=0.020..753.782 rows=1,599,469 loops=1) Index Cond: (project_id = 20006) Filter: ((deleted IS NOT TRUE) AND (is_unanswered IS NOT TRUE)) Rows Removed by Filter: 26819 Heap Fetches: 271 Buffers: shared hit=162214
9.						SubPlan (forGroupAggregate)
10.	0.610	0.610	↑ 1.0	1	122	→ Index Scan using forms_pkey on forms forms_1 (cost=0.28..8.30 rows=1 width=26) (actual time=0.005..0.005 rows=1 loops=122) Index Cond: (id = form_responses.form_id) Buffers: shared hit=368
11.	0.244	0.244	↑ 1.0	1	122	→ Index Scan using forms_pkey on forms forms_2 (cost=0.28..8.30 rows=1 width=4) (actual time=0.001..0.002 rows=1 loops=122) Index Cond: (id = form_responses.form_id) Buffers: shared hit=368
12.	0.244	0.244	↑ 1.0	1	122	→ Index Scan using forms_pkey on forms forms_3 (cost=0.28..8.30 rows=1 width=18) (actual time=0.001..0.002 rows=1 loops=122) Index Cond: (id = form_responses.form_id) Buffers: shared hit=368

Planning time	:	0.489 ms

'Limit  (cost=358845.43..358845.48 rows=20 width=16) (actual time=4702.607..4702.620 rows=20 loops=1)'
'  Buffers: shared hit=163370, temp read=6578 written=11630'
'  ->  Sort  (cost=358845.43..358845.98 rows=218 width=16) (actual time=4702.605..4702.609 rows=20 loops=1)'
'        Sort Key: (max(form_responses.created_time))'
'        Sort Method: top-N heapsort  Memory: 28kB'
'        Buffers: shared hit=163370, temp read=6578 written=11630'
'        ->  GroupAggregate  (cost=314433.48..358839.63 rows=218 width=16) (actual time=1533.753..4702.492 rows=122 loops=1)'
'              Group Key: form_responses.form_id'
'              Buffers: shared hit=163370, temp read=6578 written=11630'
'              ->  Merge Join  (cost=314433.48..341824.39 rows=1544575 width=16) (actual time=1525.738..3602.931 rows=1599469 loops=1)'
'                    Merge Cond: (forms.id = form_responses.form_id)'
'                    Buffers: shared hit=162266, temp read=5084 written=10136'
'                    ->  Index Only Scan using forms_pkey on forms  (cost=0.28..355.43 rows=5635 width=4) (actual time=0.009..1.465 rows=5492 loops=1)'
'                          Heap Fetches: 43'
'                          Buffers: shared hit=52'
'                    ->  Materialize  (cost=314432.02..322154.89 rows=1544575 width=16) (actual time=1525.488..2883.546 rows=1599469 loops=1)'
'                          Buffers: shared hit=162214, temp read=5084 written=10136'
'                          ->  Sort  (cost=314432.02..318293.45 rows=1544575 width=16) (actual time=1525.485..2136.661 rows=1599469 loops=1)'
'                                Sort Key: form_responses.form_id'
'                                Sort Method: external merge  Disk: 40632kB'
'                                Buffers: shared hit=162214, temp read=5084 written=5084'
'                                ->  Index Only Scan using form_responses_form_id_mailing_id_covering_index on form_responses  (cost=0.56..129262.14 rows=1544575 width=16) (actual time=0.020..753.782 rows=1599469 loops=1)'
'                                      Index Cond: (project_id = 20006)'
'                                      Filter: ((deleted IS NOT TRUE) AND (is_unanswered IS NOT TRUE))'
'                                      Rows Removed by Filter: 26819'
'                                      Heap Fetches: 271'
'                                      Buffers: shared hit=162214'
'              SubPlan 1'
'                ->  Index Scan using forms_pkey on forms forms_1  (cost=0.28..8.30 rows=1 width=26) (actual time=0.005..0.005 rows=1 loops=122)'
'                      Index Cond: (id = form_responses.form_id)'
'                      Buffers: shared hit=368'
'              SubPlan 2'
'                ->  Index Scan using forms_pkey on forms forms_2  (cost=0.28..8.30 rows=1 width=4) (actual time=0.001..0.002 rows=1 loops=122)'
'                      Index Cond: (id = form_responses.form_id)'
'                      Buffers: shared hit=368'
'              SubPlan 3'
'                ->  Index Scan using forms_pkey on forms forms_3  (cost=0.28..8.30 rows=1 width=18) (actual time=0.001..0.002 rows=1 loops=122)'
'                      Index Cond: (id = form_responses.form_id)'
'                      Buffers: shared hit=368'
'Planning time: 0.489 ms'
'Execution time: 4718.112 ms'

Per node type stats

node type	count	sum of times	% of query
GroupAggregate	1	1,098.463 ms	23.4 %
Index Only Scan	2	755.247 ms	16.1 %
Index Scan	3	1.098 ms	0.0 %
Limit	1	0.011 ms	0.0 %
Materialize	1	746.885 ms	15.9 %
Merge Join	1	717.920 ms	15.3 %
Sort	2	1,382.996 ms	29.4 %

Per table stats

Table name	Scan count	Total time	% of query
scan type	count	sum of times	% of table
form_responses	1	753.782 ms	16.0 %
Index Only Scan	1	753.782 ms	100.0 %
forms	4	2.563 ms	0.1 %
Index Only Scan	1	1.465 ms	57.2 %
Index Scan	3	1.098 ms	42.8 %

explain.depesz.com

PostgreSQL's explain analyze made readable

Result: fphc : b.c groupby forms, sort last received DESC

Color mode:

Visible columns:

Per node type stats

Per table stats