explain.depesz.com

PostgreSQL's explain analyze made readable

Result: ANpT

Settings
# exclusive inclusive rows x rows loops node
1. 0.002 926,185.567 ↑ 50.0 2 1

Limit (cost=31,450,319.49..31,450,320.24 rows=100 width=72) (actual time=926,185.565..926,185.567 rows=2 loops=1)

  • Buffers: shared hit=201851 read=8678090
2. 0.002 926,185.565 ↑ 100.0 2 1

Unique (cost=31,450,319.49..31,450,320.99 rows=200 width=72) (actual time=926,185.564..926,185.565 rows=2 loops=1)

  • Buffers: shared hit=201851 read=8678090
3. 0.029 926,185.563 ↑ 100.0 2 1

Sort (cost=31,450,319.49..31,450,319.99 rows=200 width=72) (actual time=926,185.563..926,185.563 rows=2 loops=1)

  • Sort Key: ((("left"(((xpath('/metadata/office/text()'::text, rip, '{}'::text[]))[1])::text, 2)))::text) COLLATE "de_AT" NULLS FIRST, (count(("left"(((xpath('/metadata/office/text()'::text, rip, '{}'::text[]))[1])::text, 2))))
  • Sort Method: quicksort Memory: 25kB
  • Buffers: shared hit=201851 read=8678090
4. 0.035 926,185.534 ↑ 100.0 2 1

GroupAggregate (cost=31,440,723.27..31,450,311.85 rows=200 width=72) (actual time=926,185.512..926,185.534 rows=2 loops=1)

  • Group Key: ("left"(((xpath('/metadata/office/text()'::text, rip, '{}'::text[]))[1])::text, 2))
  • Buffers: shared hit=201851 read=8678090
5. 0.194 926,185.499 ↑ 889.8 41 1

Sort (cost=31,440,723.27..31,440,814.47 rows=36,483 width=741) (actual time=926,185.466..926,185.499 rows=41 loops=1)

  • Sort Key: ("left"(((xpath('/metadata/office/text()'::text, rip, '{}'::text[]))[1])::text, 2))
  • Sort Method: quicksort Memory: 66kB
  • Buffers: shared hit=201851 read=8678090
6. 926,185.305 926,185.305 ↑ 889.8 41 1

Seq Scan on archive d (cost=0.00..31,437,958.78 rows=36,483 width=741) (actual time=190,460.321..926,185.305 rows=41 loops=1)

  • Filter: ((((xpath('/metadata/registrationNumberAdvanceDirective/text()'::text, rip, '{}'::text[]))[1])::text IS NOT NULL) AND (((xpath('/metadata/registrationNumberAdvanceDirective/text()'::text, rip, '{}'::text[]))[1])::text <> ''::text) AND (((xpath('/metadata/office/text()'::text, rip, '{}'::text[]))[1])::text ~ '^N[1-6]'::text))
  • Rows Removed by Filter: 29480346
  • Buffers: shared hit=201851 read=8678090