explain.depesz.com

PostgreSQL's explain analyze made readable

Result: JxX

Settings
# exclusive inclusive rows x rows loops node
1. 0.002 742.920 ↑ 1.0 1 1

Subquery Scan on m (cost=52,524.36..58,788.66 rows=1 width=1,035) (actual time=370.544..742.920 rows=1 loops=1)

2. 49.918 742.918 ↑ 1.0 1 1

Nested Loop Left Join (cost=52,524.36..58,788.65 rows=1 width=1,108) (actual time=370.542..742.918 rows=1 loops=1)

  • Join Filter: ((movie.gid)::ltree = (relevant_country_override.gid_entity)::ltree)
  • Rows Removed by Join Filter: 194031
  • Filter: ((relevant_country_override.active IS NULL) OR relevant_country_override.active)
3.          

CTE relevant_country_override

4. 194.395 590.577 ↓ 1.0 194,032 1

Hash Join (cost=1.52..49,590.66 rows=190,573 width=84) (actual time=0.080..590.577 rows=194,032 loops=1)

  • Hash Cond: (entity_has_override.id_country = brand_1.id_country)
5. 396.173 396.173 ↓ 1.0 1,143,742 1

Index Scan using country_entity_has_override_gid_idx on entity_has_override (cost=0.43..44,467.93 rows=1,143,438 width=84) (actual time=0.052..396.173 rows=1,143,742 loops=1)

  • Index Cond: (subltree((gid_entity)::ltree, 0, 2) = 'movie.movie'::ltree)
6. 0.002 0.009 ↑ 1.0 1 1

Hash (cost=1.07..1.07 rows=1 width=4) (actual time=0.009..0.009 rows=1 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
7. 0.007 0.007 ↑ 1.0 1 1

Seq Scan on brand brand_1 (cost=0.00..1.07 rows=1 width=4) (actual time=0.006..0.007 rows=1 loops=1)

  • Filter: (id = 'AD'::bpchar)
  • Rows Removed by Filter: 5
8.          

CTE relevant_brand_override

9. 0.191 11.282 ↑ 12.8 150 1

Bitmap Heap Scan on entity_has_override entity_has_override_1 (cost=1,248.83..2,932.85 rows=1,913 width=104) (actual time=11.110..11.282 rows=150 loops=1)

  • Recheck Cond: ((id_brand = 'AD'::bpchar) AND (subltree((gid_entity)::ltree, 0, 2) = 'movie.movie'::ltree))
  • Heap Blocks: exact=12
10. 0.037 11.091 ↓ 0.0 0 1

BitmapAnd (cost=1,248.83..1,248.83 rows=1,913 width=0) (actual time=11.091..11.091 rows=0 loops=1)

11. 0.983 0.983 ↑ 1.0 5,309 1

Bitmap Index Scan on entity_has_override_pkey (cost=0.00..157.94 rows=5,309 width=0) (actual time=0.982..0.983 rows=5,309 loops=1)

  • Index Cond: (id_brand = 'AD'::bpchar)
12. 10.071 10.071 ↑ 1.0 51,075 1

Bitmap Index Scan on brand_entity_has_override_entity_type_idx (cost=0.00..1,089.68 rows=51,075 width=0) (actual time=10.071..10.071 rows=51,075 loops=1)

  • Index Cond: (subltree((gid_entity)::ltree, 0, 2) = 'movie.movie'::ltree)
13. 0.042 11.529 ↑ 1.0 1 1

Nested Loop Left Join (cost=0.85..70.76 rows=1 width=1,253) (actual time=11.522..11.529 rows=1 loops=1)

  • Join Filter: ((movie.gid)::ltree = (relevant_brand_override.gid_entity)::ltree)
  • Rows Removed by Join Filter: 150
  • Filter: ((relevant_brand_override.active IS NULL) OR relevant_brand_override.active)
14. 0.002 0.176 ↑ 1.0 1 1

Nested Loop (cost=0.85..8.59 rows=1 width=1,221) (actual time=0.169..0.176 rows=1 loops=1)

15. 0.002 0.113 ↑ 1.0 1 1

Nested Loop (cost=0.43..5.94 rows=1 width=722) (actual time=0.109..0.113 rows=1 loops=1)

16. 0.008 0.008 ↑ 1.0 1 1

Seq Scan on brand (cost=0.00..1.07 rows=1 width=6) (actual time=0.004..0.008 rows=1 loops=1)

  • Filter: (id = 'AD'::bpchar)
  • Rows Removed by Filter: 5
17. 0.103 0.103 ↑ 1.0 1 1

Index Scan using movie_localized_pkey on movie_localized (cost=0.43..2.65 rows=1 width=728) (actual time=0.103..0.103 rows=1 loops=1)

  • Index Cond: ((locale = brand.locale) AND (id = 218395))
  • Filter: (active AND active)
18. 0.061 0.061 ↑ 1.0 1 1

Index Scan using movie_pkey on movie (cost=0.42..2.64 rows=1 width=507) (actual time=0.060..0.061 rows=1 loops=1)

  • Index Cond: (id = 218395)
  • Filter: (active AND active)
19. 11.311 11.311 ↑ 12.8 150 1

CTE Scan on relevant_brand_override (cost=0.00..38.26 rows=1,913 width=65) (actual time=11.112..11.311 rows=150 loops=1)

20. 681.471 681.471 ↓ 1.0 194,032 1

CTE Scan on relevant_country_override (cost=0.00..3,811.46 rows=190,573 width=65) (actual time=0.081..681.471 rows=194,032 loops=1)

Planning time : 12.862 ms
Execution time : 748.683 ms