explain.depesz.com

PostgreSQL's explain analyze made readable

Result: xCPe

Settings
# exclusive inclusive rows x rows loops node
1. 0.040 537.747 ↓ 141.0 141 1

Unique (cost=75,199.59..75,199.62 rows=1 width=300) (actual time=537.681..537.747 rows=141 loops=1)

  • Buffers: shared hit=53828
2. 0.273 537.707 ↓ 141.0 141 1

Sort (cost=75,199.59..75,199.59 rows=1 width=300) (actual time=537.679..537.707 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=53828
3. 68.740 537.434 ↓ 141.0 141 1

Nested Loop (cost=93.18..75,199.58 rows=1 width=300) (actual time=16.334..537.434 rows=141 loops=1)

  • Buffers: shared hit=53828
4. 8.215 453.104 ↓ 142.0 142 1

Nested Loop (cost=92.74..75,175.43 rows=1 width=8) (actual time=15.430..453.104 rows=142 loops=1)

  • Buffers: shared hit=52126
5. 289.859 303.544 ↓ 1.0 9,423 1

Bitmap Heap Scan on archive_aud rl (cost=92.30..16,134.72 rows=9,387 width=16) (actual time=15.344..303.544 rows=9,423 loops=1)

  • Recheck Cond: (((xpath('/metadata/name/text()'::text, rip, '{}'::text[]))[1])::text ~* '^haber'::text)
  • Heap Blocks: exact=9366
  • Buffers: shared hit=9801
6. 13.685 13.685 ↑ 1.0 9,423 1

Bitmap Index Scan on cdr8_client_name_aud (cost=0.00..89.95 rows=9,434 width=0) (actual time=13.685..13.685 rows=9,423 loops=1)

  • Index Cond: (((xpath('/metadata/name/text()'::text, rip, '{}'::text[]))[1])::text ~* '^haber'::text)
  • Buffers: shared hit=435
7. 141.345 141.345 ↓ 0.0 0 9,423

Index Scan using cdr8_targetid on dossierlink rdl (cost=0.44..6.28 rows=1 width=24) (actual time=0.015..0.015 rows=0 loops=9,423)

  • Index Cond: (targetid = rl.id)
  • Filter: (((relation)::text = 'activepartyprocuration'::text) AND (rl.rev = targetrevision))
  • Rows Removed by Filter: 1
  • Buffers: shared hit=42325
8. 11.360 11.360 ↑ 1.0 1 142

Index Scan using archive_pkey on archive r (cost=0.44..3.03 rows=1 width=806) (actual time=0.079..0.080 rows=1 loops=142)

  • Index Cond: (id = rdl.sourceid)
  • Filter: ((((xpath('/metadata/procurationType/text()'::text, rip, '{}'::text[]))[1])::text IS NOT NULL) AND (((xpath('/metadata/registeredAsProcuration/text()'::text, rip, '{}'::text[]))[1])::text)::boolean)
  • Rows Removed by Filter: 0
  • Buffers: shared hit=569
9.          

SubPlan (forNested Loop)

10. 2.820 2.820 ↓ 0.0 0 141

Index Scan using cdr8_procurationchain on archive a (cost=1.31..7.41 rows=4 width=0) (actual time=0.020..0.020 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
11. 0.000 0.000 ↓ 0.0 0

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

12. 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))
13. 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