explain.depesz.com

PostgreSQL's explain analyze made readable

Result: NAJR : pghackers slow query

Settings
# exclusive inclusive rows x rows loops node
1. 0.004 41,027.412 ↑ 1.7 3 1

Limit (cost=13.40..20.22 rows=5 width=233) (actual time=41,023.057..41,027.412 rows=3 loops=1)

  • Buffers: shared hit=75782139 read=1834969
  • -> Index Scan using unique_chname on channel (cost=0.41..8.43 rows=1 width=8) (actual time=2.442..2.443 rows=1 loops=
  • Index Cond: ((name)::text = 'BUIL-B36-VA-RT-RT1:CL0001-2-ABW'::text)
  • Buffers: shared read=4
2.          

Initplan (forLimit)

3. 0.004 41,027.408 ↑ 2,039,107.7 3 1

Result (cost=4.96..8,344,478.65 rows=6,117,323 width=233) (actual time=41,023.055..41,027.408 rows=3 loops=1)

  • Buffers: shared hit=75782139 read=1834969
4. 41,027.404 41,027.404 ↑ 2,039,107.7 3 1

Merge Append (cost=4.96..8,283,305.42 rows=6,117,323 width=201) (actual time=41,023.054..41,027.404 rows=3 loops=1)

  • Sort Key: c.smpl_time DESC
  • Buffers: shared hit=75782139 read=1834969
  • -> Index Scan Backward using smpl_time_qa_idx on sample c (cost=0.12..8.14 rows=1 width=326) (actual time=0
  • Filter: (channel_id = $0)
  • Buffers: shared hit=1
  • -> Index Scan Backward using sample_time_c_idx on sample_ctrl c_2 (cost=0.42..116482.81 rows=33661 width=32
  • -> Index Scan Backward using sample_time_bm_idx on sample_buil_month c_4 (cost=0.56..60293.88 rows=15711 wi
  • -> Index Scan Backward using smpl_time_bx2_idx on sample_buil_year c_5 (cost=0.56..2023925.30 rows=3162364
  • -> Index Scan Backward using sample_time_cm_idx on sample_ctrl_month c_6 (cost=0.56..1862587.12 rows=537562
  • -> Index Scan Backward using smpl_time_cmx2_idx on sample_ctrl_year c_7 (cost=0.57..3186305.67 rows=2094186
  • -> Index Scan Backward using sample_time_um_idx on sample_util_month c_8 (cost=0.57..360454.53 rows=97101 w
  • -> Index Scan Backward using sample_time_uy_idx on sample_util_year c_9 (cost=0.57..498663.22 rows=160954 w
5. 0.000 0.000 ↓ 0.0

Index Scan Backward using sample_time_b_idx on sample_buil c_1 (cost=0.42..22,318.03 rows=6,300 width=320) (actual rows= loops=)

  • Index Cond: (channel_id = $0)
  • Buffers: shared read=7
  • Index Cond: (channel_id = $0)
  • Buffers: shared read=3
6. 0.000 0.000 ↓ 0.0

Index Scan Backward using sample_time_u_idx on sample_util c_3 (cost=0.43..35,366.72 rows=9,483 width=320) (actual rows= loops=)

  • Index Cond: (channel_id = $0)
  • Buffers: shared read=3
  • Index Cond: (channel_id = $0)
  • Buffers: shared read=8
  • Filter: (channel_id = $0)
  • Rows Removed by Filter: 50597834
  • Buffers: shared hit=25913147 read=713221
  • Index Cond: (channel_id = $0)
  • Buffers: shared read=4
  • Filter: (channel_id = $0)
  • Rows Removed by Filter: 79579075
  • Buffers: shared hit=49868991 read=1121715
  • Index Cond: (channel_id = $0)
  • Buffers: shared read=4
  • Index Cond: (channel_id = $0)
  • Buffers: shared read=4