#	exclusive	inclusive	rows x	rows	loops	node
1.	0.003	1,455.088	↑ 27.0	1	1	→ Subquery Scan on graphql_guidebooks (cost=217,984.01..217,984.55 rows=27 width=30) (actual time=1,455.086..1,455.088 rows=1 loops=1)
2.	0.002	1,455.085	↑ 27.0	1	1	→ Unique (cost=217,984.01..217,984.28 rows=27 width=290) (actual time=1,455.084..1,455.085 rows=1 loops=1)
3.						CTE types
4.	2.954	1,454.491	↓ 1.0	517	1	→ GroupAggregate (cost=217,896.98..217,931.35 rows=507 width=36) (actual time=1,450.472..1,454.491 rows=517 loops=1) Group Key: guidebooks_1.id
5.	4.321	1,451.537	↑ 1.0	3,724	1	→ Sort (cost=217,896.98..217,906.33 rows=3,737 width=15) (actual time=1,450.435..1,451.537 rows=3,724 loops=1) Sort Key: guidebooks_1.id Sort Method: quicksort Memory: 281kB
6.	4.971	1,447.216	↑ 1.0	3,724	1	→ Hash Join (cost=194,840.59..217,675.24 rows=3,737 width=15) (actual time=900.703..1,447.216 rows=3,724 loops=1) Hash Cond: (adventures_guidebooks_1.guidebook_id = guidebooks_1.id)
7.	58.174	1,441.859	↓ 1.0	3,757	1	→ Merge Join (cost=194,770.18..217,594.94 rows=3,737 width=15) (actual time=900.304..1,441.859 rows=3,757 loops=1) Merge Cond: (adventures.id = adventures_guidebooks_1.adventure_id)
8.	97.493	1,381.256	↑ 1.0	163,304	1	→ Unique (cost=194,769.90..215,293.34 rows=165,017 width=943) (actual time=900.078..1,381.256 rows=163,304 loops=1)
9.	257.301	1,283.763	↓ 1.1	179,471	1	→ Gather Merge (cost=194,769.90..214,880.79 rows=165,017 width=943) (actual time=900.075..1,283.763 rows=179,471 loops=1) Workers Planned: 2 Workers Launched: 2
10.	87.369	1,026.462	↑ 1.1	60,110	3 / 3	→ Merge Left Join (cost=193,769.87..194,833.71 rows=68,757 width=15) (actual time=873.063..1,026.462 rows=60,110 loops=3) Merge Cond: (adventures.id = adventures_regions.adventure_id)
11.	118.999	627.315	↑ 1.3	54,721	3 / 3	→ Sort (cost=186,225.64..186,397.53 rows=68,757 width=15) (actual time=599.206..627.315 rows=54,721 loops=3) Sort Key: adventures.id Sort Method: quicksort Memory: 3,721kB
12.	48.557	508.316	↑ 1.3	54,826	3 / 3	→ Hash Join (cost=6.37..180,701.28 rows=68,757 width=15) (actual time=1.713..508.316 rows=54,826 loops=3) Hash Cond: (adventures.adventure_type_id = adventure_types.id)
13.	55.157	459.728	↑ 1.3	54,826	3 / 3	→ Hash Left Join (cost=4.89..180,482.90 rows=68,757 width=8) (actual time=1.637..459.728 rows=54,826 loops=3) Hash Cond: (adventures.geo_point_id = geo_points.id) Filter: ((geo_lines.line IS NOT NULL) OR (geo_points.point IS NOT NULL)) Rows Removed by Filter: 4
14.	151.948	402.979	↑ 1.3	54,829	3 / 3	→ Nested Loop Left Join (cost=0.42..180,297.94 rows=68,757 width=18,235) (actual time=0.019..402.979 rows=54,829 loops=3)
15.	31.714	31.714	↑ 1.3	54,829	3 / 3	→ Parallel Seq Scan on adventures (cost=0.00..24,533.57 rows=68,757 width=20) (actual time=0.003..31.714 rows=54,829 loops=3)
16.	219.317	219.317	↑ 1.0	1	164,488 / 3	→ Index Scan using geo_lines_pkey on geo_lines (cost=0.42..2.27 rows=1 width=18,223) (actual time=0.004..0.004 rows=1 loops=164,488) Index Cond: (adventures.geo_line_id = id)
17.	1.548	1.592	↑ 1.0	110	3 / 3	→ Hash (cost=3.10..3.10 rows=110 width=48) (actual time=1.592..1.592 rows=110 loops=3) Buckets: 1,024 Batches: 1 Memory Usage: 17kB
18.	0.044	0.044	↑ 1.0	110	3 / 3	→ Seq Scan on geo_points (cost=0.00..3.10 rows=110 width=48) (actual time=0.008..0.044 rows=110 loops=3)
19.	0.013	0.031	↑ 1.0	21	3 / 3	→ Hash (cost=1.21..1.21 rows=21 width=15) (actual time=0.031..0.031 rows=21 loops=3) Buckets: 1,024 Batches: 1 Memory Usage: 10kB
20.	0.018	0.018	↑ 1.0	21	3 / 3	→ Seq Scan on adventure_types (cost=0.00..1.21 rows=21 width=15) (actual time=0.011..0.018 rows=21 loops=3)
21.	244.101	311.778	↓ 1.0	78,606	3 / 3	→ Sort (cost=7,544.23..7,740.61 rows=78,551 width=8) (actual time=273.846..311.778 rows=78,606 loops=3) Sort Key: adventures_regions.adventure_id Sort Method: external sort Disk: 1,696kB
22.	67.677	67.677	↓ 1.0	78,610	3 / 3	→ Seq Scan on adventures_regions (cost=0.00..1,157.51 rows=78,551 width=8) (actual time=0.029..67.677 rows=78,610 loops=3)
23.	2.429	2.429	↓ 1.0	3,808	1	→ Index Only Scan using index_adventures_guidebooks_on_adventure_id_and_guidebook_id on adventures_guidebooks adventures_guidebooks_1 (cost=0.28..192.18 rows=3,737 width=8) (actual time=0.022..2.429 rows=3,808 loops=1) Heap Fetches: 1,809
24.	0.160	0.386	↓ 1.1	533	1	→ Hash (cost=64.07..64.07 rows=507 width=4) (actual time=0.385..0.386 rows=533 loops=1) Buckets: 1,024 Batches: 1 Memory Usage: 27kB
25.	0.226	0.226	↓ 1.1	533	1	→ Seq Scan on guidebooks guidebooks_1 (cost=0.00..64.07 rows=507 width=4) (actual time=0.003..0.226 rows=533 loops=1)
26.	0.010	1,455.083	↑ 27.0	1	1	→ HashAggregate (cost=52.66..52.93 rows=27 width=290) (actual time=1,455.083..1,455.083 rows=1 loops=1) Group Key: guidebooks.id, types.names, admin_users.first_name, admin_users.last_name
27.	0.004	1,455.073	↑ 27.0	1	1	→ Nested Loop Left Join (cost=8.58..52.39 rows=27 width=79) (actual time=1,455.066..1,455.073 rows=1 loops=1) Join Filter: (adventures_guidebooks.guidebook_id = guidebooks.id)
28.	0.003	1,455.057	↑ 7.0	1	1	→ Nested Loop Left Join (cost=8.30..37.71 rows=7 width=79) (actual time=1,455.053..1,455.057 rows=1 loops=1) Join Filter: (types.guidebook_id = guidebooks.id)
29.	0.168	0.325	↑ 2.0	1	1	→ Hash Right Join (cost=8.30..26.20 rows=2 width=47) (actual time=0.322..0.325 rows=1 loops=1) Hash Cond: ((admin_users.id)::text = guidebooks.owner_id)
30.	0.133	0.133	↓ 1.0	460	1	→ Seq Scan on admin_users (cost=0.00..16.56 rows=456 width=17) (actual time=0.007..0.133 rows=460 loops=1)
31.	0.006	0.024	↑ 1.0	1	1	→ Hash (cost=8.29..8.29 rows=1 width=40) (actual time=0.024..0.024 rows=1 loops=1) Buckets: 1,024 Batches: 1 Memory Usage: 9kB
32.	0.018	0.018	↑ 1.0	1	1	→ Index Scan using guidebooks_pkey on guidebooks (cost=0.27..8.29 rows=1 width=40) (actual time=0.017..0.018 rows=1 loops=1) Index Cond: (id = 596)
33.	1,454.729	1,454.729	↑ 3.0	1	1	→ CTE Scan on types (cost=0.00..11.41 rows=3 width=36) (actual time=1,454.728..1,454.729 rows=1 loops=1) Filter: (guidebook_id = 596) Rows Removed by Filter: 516
34.	0.002	0.012	↑ 4.0	1	1	→ Materialize (cost=0.28..14.27 rows=4 width=8) (actual time=0.011..0.012 rows=1 loops=1)
35.	0.010	0.010	↑ 4.0	1	1	→ Index Scan using index_adventures_guidebooks_on_guidebook_id_and_position on adventures_guidebooks (cost=0.28..14.25 rows=4 width=8) (actual time=0.009..0.010 rows=1 loops=1) Index Cond: (guidebook_id = 596)

