explain.depesz.com

PostgreSQL's explain analyze made readable

Result: oruo

Settings
# 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. 0.000 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. 262.107 3,079.386 ↑ 1.1 60,110 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. 356.997 1,881.945 ↑ 1.3 54,721 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: 3721kB
12. 145.671 1,524.948 ↑ 1.3 54,826 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. 165.471 1,379.184 ↑ 1.3 54,826 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. 455.843 1,208.937 ↑ 1.3 54,829 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. 95.142 95.142 ↑ 1.3 54,829 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. 657.952 657.952 ↑ 1.0 1 164,488

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. 4.644 4.776 ↑ 1.0 110 3

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
18. 0.132 0.132 ↑ 1.0 110 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.039 0.093 ↑ 1.0 21 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
20. 0.054 0.054 ↑ 1.0 21 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. 732.303 935.334 ↓ 1.0 78,606 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: 1696kB
22. 203.031 203.031 ↓ 1.0 78,610 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: 1809
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: 1024 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: 1024 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