explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 0AyA

Settings
# exclusive inclusive rows x rows loops node
1. 1.031 15,005.089 ↓ 91.7 5,504 1

Group (cost=2,261.59..2,261.89 rows=60 width=4) (actual time=15,003.299..15,005.089 rows=5,504 loops=1)

  • Group Key: projects.id
  • Buffers: shared hit=33514 read=13332 dirtied=83
  • I/O Timings: read=14687.006
2. 10.025 15,004.058 ↓ 103.3 6,201 1

Sort (cost=2,261.59..2,261.74 rows=60 width=4) (actual time=15,003.297..15,004.058 rows=6,201 loops=1)

  • Sort Key: projects.id
  • Sort Method: quicksort Memory: 483kB
  • Buffers: shared hit=33514 read=13332 dirtied=83
  • I/O Timings: read=14687.006
3. 20.931 14,994.033 ↓ 103.3 6,201 1

Nested Loop Left Join (cost=191.84..2,259.82 rows=60 width=4) (actual time=109.537..14,994.033 rows=6,201 loops=1)

  • Filter: ((services.id IS NULL) OR ((NOT services.active) AND (services.properties = '{}'::text)))
  • Rows Removed by Filter: 665
  • Buffers: shared hit=33514 read=13332 dirtied=83
  • I/O Timings: read=14687.006
4. 5.266 8,889.826 ↓ 75.5 6,866 1

Nested Loop (cost=191.41..2,214.02 rows=91 width=4) (actual time=100.600..8,889.826 rows=6,866 loops=1)

  • Buffers: shared hit=13152 read=7743 dirtied=61
  • I/O Timings: read=8742.307
5. 1.371 1,324.515 ↓ 13.2 645 1

Nested Loop (cost=190.98..2,163.45 rows=49 width=8) (actual time=95.966..1,324.515 rows=645 loops=1)

  • Buffers: shared hit=10845 read=1223 dirtied=52
  • I/O Timings: read=1257.844
6. 1.745 440.784 ↓ 13.2 645 1

Nested Loop (cost=190.55..2,044.68 rows=49 width=4) (actual time=92.325..440.784 rows=645 loops=1)

  • Buffers: shared hit=9163 read=481 dirtied=34
  • I/O Timings: read=389.144
7. 3.033 141.049 ↑ 3.2 645 1

Merge Join (cost=190.26..667.56 rows=2,064 width=12) (actual time=89.079..141.049 rows=645 loops=1)

  • Buffers: shared hit=7553 read=152 dirtied=33
  • I/O Timings: read=99.847
8. 21.601 21.601 ↓ 1.0 2,988 1

Index Only Scan using index_cluster_groups_on_cluster_id_and_group_id on public.cluster_groups (cost=0.28..108.50 rows=2,978 width=8) (actual time=0.183..21.601 rows=2,988 loops=1)

  • Heap Fetches: 2474
  • Buffers: shared hit=968 read=21 dirtied=15
  • I/O Timings: read=7.870
9. 116.415 116.415 ↓ 1.0 5,249 1

Index Scan using index_clusters_applications_prometheus_on_cluster_id on public.clusters_applications_prometheus (cost=0.28..518.37 rows=5,245 width=4) (actual time=1.989..116.415 rows=5,249 loops=1)

  • Filter: (clusters_applications_prometheus.status = ANY ('{3,5}'::integer[]))
  • Rows Removed by Filter: 2325
  • Buffers: shared hit=6585 read=131 dirtied=18
  • I/O Timings: read=91.977
10. 297.990 297.990 ↑ 1.0 1 645

Index Scan using clusters_pkey on public.clusters (cost=0.29..0.65 rows=1 width=4) (actual time=0.459..0.462 rows=1 loops=645)

  • Index Cond: (clusters.id = clusters_applications_prometheus.cluster_id)
  • Filter: (clusters.cluster_type = 2)
  • Rows Removed by Filter: 0
  • Buffers: shared hit=1610 read=329 dirtied=1
  • I/O Timings: read=289.297
11. 882.360 882.360 ↑ 1.0 1 645

Index Only Scan using namespaces_pkey on public.namespaces (cost=0.43..2.41 rows=1 width=4) (actual time=1.350..1.368 rows=1 loops=645)

  • Index Cond: (namespaces.id = cluster_groups.group_id)
  • Heap Fetches: 150
  • Buffers: shared hit=1682 read=742 dirtied=18
  • I/O Timings: read=868.700
12. 7,560.045 7,560.045 ↑ 1.5 11 645

Index Scan using index_projects_on_namespace_id on public.projects (cost=0.43..0.87 rows=16 width=8) (actual time=2.061..11.721 rows=11 loops=645)

  • Index Cond: (projects.namespace_id = namespaces.id)
  • Buffers: shared hit=2307 read=6520 dirtied=9
  • I/O Timings: read=7484.463
13. 6,083.276 6,083.276 ↓ 0.0 0 6,866

Index Scan using index_services_on_project_id on public.services (cost=0.43..0.49 rows=1 width=45) (actual time=0.850..0.886 rows=0 loops=6,866)

  • Index Cond: (services.project_id = projects.id)
  • Filter: ((services.type)::text = 'PrometheusService'::text)
  • Rows Removed by Filter: 1
  • Buffers: shared hit=20362 read=5589 dirtied=22
  • I/O Timings: read=5944.699