explain.depesz.com

PostgreSQL's explain analyze made readable

Result: eEMy

Settings
# exclusive inclusive rows x rows loops node
1. 0.044 45,723.646 ↓ 141.0 141 1

Unique (cost=628,824.16..628,824.19 rows=1 width=300) (actual time=45,723.588..45,723.646 rows=141 loops=1)

  • Buffers: shared hit=1605885
2. 0.412 45,723.602 ↓ 141.0 141 1

Sort (cost=628,824.16..628,824.16 rows=1 width=300) (actual time=45,723.587..45,723.602 rows=141 loops=1)

  • Sort Key: r.id, (((((((xpath('/metadata/registrationOfficeProcuration/text()'::text, r.rip, '{}'::text[]))[1])::text || '\'::text) || ((xpath('/metadata/registrationNumberProcuration/text()'::text, r.rip, '{}'::text[]))[1])::text) || '\'::text) || "substring"(((xpath('/metadata/registrationDateProcuration/text()'::text, r.rip, '{}'::text[]))[1])::text, 0, 5))), (((((((xpath('/metadata/registrationOfficeProcuration/text()'::text, r.rip, '{}'::text[]))[1])::text || '\'::text) || lpad(((xpath('/metadata/registrationNumberProcuration/text()'::text, r.rip, '{}'::text[]))[1])::text, 10, '0'::text)) || '\'::text) || "substring"(((xpath('/metadata/registrationDateProcuration/text()'::text, r.rip, '{}'::text[]))[1])::text, 0, 5))), (((xpath('/metadata/procurationType/text()'::text, r.rip, '{}'::text[]))[1])::text), ((((xpath('/metadata/registrationDateProcuration/text()'::text, r.rip, '{}'::text[]))[1])::text)::date), ((xpath('/metadata/activepartyprocuration/text()'::text, r.rip, '{}'::text[]))::text), ((xpath('/metadata/passivepartyprocuration/text()'::text, r.rip, '{}'::text[]))::text), (((xpath('/metadata/registrationNumberProcuration/text()'::text, r.rip, '{}'::text[]))[1])::text), (((xpath('/metadata/registrationOfficeProcuration/text()'::text, r.rip, '{}'::text[]))[1])::text), (((SubPlan 1))::text), (((((((xpath('/metadata/procurationType/text()'::text, r.rip, '{}'::text[]))[1])::text = 'lawfulProcuration'::text) AND (((((xpath('/metadata/courtOrderEnd/text()'::text, r.rip, '{}'::text[]))[1])::text IS NOT NULL) AND ((((xpath('/metadata/courtOrderEnd/text()'::text, r.rip, '{}'::text[]))[1])::text)::date < ('now'::cstring)::date)) OR COALESCE(((SubPlan 2) < ('now'::cstring)::date), false))) OR (SubPlan 3)))::text)
  • Sort Method: quicksort Memory: 62kB
  • Buffers: shared hit=1605885
3. 32,487.664 45,723.190 ↓ 141.0 141 1

Nested Loop (cost=1.44..628,824.15 rows=1 width=300) (actual time=124.616..45,723.190 rows=141 loops=1)

  • Join Filter: (((((xpath('/metadata/registeredAsProcuration/text()'::text, r.rip, '{}'::text[]))[1])::text)::boolean AND (((xpath('/metadata/procurationType/text()'::text, r.rip, '{}'::text[]))[1])::text IS NOT NULL) AND (((xpath('/metadata/name/text()'::text, rl.rip, '{}'::text[]))[1])::text IS NOT NULL) AND (((xpath('/metadata/name/text()'::text, rl.rip, '{}'::text[]))[1])::text ~* '^haber'::text)) OR (cdr8_registration_code(((xpath('/metadata/registrationOfficeProcuration/text()'::text, r.rip, '{}'::text[]))[1])::text, ((xpath('/metadata/registrationNumberProcuration/text()'::text, r.rip, '{}'::text[]))[1])::text, ((xpath('/metadata/registrationDateProcuration/text()'::text, r.rip, '{}'::text[]))[1])::text) = ANY ('{ESV00112018}'::text[])))
  • Rows Removed by Join Filter: 184061
  • Buffers: shared hit=1605885
4. 251.706 2,178.393 ↓ 184,208.0 184,208 1

Nested Loop (cost=1.00..628,796.98 rows=1 width=802) (actual time=0.035..2,178.393 rows=184,208 loops=1)

  • Buffers: shared hit=866800
5. 453.023 453.023 ↑ 1.1 184,208 1

Index Scan using cdr8_relation on dossierlink rdl (cost=0.56..132,408.48 rows=195,966 width=24) (actual time=0.023..453.023 rows=184,208 loops=1)

  • Index Cond: ((relation)::text = 'activepartyprocuration'::text)
  • Buffers: shared hit=128983
6. 1,473.664 1,473.664 ↑ 1.0 1 184,208

Index Scan using uk_aflovfbip87753ua06hrfm8dp on archive_aud rl (cost=0.44..2.52 rows=1 width=810) (actual time=0.008..0.008 rows=1 loops=184,208)

  • Index Cond: (rev = rdl.targetrevision)
  • Filter: (rdl.targetid = id)
  • Buffers: shared hit=737817
7. 11,052.480 11,052.480 ↑ 1.0 1 184,208

Index Scan using archive_pkey on archive r (cost=0.44..4.03 rows=1 width=806) (actual time=0.060..0.060 rows=1 loops=184,208)

  • Index Cond: (id = rdl.sourceid)
  • Filter: (((((xpath('/metadata/registeredAsProcuration/text()'::text, rip, '{}'::text[]))[1])::text)::boolean AND (((xpath('/metadata/procurationType/text()'::text, rip, '{}'::text[]))[1])::text IS NOT NULL)) OR (cdr8_registration_code(((xpath('/metadata/registrationOfficeProcuration/text()'::text, rip, '{}'::text[]))[1])::text, ((xpath('/metadata/registrationNumberProcuration/text()'::text, rip, '{}'::text[]))[1])::text, ((xpath('/metadata/registrationDateProcuration/text()'::text, rip, '{}'::text[]))[1])::text) = ANY ('{ESV00112018}'::text[])))
  • Rows Removed by Filter: 0
  • Buffers: shared hit=737892
8.          

SubPlan (forNested Loop)

9. 3.243 3.243 ↓ 0.0 0 141

Index Scan using cdr8_procurationchain on archive a (cost=1.31..7.41 rows=4 width=0) (actual time=0.023..0.023 rows=0 loops=141)

  • Index Cond: ((((xpath('/metadata/registrationNumberProcuration/text()'::text, rip, '{}'::text[]))[1])::text = ((xpath('/metadata/registrationNumberProcuration/text()'::text, r.rip, '{}'::text[]))[1])::text) AND (((xpath('/metadata/registrationOfficeProcuration/text()'::text, rip, '{}'::text[]))[1])::text = ((xpath('/metadata/registrationOfficeProcuration/text()'::text, r.rip, '{}'::text[]))[1])::text) AND (((xpath('/metadata/registrationDateProcuration/text()'::text, rip, '{}'::text[]))[1])::text = ((xpath('/metadata/registrationDateProcuration/text()'::text, r.rip, '{}'::text[]))[1])::text) AND (((xpath('/metadata/amendmentTypeProcuration/text()'::text, rip, '{}'::text[]))[1])::text IS NOT NULL))
  • Buffers: shared hit=566
10. 0.000 0.000 ↓ 0.0 0

Aggregate (cost=4.00..4.01 rows=1 width=4) (never executed)

11. 0.000 0.000 ↓ 0.0 0

Index Scan using cdr8_procurationchain on archive a_1 (cost=1.31..3.74 rows=1 width=798) (never executed)

  • Index Cond: ((((xpath('/metadata/registrationNumberProcuration/text()'::text, rip, '{}'::text[]))[1])::text = ((xpath('/metadata/registrationNumberProcuration/text()'::text, r.rip, '{}'::text[]))[1])::text) AND (((xpath('/metadata/registrationOfficeProcuration/text()'::text, rip, '{}'::text[]))[1])::text = ((xpath('/metadata/registrationOfficeProcuration/text()'::text, r.rip, '{}'::text[]))[1])::text) AND (((xpath('/metadata/registrationDateProcuration/text()'::text, rip, '{}'::text[]))[1])::text = ((xpath('/metadata/registrationDateProcuration/text()'::text, r.rip, '{}'::text[]))[1])::text) AND (((xpath('/metadata/amendmentTypeProcuration/text()'::text, rip, '{}'::text[]))[1])::text IS NOT NULL) AND (((xpath('/metadata/amendmentTypeProcuration/text()'::text, rip, '{}'::text[]))[1])::text = 'renewal'::text))
12. 1.410 1.410 ↓ 0.0 0 141

Index Scan using cdr8_procurationchain on archive a_2 (cost=1.31..10.45 rows=1 width=0) (actual time=0.010..0.010 rows=0 loops=141)

  • Index Cond: ((((xpath('/metadata/registrationNumberProcuration/text()'::text, rip, '{}'::text[]))[1])::text = ((xpath('/metadata/registrationNumberProcuration/text()'::text, r.rip, '{}'::text[]))[1])::text) AND (((xpath('/metadata/registrationOfficeProcuration/text()'::text, rip, '{}'::text[]))[1])::text = ((xpath('/metadata/registrationOfficeProcuration/text()'::text, r.rip, '{}'::text[]))[1])::text) AND (((xpath('/metadata/registrationDateProcuration/text()'::text, rip, '{}'::text[]))[1])::text = ((xpath('/metadata/registrationDateProcuration/text()'::text, r.rip, '{}'::text[]))[1])::text) AND (((xpath('/metadata/amendmentTypeProcuration/text()'::text, rip, '{}'::text[]))[1])::text IS NOT NULL))
  • Filter: ((((xpath('/metadata/amendmentTypeProcuration/text()'::text, rip, '{}'::text[]))[1])::text = ANY ('{discontinuation,discontinuation3,discontinuation4,discontinuation6,discontinuationByDeath,expirationByResolution}'::text[])) AND (((xpath('/metadata/referencedpartyprocuration/text()'::text, rip, '{}'::text[]))[1])::text = ((xpath('/metadata/activepartyprocuration/text()'::text, r.rip, '{}'::text[]))[1])::text))
  • Rows Removed by Filter: 0
  • Buffers: shared hit=567