explain.depesz.com

PostgreSQL's explain analyze made readable

Result: ACAv

Settings
# exclusive inclusive rows x rows loops node
1. 11.123 419.332 ↓ 288.3 14,702 1

Merge Left Join (cost=1,044.27..1,057.21 rows=51 width=228) (actual time=382.252..419.332 rows=14,702 loops=1)

  • Merge Cond: ((subltree(t2.path, 0, (nlevel(t2.path) - 1)) = (CASE WHEN ((subltree(t2_2.path, (nlevel(t2_2.path) - 4), (nlevel(t2_2.path) - 3)))::text = 'complextype'::text) THEN subltree(t2_2.path, 0, (nlevel(t2_2.path) - 4)) ELSE subltree(t2_2.path, 0, (nlevel(t2_2.path) - 3)) END)) AND ((t2.xml)::text = (t2_2.xml)::text))
2. 13.587 381.382 ↓ 288.3 14,702 1

Merge Left Join (cost=563.79..566.15 rows=51 width=300) (actual time=355.580..381.382 rows=14,702 loops=1)

  • Merge Cond: (((subltree(t2.path, 0, (nlevel(t2.path) - 1))) = (subltree(t2_1.path, 0, (nlevel(t2_1.path) - 1)))) AND ((t2.xml)::text = (t2_1.xml)::text))
3. 55.767 290.441 ↓ 288.3 14,702 1

Sort (cost=376.94..377.07 rows=51 width=255) (actual time=282.192..290.441 rows=14,702 loops=1)

  • Sort Key: (subltree(t2.path, 0, (nlevel(t2.path) - 1))), t2.xml
  • Sort Method: external merge Disk: 6096kB
4. 19.973 234.674 ↓ 288.3 14,702 1

Merge Left Join (cost=373.70..375.50 rows=51 width=255) (actual time=200.711..234.674 rows=14,702 loops=1)

  • Merge Cond: (((t2.xml)::text = (t2_3.xml)::text) AND (((subltree(t2.path, 0, (nlevel(t2.path) - 1)) || 'annotation.documentation.__text'::ltree)) = t2_3.path))
5. 70.006 103.926 ↓ 288.3 14,702 1

Sort (cost=186.85..186.98 rows=51 width=210) (actual time=96.344..103.926 rows=14,702 loops=1)

  • Sort Key: t2.xml, ((subltree(t2.path, 0, (nlevel(t2.path) - 1)) || 'annotation.documentation.__text'::ltree))
  • Sort Method: external merge Disk: 4944kB
6. 12.272 33.920 ↓ 288.3 14,702 1

Bitmap Heap Scan on t2 (cost=8.67..185.40 rows=51 width=210) (actual time=21.864..33.920 rows=14,702 loops=1)

  • Recheck Cond: (path ~ '*._name'::lquery)
  • Heap Blocks: exact=1315
7. 21.648 21.648 ↓ 288.3 14,702 1

Bitmap Index Scan on path_gist_idx (cost=0.00..8.66 rows=51 width=0) (actual time=21.648..21.648 rows=14,702 loops=1)

  • Index Cond: (path ~ '*._name'::lquery)
8. 82.589 110.775 ↓ 283.3 14,447 1

Sort (cost=186.85..186.98 rows=51 width=210) (actual time=104.347..110.775 rows=14,447 loops=1)

  • Sort Key: t2_3.xml, t2_3.path
  • Sort Method: external sort Disk: 4560kB
9. 8.336 28.186 ↓ 283.3 14,447 1

Bitmap Heap Scan on t2 t2_3 (cost=8.67..185.40 rows=51 width=210) (actual time=20.011..28.186 rows=14,447 loops=1)

  • Recheck Cond: (path ~ '*.annotation.documentation.__text'::lquery)
  • Heap Blocks: exact=1213
10. 19.850 19.850 ↓ 283.3 14,447 1

Bitmap Index Scan on path_gist_idx (cost=0.00..8.66 rows=51 width=0) (actual time=19.850..19.850 rows=14,447 loops=1)

  • Index Cond: (path ~ '*.annotation.documentation.__text'::lquery)
11. 48.261 77.354 ↓ 189.4 9,658 1

Sort (cost=186.85..186.98 rows=51 width=210) (actual time=73.375..77.354 rows=9,658 loops=1)

  • Sort Key: (subltree(t2_1.path, 0, (nlevel(t2_1.path) - 1))), t2_1.xml
  • Sort Method: external sort Disk: 3168kB
12. 8.721 29.093 ↓ 189.4 9,658 1

Bitmap Heap Scan on t2 t2_1 (cost=8.67..185.40 rows=51 width=210) (actual time=20.555..29.093 rows=9,658 loops=1)

  • Recheck Cond: (path ~ '*._type'::lquery)
  • Heap Blocks: exact=1225
13. 20.372 20.372 ↓ 189.4 9,658 1

Bitmap Index Scan on path_gist_idx (cost=0.00..8.66 rows=51 width=0) (actual time=20.372..20.372 rows=9,658 loops=1)

  • Index Cond: (path ~ '*._type'::lquery)
14. 3.075 26.827 ↓ 11.3 1,720 1

Sort (cost=480.48..480.86 rows=152 width=210) (actual time=26.662..26.827 rows=1,720 loops=1)

  • Sort Key: (CASE WHEN ((subltree(t2_2.path, (nlevel(t2_2.path) - 4), (nlevel(t2_2.path) - 3)))::text = 'complextype'::text) THEN subltree(t2_2.path, 0, (nlevel(t2_2.path) - 4)) ELSE subltree(t2_2.path, 0, (nlevel(t2_2.path) - 3)) END), t2_2.xml
  • Sort Method: quicksort Memory: 818kB
15. 2.253 23.752 ↓ 11.3 1,720 1

Bitmap Heap Scan on t2 t2_2 (cost=26.09..474.97 rows=152 width=210) (actual time=21.575..23.752 rows=1,720 loops=1)

  • Recheck Cond: ((path ~ '*.complexcontent.extension._base'::lquery) OR (path ~ '*.simplecontent.extension._base'::lquery) OR (path ~ '*.simpletype.restriction._base'::lquery))
  • Heap Blocks: exact=544
16. 0.004 21.499 ↓ 0.0 0 1

BitmapOr (cost=26.09..26.09 rows=152 width=0) (actual time=21.498..21.499 rows=0 loops=1)

17. 8.493 8.493 ↓ 15.0 763 1

Bitmap Index Scan on path_gist_idx (cost=0.00..8.66 rows=51 width=0) (actual time=8.493..8.493 rows=763 loops=1)

  • Index Cond: (path ~ '*.complexcontent.extension._base'::lquery)
18. 6.538 6.538 ↑ 2.1 24 1

Bitmap Index Scan on path_gist_idx (cost=0.00..8.66 rows=51 width=0) (actual time=6.538..6.538 rows=24 loops=1)

  • Index Cond: (path ~ '*.simplecontent.extension._base'::lquery)
19. 6.464 6.464 ↓ 18.3 933 1

Bitmap Index Scan on path_gist_idx (cost=0.00..8.66 rows=51 width=0) (actual time=6.464..6.464 rows=933 loops=1)

  • Index Cond: (path ~ '*.simpletype.restriction._base'::lquery)