Planning time	:	2.521 ms
Execution time	:	1,531.529 ms

                                                                                                                          QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Subquery Scan on graphql_guidebooks  (cost=217984.01..217984.55 rows=27 width=30) (actual time=1455.086..1455.088 rows=1 loops=1)
   ->  Unique  (cost=217984.01..217984.28 rows=27 width=290) (actual time=1455.084..1455.085 rows=1 loops=1)
         CTE types
           ->  GroupAggregate  (cost=217896.98..217931.35 rows=507 width=36) (actual time=1450.472..1454.491 rows=517 loops=1)
                 Group Key: guidebooks_1.id
                 ->  Sort  (cost=217896.98..217906.33 rows=3737 width=15) (actual time=1450.435..1451.537 rows=3724 loops=1)
                       Sort Key: guidebooks_1.id
                       Sort Method: quicksort  Memory: 281kB
                       ->  Hash Join  (cost=194840.59..217675.24 rows=3737 width=15) (actual time=900.703..1447.216 rows=3724 loops=1)
                             Hash Cond: (adventures_guidebooks_1.guidebook_id = guidebooks_1.id)
                             ->  Merge Join  (cost=194770.18..217594.94 rows=3737 width=15) (actual time=900.304..1441.859 rows=3757 loops=1)
                                   Merge Cond: (adventures.id = adventures_guidebooks_1.adventure_id)
                                   ->  Unique  (cost=194769.90..215293.34 rows=165017 width=943) (actual time=900.078..1381.256 rows=163304 loops=1)
                                         ->  Gather Merge  (cost=194769.90..214880.79 rows=165017 width=943) (actual time=900.075..1283.763 rows=179471 loops=1)
                                               Workers Planned: 2
                                               Workers Launched: 2
                                               ->  Merge Left Join  (cost=193769.87..194833.71 rows=68757 width=15) (actual time=873.063..1026.462 rows=60110 loops=3)
                                                     Merge Cond: (adventures.id = adventures_regions.adventure_id)
                                                     ->  Sort  (cost=186225.64..186397.53 rows=68757 width=15) (actual time=599.206..627.315 rows=54721 loops=3)
                                                           Sort Key: adventures.id
                                                           Sort Method: quicksort  Memory: 3721kB
                                                           ->  Hash Join  (cost=6.37..180701.28 rows=68757 width=15) (actual time=1.713..508.316 rows=54826 loops=3)
                                                                 Hash Cond: (adventures.adventure_type_id = adventure_types.id)
                                                                 ->  Hash Left Join  (cost=4.89..180482.90 rows=68757 width=8) (actual time=1.637..459.728 rows=54826 loops=3)
                                                                       Hash Cond: (adventures.geo_point_id = geo_points.id)
                                                                       Filter: ((geo_lines.line IS NOT NULL) OR (geo_points.point IS NOT NULL))
                                                                       Rows Removed by Filter: 4
                                                                       ->  Nested Loop Left Join  (cost=0.42..180297.94 rows=68757 width=18235) (actual time=0.019..402.979 rows=54829 loops=3)
                                                                             ->  Parallel Seq Scan on adventures  (cost=0.00..24533.57 rows=68757 width=20) (actual time=0.003..31.714 rows=54829 loops=3)
                                                                             ->  Index Scan using geo_lines_pkey on geo_lines  (cost=0.42..2.27 rows=1 width=18223) (actual time=0.004..0.004 rows=1 loops=164488)
                                                                                   Index Cond: (adventures.geo_line_id = id)
                                                                       ->  Hash  (cost=3.10..3.10 rows=110 width=48) (actual time=1.592..1.592 rows=110 loops=3)
                                                                             Buckets: 1024  Batches: 1  Memory Usage: 17kB
                                                                             ->  Seq Scan on geo_points  (cost=0.00..3.10 rows=110 width=48) (actual time=0.008..0.044 rows=110 loops=3)
                                                                 ->  Hash  (cost=1.21..1.21 rows=21 width=15) (actual time=0.031..0.031 rows=21 loops=3)
                                                                       Buckets: 1024  Batches: 1  Memory Usage: 10kB
                                                                       ->  Seq Scan on adventure_types  (cost=0.00..1.21 rows=21 width=15) (actual time=0.011..0.018 rows=21 loops=3)
                                                     ->  Sort  (cost=7544.23..7740.61 rows=78551 width=8) (actual time=273.846..311.778 rows=78606 loops=3)
                                                           Sort Key: adventures_regions.adventure_id
                                                           Sort Method: external sort  Disk: 1696kB
                                                           ->  Seq Scan on adventures_regions  (cost=0.00..1157.51 rows=78551 width=8) (actual time=0.029..67.677 rows=78610 loops=3)
                                   ->  Index Only Scan using index_adventures_guidebooks_on_adventure_id_and_guidebook_id on adventures_guidebooks adventures_guidebooks_1  (cost=0.28..192.18 rows=3737 width=8) (actual time=0.022..2.429 rows=3808 loops=1)
                                         Heap Fetches: 1809
                             ->  Hash  (cost=64.07..64.07 rows=507 width=4) (actual time=0.385..0.386 rows=533 loops=1)
                                   Buckets: 1024  Batches: 1  Memory Usage: 27kB
                                   ->  Seq Scan on guidebooks guidebooks_1  (cost=0.00..64.07 rows=507 width=4) (actual time=0.003..0.226 rows=533 loops=1)
         ->  HashAggregate  (cost=52.66..52.93 rows=27 width=290) (actual time=1455.083..1455.083 rows=1 loops=1)
               Group Key: guidebooks.id, types.names, admin_users.first_name, admin_users.last_name
               ->  Nested Loop Left Join  (cost=8.58..52.39 rows=27 width=79) (actual time=1455.066..1455.073 rows=1 loops=1)
                     Join Filter: (adventures_guidebooks.guidebook_id = guidebooks.id)
                     ->  Nested Loop Left Join  (cost=8.30..37.71 rows=7 width=79) (actual time=1455.053..1455.057 rows=1 loops=1)
                           Join Filter: (types.guidebook_id = guidebooks.id)
                           ->  Hash Right Join  (cost=8.30..26.20 rows=2 width=47) (actual time=0.322..0.325 rows=1 loops=1)
                                 Hash Cond: ((admin_users.id)::text = guidebooks.owner_id)
                                 ->  Seq Scan on admin_users  (cost=0.00..16.56 rows=456 width=17) (actual time=0.007..0.133 rows=460 loops=1)
                                 ->  Hash  (cost=8.29..8.29 rows=1 width=40) (actual time=0.024..0.024 rows=1 loops=1)
                                       Buckets: 1024  Batches: 1  Memory Usage: 9kB
                                       ->  Index Scan using guidebooks_pkey on guidebooks  (cost=0.27..8.29 rows=1 width=40) (actual time=0.017..0.018 rows=1 loops=1)
                                             Index Cond: (id = 596)
                           ->  CTE Scan on types  (cost=0.00..11.41 rows=3 width=36) (actual time=1454.728..1454.729 rows=1 loops=1)
                                 Filter: (guidebook_id = 596)
                                 Rows Removed by Filter: 516
                     ->  Materialize  (cost=0.28..14.27 rows=4 width=8) (actual time=0.011..0.012 rows=1 loops=1)
                           ->  Index Scan using index_adventures_guidebooks_on_guidebook_id_and_position on adventures_guidebooks  (cost=0.28..14.25 rows=4 width=8) (actual time=0.009..0.010 rows=1 loops=1)
                                 Index Cond: (guidebook_id = 596)
 Planning time: 2.521 ms
 Execution time: 1531.529 ms

