explain.depesz.com

PostgreSQL's explain analyze made readable

Result: KVgv : without index

Settings
# exclusive inclusive rows x rows loops node
1. 0.045 3,957.044 ↑ 1.0 1 1

Aggregate (cost=9,628.10..9,628.11 rows=1 width=8) (actual time=3,957.044..3,957.044 rows=1 loops=1)

  • Buffers: shared hit=11514 read=3997 dirtied=71
  • I/O Timings: read=3876.488
2.          

CTE created_records

3. 0.003 3,956.996 ↓ 0.0 0 1

ModifyTable on public.services (cost=1.56..9,628.08 rows=1 width=1,181) (actual time=3,956.995..3,956.996 rows=0 loops=1)

  • Buffers: shared hit=11514 read=3997 dirtied=71
  • I/O Timings: read=3876.488
4. 0.001 3,956.993 ↓ 0.0 0 1

Nested Loop (cost=1.56..9,628.08 rows=1 width=1,181) (actual time=3,956.993..3,956.993 rows=0 loops=1)

  • Buffers: shared hit=11514 read=3997 dirtied=71
  • I/O Timings: read=3876.488
5. 11.134 3,956.992 ↓ 0.0 0 1

Nested Loop (cost=1.27..9,627.66 rows=1 width=8) (actual time=3,956.992..3,956.992 rows=0 loops=1)

  • Buffers: shared hit=11514 read=3997 dirtied=71
  • I/O Timings: read=3876.488
6. 5.936 3,918.568 ↓ 5,458.0 5,458 1

Merge Left Join (cost=0.99..9,627.34 rows=1 width=8) (actual time=6.545..3,918.568 rows=5,458 loops=1)

  • Merge Cond: (projects.id = services_1.project_id)
  • Filter: (services_1.id IS NULL)
  • Rows Removed by Filter: 0
  • Buffers: shared hit=604 read=3991 dirtied=71
  • I/O Timings: read=3871.037
7. 3,909.072 3,909.072 ↓ 1.0 5,458 1

Index Scan using projects_pkey on public.projects (cost=0.43..9,535.31 rows=5,311 width=8) (actual time=2.949..3,909.072 rows=5,458 loops=1)

  • Index Cond: ((projects.id >= 1) AND (projects.id <= 10000))
  • Buffers: shared hit=600 read=3983 dirtied=71
  • I/O Timings: read=3867.684
8. 3.560 3.560 ↓ 0.0 0 1

Index Scan using index_services_on_project_id_and_type on public.services services_1 (cost=0.56..78.71 rows=12 width=8) (actual time=3.560..3.560 rows=0 loops=1)

  • Index Cond: ((services_1.project_id >= 1) AND (services_1.project_id <= 10000) AND ((services_1.type)::text = 'PrometheusService'::text))
  • Buffers: shared hit=4 read=8
  • I/O Timings: read=3.353
9. 27.290 27.290 ↓ 0.0 0 5,458

Index Scan using index_cluster_groups_on_group_id on public.cluster_groups (cost=0.28..0.31 rows=1 width=8) (actual time=0.005..0.005 rows=0 loops=5,458)

  • Index Cond: (cluster_groups.group_id = projects.namespace_id)
  • Buffers: shared hit=10910 read=6
  • I/O Timings: read=5.451
10. 0.000 0.000 ↓ 0.0 0 0

Index Scan using index_clusters_applications_prometheus_on_cluster_id on public.clusters_applications_prometheus (cost=0.29..0.38 rows=1 width=4) (actual time=0.000..0.000 rows=0 loops=0)

  • Index Cond: (clusters_applications_prometheus.cluster_id = cluster_groups.cluster_id)
  • Filter: (clusters_applications_prometheus.status = ANY ('{3,5}'::integer[]))
  • Rows Removed by Filter: 0
11. 3,956.999 3,956.999 ↓ 0.0 0 1

CTE Scan on created_records (cost=0.00..0.02 rows=1 width=0) (actual time=3,956.998..3,956.999 rows=0 loops=1)

  • Buffers: shared hit=11514 read=3997 dirtied=71
  • I/O Timings: read=3876.488