explain.depesz.com

PostgreSQL's explain analyze made readable

Result: x6t9

Settings
# exclusive inclusive rows x rows loops node
1. 0.010 5,054.005 ↓ 100.0 100 1

Limit (cost=418,094.57..418,094.61 rows=1 width=280) (actual time=5,053.960..5,054.005 rows=100 loops=1)

  • Buffers: shared hit=71078
2. 0.027 5,053.995 ↓ 100.0 100 1

Unique (cost=418,094.57..418,094.61 rows=1 width=280) (actual time=5,053.958..5,053.995 rows=100 loops=1)

  • Buffers: shared hit=71078
3. 3.551 5,053.968 ↓ 100.0 100 1

Sort (cost=418,094.57..418,094.58 rows=1 width=280) (actual time=5,053.957..5,053.968 rows=100 loops=1)

  • Sort Key: (cdr8_from_iso_date(((xpath('/metadata/creationDate/text()'::text, d.rip, '{}'::text[]))[1])::text)) DESC NULLS LAST, d.id, (((((((xpath('/metadata/registrationOfficeWill/text()'::text, d.rip, '{}'::text[]))[1])::text || '\'::text) || ((xpath('/metadata/registrationNumberWill/text()'::text, d.rip, '{}'::text[]))[1])::text) || '\'::text) || ("substring"(((xpath('/metadata/registrationDateWill/text()'::text, d.rip, '{}'::text[]))[1])::text, 0, 5))::text)) COLLATE "de_AT", (((((xpath('/metadata/registrationOfficeWill/text()'::text, d.rip, '{}'::text[]))[1])::text || "substring"(((xpath('/metadata/registrationDateWill/text()'::text, d.rip, '{}'::text[]))[1])::text, 0, 5)) || (lpad(((xpath('/metadata/registrationNumberWill/text()'::text, d.rip, '{}'::text[]))[1])::text, 10, '0'::text))::text)) COLLATE "de_AT", (((((((xpath('/metadata/registrationOfficeInheritanceWaiver/text()'::text, d.rip, '{}'::text[]))[1])::text || '\'::text) || ((xpath('/metadata/registrationNumberInheritanceWaiver/text()'::text, d.rip, '{}'::text[]))[1])::text) || '\'::text) || ("substring"(((xpath('/metadata/registrationDateInheritanceWaiver/text()'::text, d.rip, '{}'::text[]))[1])::text, 0, 5))::text)) COLLATE "de_AT", (((((xpath('/metadata/registrationOfficeInheritanceWaiver/text()'::text, d.rip, '{}'::text[]))[1])::text || "substring"(((xpath('/metadata/registrationDateInheritanceWaiver/text()'::text, d.rip, '{}'::text[]))[1])::text, 0, 5)) || (lpad(((xpath('/metadata/registrationNumberInheritanceWaiver/text()'::text, d.rip, '{}'::text[]))[1])::text, 10, '0'::text))::text)) COLLATE "de_AT", (((xpath('/metadata/dossierNumber/text()'::text, d.rip, '{}'::text[]))[1])::text) COLLATE "de_AT", ((xpath('/metadata/testator/text()'::text, d.rip, '{}'::text[]))::text), (((xpath('/metadata/area/text()'::text, d.rip, '{}'::text[]))[1])::text) COLLATE "de_AT", (((xpath('/metadata/referenceNumber/text()'::text, d.rip, '{}'::text[]))[1])::text) COLLATE "de_AT", ((btrim(((xpath('/metadata/referenceNumber/text()'::text, d.rip, '{}'::text[]))[1])::text, 'abcdefghijklmnopqrstuvwxyz'::text))::integer)
  • Sort Method: quicksort Memory: 390kB
  • Buffers: shared hit=71078
4. 768.762 5,050.417 ↓ 2,064.0 2,064 1

Nested Loop (cost=173,914.19..418,094.56 rows=1 width=280) (actual time=3,596.666..5,050.417 rows=2,064 loops=1)

  • Buffers: shared hit=71078
5. 2.793 4,275.463 ↓ 15.8 2,064 1

Nested Loop (cost=173,913.63..417,869.56 rows=131 width=733) (actual time=3,596.139..4,275.463 rows=2,064 loops=1)

  • Buffers: shared hit=62821
6. 666.773 4,262.350 ↓ 9.5 2,064 1

Bitmap Heap Scan on archive d (cost=173,913.07..417,217.74 rows=218 width=717) (actual time=3,596.112..4,262.350 rows=2,064 loops=1)

  • Recheck Cond: ((cdr8_trim_office(rip) IS NOT NULL) AND (cdr8_trim_office(rip) = 'N999804-1'::text) AND (((xpath('/metadata/area/text()'::text, rip, '{}'::text[]))[1])::text IS NOT NULL) AND (((xpath('/metadata/area/text()'::text, rip, '{}'::text[]))[1])::text = ANY ('{notary-deed,misc-deed,none}'::text[])))
  • Rows Removed by Index Recheck: 108
  • Filter: ((cdr8_from_iso_date(((xpath('/metadata/creationDate/text()'::text, rip, '{}'::text[]))[1])::text) >= '2000-01-01'::date) AND (cdr8_from_iso_date(((xpath('/metadata/creationDate/text()'::text, rip, '{}'::text[]))[1])::text) <= '2018-12-31'::date) AND ((((((xpath('/metadata/registeredAsWill/text()'::text, rip, '{}'::text[]))[1])::text)::boolean IS NOT NULL) AND (((xpath('/metadata/registeredAsWill/text()'::text, rip, '{}'::text[]))[1])::text)::boolean) OR (((((xpath('/metadata/registeredAsInheritanceWaiver/text()'::text, rip, '{}'::text[]))[1])::text)::boolean IS NOT NULL) AND (((xpath('/metadata/registeredAsInheritanceWaiver/text()'::text, rip, '{}'::text[]))[1])::text)::boolean) OR ((NOT (((xpath('/metadata/registeredAsWill/text()'::text, rip, '{}'::text[]))[1])::text)::boolean) AND (((xpath('/metadata/registrationNumberWill/text()'::text, rip, '{}'::text[]))[1])::text IS NOT NULL)) OR ((NOT (((xpath('/metadata/registeredAsInheritanceWaiver/text()'::text, rip, '{}'::text[]))[1])::text)::boolean) AND (((xpath('/metadata/registrationNumberInheritanceWaiver/text()'::text, rip, '{}'::text[]))[1])::text IS NOT NULL))))
  • Rows Removed by Filter: 766
  • Heap Blocks: exact=592
  • Buffers: shared hit=52478
7. 5.780 3,595.577 ↓ 0.0 0 1

BitmapAnd (cost=173,913.07..173,913.07 rows=46,488 width=0) (actual time=3,595.577..3,595.577 rows=0 loops=1)

  • Buffers: shared hit=48078
8. 0.510 0.510 ↑ 45.0 3,262 1

Bitmap Index Scan on cdr8_office_trimmed (cost=0.00..2,145.21 rows=146,665 width=0) (actual time=0.510..0.510 rows=3,262 loops=1)

  • Index Cond: ((cdr8_trim_office(rip) IS NOT NULL) AND (cdr8_trim_office(rip) = 'N999804-1'::text))
  • Buffers: shared hit=17
9. 3,589.287 3,589.287 ↓ 1.0 9,433,489 1

Bitmap Index Scan on cdr8_area (cost=0.00..171,767.50 rows=9,344,300 width=0) (actual time=3,589.287..3,589.287 rows=9,433,489 loops=1)

  • Index Cond: ((((xpath('/metadata/area/text()'::text, rip, '{}'::text[]))[1])::text IS NOT NULL) AND (((xpath('/metadata/area/text()'::text, rip, '{}'::text[]))[1])::text = ANY ('{notary-deed,misc-deed,none}'::text[])))
  • Buffers: shared hit=48061
10. 10.320 10.320 ↑ 1.0 1 2,064

Index Scan using cdr8_sourceid on dossierlink dl (cost=0.56..2.98 rows=1 width=24) (actual time=0.005..0.005 rows=1 loops=2,064)

  • Index Cond: (sourceid = d.id)
  • Buffers: shared hit=10343
11. 6.192 6.192 ↑ 1.0 1 2,064

Index Only Scan using archive_aud_pkey on archive_aud l (cost=0.56..1.67 rows=1 width=16) (actual time=0.003..0.003 rows=1 loops=2,064)

  • Index Cond: ((id = dl.targetid) AND (rev = dl.targetrevision))
  • Heap Fetches: 0
  • Buffers: shared hit=8257