explain.depesz.com

PostgreSQL's explain analyze made readable

Result: XfOl : Test

Settings
# exclusive inclusive rows x rows loops node
1. 88.516 20,023.481 ↑ 3.9 18,309 1

GroupAggregate (cost=231,378.77..243,617.71 rows=72,108 width=160) (actual time=19,308.304..20,023.481 rows=18,309 loops=1)

  • Group Key: images.id
2. 256.363 19,934.965 ↑ 1.3 304,908 1

Merge Join (cost=231,378.77..240,956.19 rows=388,089 width=160) (actual time=19,307.980..19,934.965 rows=304,908 loops=1)

  • Merge Cond: (images.id = page_matches.image_id)
  • Join Filter: ((((images.status)::text <> 'Deleted'::text) AND (page_matches.organisation_id = 8) AND (page_matches.rematched IS NULL) AND (page_matches.domain IS NULL)) OR ((page_matches.domain)::text ~~ ANY ('{%.fi}'::text[])))
3. 503.188 1,143.671 ↑ 1.0 72,108 1

Sort (cost=16,955.43..17,135.70 rows=72,108 width=152) (actual time=1,049.097..1,143.671 rows=72,108 loops=1)

  • Sort Key: images.id DESC
  • Sort Method: external merge Disk: 11544kB
4. 640.483 640.483 ↑ 1.0 72,108 1

Seq Scan on images (cost=0.00..5,712.08 rows=72,108 width=152) (actual time=0.007..640.483 rows=72,108 loops=1)

5. 134.101 18,534.931 ↑ 1.3 304,908 1

Materialize (cost=214,423.23..216,363.68 rows=388,089 width=41) (actual time=18,252.954..18,534.931 rows=304,908 loops=1)

6. 407.612 18,400.830 ↑ 1.3 304,908 1

Sort (cost=214,423.23..215,393.45 rows=388,089 width=41) (actual time=18,252.394..18,400.830 rows=304,908 loops=1)

  • Sort Key: page_matches.image_id DESC
  • Sort Method: external merge Disk: 13568kB
7. 17,580.470 17,993.218 ↑ 1.3 304,908 1

Bitmap Heap Scan on page_matches (cost=4,525.15..166,458.38 rows=388,089 width=41) (actual time=422.455..17,993.218 rows=304,908 loops=1)

  • Recheck Cond: ((domain IS NULL) OR ((domain)::text ~~ ANY ('{%.fi}'::text[])))
  • Rows Removed by Index Recheck: 1911
  • Filter: (((organisation_id = 8) AND (rematched IS NULL) AND (domain IS NULL)) OR ((domain)::text ~~ ANY ('{%.fi}'::text[])))
  • Heap Blocks: exact=43370
8. 0.002 412.748 ↓ 0.0 0 1

BitmapOr (cost=4,525.15..4,525.15 rows=388,089 width=0) (actual time=412.748..412.748 rows=0 loops=1)

9. 3.805 3.805 ↓ 0.0 0 1

Bitmap Index Scan on page_matches_domain (cost=0.00..4.44 rows=1 width=0) (actual time=3.804..3.805 rows=0 loops=1)

  • Index Cond: (domain IS NULL)
10. 408.941 408.941 ↑ 1.3 307,058 1

Bitmap Index Scan on page_matches_domain_gin_trgm_idx (cost=0.00..4,326.67 rows=388,089 width=0) (actual time=408.941..408.941 rows=307,058 loops=1)

  • Index Cond: ((domain)::text ~~ ANY ('{%.fi}'::text[]))