explain.depesz.com

PostgreSQL's explain analyze made readable

Result: CcFu

Settings
# exclusive inclusive rows x rows loops node
1. 48,720.500 115,836.523 ↑ 42.3 3 1

GroupAggregate (cost=123,876.69..124,015.26 rows=127 width=64) (actual time=66,113.930..115,836.523 rows=3 loops=1)

  • Group Key: (((xpath('/metadata/area/text()'::text, a.rip, '{}'::text[]))[1])::text)
  • Buffers: shared hit=4332957 read=467141 written=4117, temp read=68547 written=68547
2.          

Initplan (forGroupAggregate)

3. 0.003 1.217 ↑ 1.0 1 1

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

  • Buffers: shared hit=89 read=34
4.          

Initplan (forResult)

5. 0.000 1.214 ↑ 1.0 1 1

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

  • Buffers: shared hit=89 read=34
6. 1.214 1.214 ↑ 10,765,491.0 1 1

Index Scan Backward using archiverevisions_pkey on archiverevisions ar (cost=0.43..309,654.20 rows=10,765,491 width=8) (actual time=1.214..1.214 rows=1 loops=1)

  • Index Cond: (id IS NOT NULL)
  • Filter: ("timestamp" <= '1286668800000'::bigint)
  • Rows Removed by Filter: 3208
  • Buffers: shared hit=89 read=34
7. 4,087.901 67,114.806 ↓ 5,371.6 1,085,056 1

Sort (cost=123,876.22..123,876.72 rows=202 width=857) (actual time=66,110.001..67,114.806 rows=1,085,056 loops=1)

  • Sort Key: (((xpath('/metadata/area/text()'::text, a.rip, '{}'::text[]))[1])::text)
  • Sort Method: external merge Disk: 548368kB
  • Buffers: shared hit=4332957 read=467141 written=4117, temp read=68547 written=68547
8. 58,685.553 63,026.905 ↓ 5,371.6 1,085,056 1

Index Scan using cdr8_will_waiver_rev_aud on archive_aud a (cost=0.43..123,868.48 rows=202 width=857) (actual time=1.539..63,026.905 rows=1,085,056 loops=1)

  • Index Cond: ((((((xpath('/metadata/registeredAsWill/text()'::text, rip, '{}'::text[]))[1])::text)::boolean OR (((xpath('/metadata/registeredAsInheritanceWaiver/text()'::text, rip, '{}'::text[]))[1])::text)::boolean) = true) AND (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: 282
  • Buffers: shared hit=4332957 read=467141 written=4117
9.          

SubPlan (forIndex Scan)

10. 1,085.338 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=4324770 read=16607 written=142
11.          

Initplan (forResult)

12. 0.000 3,256.014 ↑ 1.0 1 1,085,338

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

  • Buffers: shared hit=4324770 read=16607 written=142
13. 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=4324770 read=16607 written=142