explain.depesz.com

PostgreSQL's explain analyze made readable

Result: KUTg

Settings
# exclusive inclusive rows x rows loops node
1. 2.744 44,203.606 ↓ 2,175.0 2,175 1

Nested Loop (cost=567.06..1,057.51 rows=1 width=584) (actual time=17.016..44,203.606 rows=2,175 loops=1)

2. 1.717 44,189.987 ↓ 2,175.0 2,175 1

Nested Loop (cost=566.63..1,049.06 rows=1 width=588) (actual time=16.984..44,189.987 rows=2,175 loops=1)

  • Join Filter: ((skymap.skymap)::text = (dataset.skymap)::text)
3. 0.030 0.030 ↑ 1.0 1 1

Index Scan using dataset_idx05 on dataset (cost=0.42..8.28 rows=1 width=17) (actual time=0.029..0.030 rows=1 loops=1)

  • Index Cond: ((dataset_type_name)::text = 'deepCoadd_skyMap'::text)
4. 14.197 44,188.240 ↓ 2,175.0 2,175 1

Nested Loop (cost=566.21..1,040.76 rows=1 width=623) (actual time=16.953..44,188.240 rows=2,175 loops=1)

  • Join Filter: (((instrument.instrument)::text = (visit_detector_patch_join.instrument)::text) AND ((skymap.skymap)::text = (visit_detector_patch_join.skymap)::text) AND (visit.visit = visit_detector_patch_join.visit) AND (detector.detector = visit_detector_patch_join.detector))
5. 284.341 1,275.707 ↓ 42,016.0 42,016 1

Nested Loop (cost=565.78..835.92 rows=1 width=680) (actual time=0.809..1,275.707 rows=42,016 loops=1)

  • Join Filter: (((instrument.instrument)::text = (visit_detector_region.instrument)::text) AND (detector.detector = visit_detector_region.detector))
  • Rows Removed by Join Filter: 2,499,952
6. 20.438 357.894 ↓ 45,248.0 45,248 1

Hash Join (cost=565.49..821.60 rows=1 width=532) (actual time=0.788..357.894 rows=45,248 loops=1)

  • Hash Cond: ((skymap.skymap)::text = (tract.skymap)::text)
7. 7.226 336.959 ↓ 3.6 45,248 1

Nested Loop (cost=26.68..235.42 rows=12,500 width=362) (actual time=0.281..336.959 rows=45,248 loops=1)

8. 0.004 0.004 ↑ 250.0 1 1

Seq Scan on skymap (cost=0.00..12.50 rows=250 width=146) (actual time=0.003..0.004 rows=1 loops=1)

9. 23.281 329.729 ↓ 905.0 45,248 1

Materialize (cost=26.68..66.79 rows=50 width=216) (actual time=0.277..329.729 rows=45,248 loops=1)

10. 96.640 306.448 ↓ 905.0 45,248 1

Nested Loop (cost=26.68..66.54 rows=50 width=216) (actual time=0.274..306.448 rows=45,248 loops=1)

  • Join Filter: ((physical_filter.physical_filter)::text = (visit.physical_filter)::text)
  • Rows Removed by Join Filter: 1,040,704
11. 1.573 2.832 ↓ 896.0 2,688 1

Hash Join (cost=26.54..29.11 rows=3 width=208) (actual time=0.083..2.832 rows=2,688 loops=1)

  • Hash Cond: ((physical_filter.abstract_filter)::text = (physical_filter_1.abstract_filter)::text)
12. 1.068 1.234 ↓ 896.0 2,688 1

Hash Join (cost=24.45..27.02 rows=3 width=208) (actual time=0.046..1.234 rows=2,688 loops=1)

  • Hash Cond: ((detector.instrument)::text = (instrument.instrument)::text)
13. 0.133 0.133 ↑ 1.0 112 1

Seq Scan on detector (cost=0.00..2.12 rows=112 width=8) (actual time=0.003..0.133 rows=112 loops=1)

14. 0.007 0.033 ↑ 1.0 24 1

