explain.depesz.com

PostgreSQL's explain analyze made readable

Result: yCYd

Settings
# exclusive inclusive rows x rows loops node
1. 0.001 1,180.170 ↓ 0.0 0 1

Limit (cost=5,987.23..5,987.25 rows=1 width=152) (actual time=1,180.170..1,180.170 rows=0 loops=1)

  • Buffers: shared hit=4 read=18110
2. 0.000 1,180.169 ↓ 0.0 0 1

Unique (cost=5,987.23..5,987.25 rows=1 width=152) (actual time=1,180.169..1,180.169 rows=0 loops=1)

  • Buffers: shared hit=4 read=18110
3. 0.038 1,180.169 ↓ 0.0 0 1

Sort (cost=5,987.23..5,987.24 rows=1 width=152) (actual time=1,180.169..1,180.169 rows=0 loops=1)

  • Sort Key: (((xpath('/metadata/office/text()'::text, rip, '{}'::text[]))[1])::text) COLLATE "de_AT" NULLS FIRST, ((btrim(((xpath('/metadata/referenceNumber/text()'::text, rip, '{}'::text[]))[1])::text, 'abcdefghijklmnopqrstuvwxyz'::text))::integer) DESC NULLS LAST, id, (((xpath('/metadata/area/text()'::text, rip, '{}'::text[]))[1])::text) COLLATE "de_AT", ((((xpath('/metadata/creationDate/text()'::text, rip, '{}'::text[]))[1])::text)::date), (((xpath('/metadata/referenceNumber/text()'::text, rip, '{}'::text[]))[1])::text) COLLATE "de_AT", ((xpath('/metadata/*[local-name()="party" or local-name()="testator" or local-name()="activepartyprocuration" or local-name()="passivepartyprocuration" or local-name()="depositary" or local-name()="patient"]/text()'::text, rip, '{}'::text[]))::text)
  • Sort Method: quicksort Memory: 25kB
  • Buffers: shared hit=4 read=18110
4. 13.688 1,180.131 ↓ 0.0 0 1

Bitmap Heap Scan on archive d (cost=3,001.75..5,987.22 rows=1 width=152) (actual time=1,180.131..1,180.131 rows=0 loops=1)

  • Recheck Cond: ((((xpath('/metadata/office/text()'::text, rip, '{}'::text[]))[1])::text IS NOT NULL) AND (((xpath('/metadata/office/text()'::text, rip, '{}'::text[]))[1])::text = 'N999816-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,gog-deed,misc-deed}'::text[])))
  • Rows Removed by Index Recheck: 14
  • Filter: ((((xpath('/metadata/accessCode/text()'::text, rip, '{}'::text[]))[1])::text IS NOT NULL) AND (((xpath('/metadata/accessCode/text()'::text, rip, '{}'::text[]))[1])::text = '12345'::text) AND ((((xpath('/metadata/creationDate/text()'::text, rip, '{}'::text[]))[1])::text)::date >= '2018-12-04'::date) AND ((((xpath('/metadata/creationDate/text()'::text, rip, '{}'::text[]))[1])::text)::date <= '2018-12-22'::date) AND ((btrim(((xpath('/metadata/referenceNumber/text()'::text, rip, '{}'::text[]))[1])::text, 'abcdefghijklmnopqrstuvwxyz'::text))::integer >= 1) AND ((btrim(((xpath('/metadata/referenceNumber/text()'::text, rip, '{}'::text[]))[1])::text, 'abcdefghijklmnopqrstuvwxyz'::text))::integer <= 3))
  • Rows Removed by Filter: 92
  • Heap Blocks: exact=76
  • Buffers: shared hit=4 read=18110
5. 6.512 1,166.443 ↓ 0.0 0 1

BitmapAnd (cost=3,001.75..3,001.75 rows=796 width=0) (actual time=1,166.443..1,166.443 rows=0 loops=1)

  • Buffers: shared hit=4 read=18034
6. 0.189 0.189 ↑ 392.2 136 1

Bitmap Index Scan on cdr8_office (cost=0.00..780.97 rows=53,334 width=0) (actual time=0.189..0.189 rows=136 loops=1)

  • Index Cond: ((((xpath('/metadata/office/text()'::text, rip, '{}'::text[]))[1])::text IS NOT NULL) AND (((xpath('/metadata/office/text()'::text, rip, '{}'::text[]))[1])::text = 'N999816-1'::text))
  • Buffers: shared read=3
7. 1,159.742 1,159.742 ↓ 29.4 4,704,738 1

Bitmap Index Scan on cdr8_area (cost=0.00..2,220.52 rows=160,003 width=0) (actual time=1,159.742..1,159.742 rows=4,704,738 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,gog-deed,misc-deed}'::text[])))
  • Buffers: shared hit=4 read=18031