Per node type stats

node type	count	sum of times	% of query
CTE Scan	1	1,454.729 ms	100.0 %
Gather Merge	1	257.301 ms	17.7 %
GroupAggregate	1	2.954 ms	0.2 %
Hash	4	1.727 ms	0.1 %
Hash Join	2	53.528 ms	3.7 %
Hash Left Join	1	55.157 ms	3.8 %
Hash Right Join	1	0.168 ms	0.0 %
HashAggregate	1	0.010 ms	0.0 %
Index Only Scan	1	2.429 ms	0.2 %
Index Scan	3	219.345 ms	15.1 %
Materialize	1	0.002 ms	0.0 %
Merge Join	1	58.174 ms	4.0 %
Merge Left Join	1	87.369 ms	6.0 %
Nested Loop Left Join	3	151.955 ms	10.4 %
Parallel Seq Scan	1	31.714 ms	2.2 %
Seq Scan	5	68.098 ms	4.7 %
Sort	3	367.421 ms	25.3 %
Subquery Scan	1	0.003 ms	0.0 %
Unique	2	97.495 ms	6.7 %

Per table stats

Table name	Scan count	Total time	% of query
scan type	count	sum of times	% of table
admin_users	1	0.133 ms	0.0 %
Seq Scan	1	0.133 ms	100.0 %
adventure_types	1	0.018 ms	0.0 %
Seq Scan	1	0.018 ms	100.0 %
adventures	1	31.714 ms	2.2 %
Parallel Seq Scan	1	31.714 ms	100.0 %
adventures_guidebooks	2	2.439 ms	0.2 %
Index Only Scan	1	2.429 ms	99.6 %
Index Scan	1	0.010 ms	0.4 %
adventures_regions	1	67.677 ms	4.7 %
Seq Scan	1	67.677 ms	100.0 %
geo_lines	1	219.317 ms	15.1 %
Index Scan	1	219.317 ms	100.0 %
geo_points	1	0.044 ms	0.0 %
Seq Scan	1	0.044 ms	100.0 %
guidebooks	2	0.244 ms	0.0 %
Index Scan	1	0.018 ms	7.4 %
Seq Scan	1	0.226 ms	92.6 %

explain.depesz.com

PostgreSQL's explain analyze made readable

Result: oruo

Color mode:

Visible columns:

Per node type stats

Per table stats