explain.depesz.com

PostgreSQL's explain analyze made readable

Result: lUNX : create with index services (project_id, id) WHERE type = 'PrometheusService';

Settings
# exclusive inclusive rows x rows loops node
1. 0.007 20.339 ↑ 1.0 1 1

Aggregate (cost=9,553.92..9,553.93 rows=1 width=8) (actual time=20.339..20.339 rows=1 loops=1)

  • Buffers: shared hit=15497 read=2
  • I/O Timings: read=0.149
2.          

CTE created_records

3. 0.004 20.329 ↓ 0.0 0 1

ModifyTable on public.services (cost=1.29..9,553.90 rows=1 width=1,181) (actual time=20.329..20.329 rows=0 loops=1)

  • Buffers: shared hit=15497 read=2
  • I/O Timings: read=0.149
4. 0.002 20.325 ↓ 0.0 0 1

Nested Loop (cost=1.29..9,553.90 rows=1 width=1,181) (actual time=20.325..20.325 rows=0 loops=1)

  • Buffers: shared hit=15497 read=2
  • I/O Timings: read=0.149
5. 0.000 20.323 ↓ 0.0 0 1

Nested Loop (cost=1.00..9,553.48 rows=1 width=8) (actual time=20.323..20.323 rows=0 loops=1)

  • Buffers: shared hit=15497 read=2
  • I/O Timings: read=0.149
6. 1.548 9.544 ↓ 5,458.0 5,458 1

Merge Left Join (cost=0.72..9,553.16 rows=1 width=8) (actual time=0.220..9.544 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=4581 read=2
  • I/O Timings: read=0.149
7. 7.831 7.831 ↓ 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=0.052..7.831 rows=5,458 loops=1)

  • Index Cond: ((projects.id >= 1) AND (projects.id <= 10000))
  • Buffers: shared hit=4581
8. 0.165 0.165 ↓ 0.0 0 1

Index Only Scan using temp_partial_indx_project_id_id on public.services services_1 (cost=0.29..4.53 rows=12 width=8) (actual time=0.165..0.165 rows=0 loops=1)

  • Index Cond: ((services_1.project_id >= 1) AND (services_1.project_id <= 10000))
  • Heap Fetches: 0
  • Buffers: shared read=2
  • I/O Timings: read=0.149
9. 10.916 10.916 ↓ 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.002..0.002 rows=0 loops=5,458)

  • Index Cond: (cluster_groups.group_id = projects.namespace_id)
  • Buffers: shared hit=10916
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. 20.332 20.332 ↓ 0.0 0 1

CTE Scan on created_records (cost=0.00..0.02 rows=1 width=0) (actual time=20.332..20.332 rows=0 loops=1)

  • Buffers: shared hit=15497 read=2
  • I/O Timings: read=0.149