explain.depesz.com

PostgreSQL's explain analyze made readable

Result: TgBa

Settings
# exclusive inclusive rows x rows loops node
1. 47,462.431 247,227.236 ↑ 42.3 3 1

GroupAggregate (cost=0.90..248,599.54 rows=127 width=64) (actual time=68,443.179..247,227.236 rows=3 loops=1)

  • Group Key: ((xpath('/metadata/area/text()'::text, a.rip, '{}'::text[]))[1])::text
  • Buffers: shared hit=4335263 read=988642
2.          

Initplan (forGroupAggregate)

3. 0.003 3.954 ↑ 1.0 1 1

Result (cost=0.46..0.47 rows=1 width=8) (actual time=3.954..3.954 rows=1 loops=1)

  • Buffers: shared hit=84 read=39
4.          

Initplan (forResult)

5. 0.002 3.951 ↑ 1.0 1 1

Limit (cost=0.43..0.46 rows=1 width=8) (actual time=3.951..3.951 rows=1 loops=1)

  • Buffers: shared hit=84 read=39
6. 3.949 3.949 ↑ 10,767,193.0 1 1

Index Scan Backward using archiverevisions_pkey on archiverevisions ar (cost=0.43..309,688.29 rows=10,767,193 width=8) (actual time=3.949..3.949 rows=1 loops=1)

  • Index Cond: (id IS NOT NULL)
  • Filter: ("timestamp" <= '1286668800000'::bigint)
  • Rows Removed by Filter: 3081
  • Buffers: shared hit=84 read=39
7. 195,419.499 199,760.851 ↓ 5,371.6 1,085,056 1

Index Scan using cdr8_area_will_waiver_rev_aud on archive_aud a (cost=0.43..248,461.01 rows=202 width=849) (actual time=32.365..199,760.851 rows=1,085,056 loops=1)

  • Index Cond: (rev <= $1)
  • Filter: (((((xpath('/metadata/registeredAsWill/text()'::text, rip, '{}'::text[]))[1])::text)::boolean OR (((xpath('/metadata/registeredAsInheritanceWaiver/text()'::text, rip, '{}'::text[]))[1])::text)::boolean) AND (rev = (SubPlan 4)))
  • Rows Removed by Filter: 2363776
  • Buffers: shared hit=4335263 read=988642
8.          

SubPlan (forIndex Scan)

9. 0.000 4,341.352 ↑ 1.0 1 1,085,338

Result (cost=2.85..2.86 rows=1 width=8) (actual time=0.004..0.004 rows=1 loops=1,085,338)

  • Buffers: shared hit=4324745 read=16623
10.          

Initplan (forResult)

11. 1,085.338 4,341.352 ↑ 1.0 1 1,085,338

Limit (cost=0.43..2.85 rows=1 width=8) (actual time=0.004..0.004 rows=1 loops=1,085,338)

  • Buffers: shared hit=4324745 read=16623
12. 3,256.014 3,256.014 ↑ 1.0 1 1,085,338

Index Only Scan Backward using archive_aud_pkey on archive_aud au (cost=0.43..2.85 rows=1 width=8) (actual time=0.003..0.003 rows=1 loops=1,085,338)

  • Index Cond: ((id = a.id) AND (rev IS NOT NULL))
  • Heap Fetches: 1085338
  • Buffers: shared hit=4324745 read=16623