explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Kmxh : updat with index services (project_id, id) WHERE type = 'PrometheusService' and COALESCE(active, false) = false;

Settings
# exclusive inclusive rows x rows loops node
1. 0.006 4,686.405 ↑ 1.0 1 1

Aggregate (cost=672.22..672.23 rows=1 width=8) (actual time=4,686.405..4,686.405 rows=1 loops=1)

  • Buffers: shared hit=11510 read=3989 dirtied=71
  • I/O Timings: read=4594.813
2.          

CTE updated_records

3. 0.000 4,686.397 ↓ 0.0 0 1

ModifyTable on public.services (cost=593.88..672.06 rows=7 width=224) (actual time=4,686.397..4,686.397 rows=0 loops=1)

  • Buffers: shared hit=11510 read=3989 dirtied=71
  • I/O Timings: read=4594.813
4.          

Initplan (for ModifyTable on public.services)

5. 0.000 4,686.390 ↓ 0.0 0 1

Nested Loop (cost=1.00..11,255.09 rows=19 width=0) (actual time=4,686.390..4,686.390 rows=0 loops=1)

  • Buffers: shared hit=11510 read=3989 dirtied=71
  • I/O Timings: read=4594.813
6. 13.397 4,686.390 ↓ 0.0 0 1

Nested Loop (cost=0.72..11,244.45 rows=27 width=4) (actual time=4,686.390..4,686.390 rows=0 loops=1)

  • Buffers: shared hit=11510 read=3989 dirtied=71
  • I/O Timings: read=4594.813
7. 4,634.787 4,634.787 ↓ 1.0 5,458 1

Index Scan using projects_pkey on public.projects (cost=0.43..9,535.31 rows=5,311 width=4) (actual time=2.889..4,634.787 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=4588.345
8. 38.206 38.206 ↓ 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.007..0.007 rows=0 loops=5,458)

  • Index Cond: (cluster_groups.group_id = projects.namespace_id)
  • Buffers: shared hit=10910 read=6
  • I/O Timings: read=6.468
9. 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
10. 4,686.394 4,686.394 ↓ 0.0 0 1

Result (cost=0.56..78.74 rows=7 width=224) (actual time=4,686.394..4,686.394 rows=0 loops=1)

  • Buffers: shared hit=11510 read=3989 dirtied=71
  • I/O Timings: read=4594.813
11. 0.000 0.000 ↓ 0.0 0 0

Index Scan using index_services_on_project_id_and_type on public.services (cost=0.56..78.74 rows=7 width=224) (actual time=0.000..0.000 rows=0 loops=0)

  • Index Cond: ((services.project_id >= 1) AND (services.project_id <= 10000) AND ((services.type)::text = 'PrometheusService'::text))
  • Filter: ((NOT services.active) AND (services.properties = '{}'::text))
  • Rows Removed by Filter: 0
12. 4,686.399 4,686.399 ↓ 0.0 0 1

CTE Scan on updated_records (cost=0.00..0.14 rows=7 width=0) (actual time=4,686.399..4,686.399 rows=0 loops=1)

  • Buffers: shared hit=11510 read=3989 dirtied=71
  • I/O Timings: read=4594.813