Hash (cost=24.15..24.15 rows=24 width=200) (actual time=0.033..0.033 rows=24 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 10kB
15. 0.013 0.026 ↑ 1.0 24 1

Hash Join (cost=1.54..24.15 rows=24 width=200) (actual time=0.022..0.026 rows=24 loops=1)

  • Hash Cond: ((instrument.instrument)::text = (physical_filter.instrument)::text)
16. 0.002 0.002 ↑ 900.0 1 1

Seq Scan on instrument (cost=0.00..19.00 rows=900 width=50) (actual time=0.002..0.002 rows=1 loops=1)

17. 0.007 0.011 ↑ 1.0 24 1

Hash (cost=1.24..1.24 rows=24 width=150) (actual time=0.011..0.011 rows=24 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 10kB
18. 0.004 0.004 ↑ 1.0 24 1

Seq Scan on physical_filter (cost=0.00..1.24 rows=24 width=150) (actual time=0.001..0.004 rows=24 loops=1)

19. 0.011 0.025 ↑ 1.1 22 1

Hash (cost=1.78..1.78 rows=24 width=50) (actual time=0.025..0.025 rows=22 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
20. 0.010 0.014 ↑ 1.1 22 1

HashAggregate (cost=1.30..1.54 rows=24 width=50) (actual time=0.012..0.014 rows=22 loops=1)

  • Group Key: physical_filter_1.abstract_filter
21. 0.004 0.004 ↑ 1.0 24 1

Seq Scan on physical_filter physical_filter_1 (cost=0.00..1.24 rows=24 width=50) (actual time=0.003..0.004 rows=24 loops=1)

22. 206.976 206.976 ↑ 1.0 404 2,688

Index Scan using visit_pkey on visit (cost=0.15..7.43 rows=404 width=14) (actual time=0.011..0.077 rows=404 loops=2,688)

  • Index Cond: ((instrument)::text = (instrument.instrument)::text)
23. 0.005 0.497 ↑ 1.0 1 1

Hash (cost=538.79..538.79 rows=1 width=170) (actual time=0.497..0.497 rows=1 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
24. 0.003 0.492 ↑ 1.0 1 1

Nested Loop (cost=0.71..538.79 rows=1 width=170) (actual time=0.261..0.492 rows=1 loops=1)

25. 0.478 0.478 ↑ 1.0 1 1

Index Only Scan using tract_pkey on tract (cost=0.29..530.33 rows=1 width=17) (actual time=0.247..0.478 rows=1 loops=1)

  • Index Cond: (tract = 9,615)
  • Heap Fetches: 1
26. 0.011 0.011 ↑ 1.0 1 1

Index Scan using patch_pkey on patch (cost=0.43..8.45 rows=1 width=157) (actual time=0.011..0.011 rows=1 loops=1)

  • Index Cond: (((skymap)::text = (tract.skymap)::text) AND (tract = 9,615) AND (patch = 10))
27. 633.472 633.472 ↑ 1.9 56 45,248

Index Scan using visit_detector_region_pkey on visit_detector_region (cost=0.29..12.76 rows=104 width=148) (actual time=0.006..0.014 rows=56 loops=45,248)

  • Index Cond: (((instrument)::text = (visit.instrument)::text) AND (visit = visit.visit))
28. 42,898.336 42,898.336 ↓ 0.0 0 42,016

Index Scan using vdpj_visit_idx on visit_detector_patch_join (cost=0.43..204.82 rows=1 width=33) (actual time=0.993..1.021 rows=0 loops=42,016)

  • Index Cond: (visit = visit_detector_region.visit)
  • Filter: ((tract = 9,615) AND (patch = 10) AND ((visit_detector_region.instrument)::text = (instrument)::text) AND (visit_detector_region.detector = detector))
  • Rows Removed by Filter: 7,319
29. 10.875 10.875 ↑ 1.0 1 2,175

Index Only Scan using dataset_collection_pkey on dataset_collection (cost=0.42..8.45 rows=1 width=4) (actual time=0.005..0.005 rows=1 loops=2,175)

  • Index Cond: (dataset_id = dataset.dataset_id)
  • Filter: ((collection)::text = ANY ('{calibs/hsc/default,raw/hsc,refcats/ps1_pv3,skymaps,masks/hsc}'::text[]))
  • Heap Fetches: 2,175
Planning time : 4.984 ms
Execution time : 44,204.878 ms