explain.depesz.com

PostgreSQL's explain analyze made readable

Result: rWNX

Settings
# exclusive inclusive rows x rows loops node
1. 0.002 179,632.333 ↓ 0.0 0 1

GroupAggregate (cost=942.70..942.77 rows=1 width=176) (actual time=179,632.333..179,632.333 rows=0 loops=1)

  • Group Key: directives.id
2. 0.018 179,632.331 ↓ 0.0 0 1

Sort (cost=942.70..942.71 rows=1 width=56) (actual time=179,632.330..179,632.331 rows=0 loops=1)

  • Sort Key: directives.id
  • Sort Method: quicksort Memory: 25kB
3. 12.382 179,632.313 ↓ 0.0 0 1

Nested Loop (cost=4.38..942.69 rows=1 width=56) (actual time=179,632.313..179,632.313 rows=0 loops=1)

  • Join Filter: (NOT (SubPlan 1))
  • Rows Removed by Join Filter: 1827
4. 3.223 5.734 ↓ 1,827.0 1,827 1

Nested Loop (cost=4.24..72.58 rows=1 width=60) (actual time=0.243..5.734 rows=1,827 loops=1)

5. 0.028 0.216 ↓ 9.0 9 1

Nested Loop (cost=0.43..60.34 rows=1 width=56) (actual time=0.127..0.216 rows=9 loops=1)

6. 0.052 0.052 ↓ 1.1 8 1

Seq Scan on directives (cost=0.00..1.14 rows=7 width=52) (actual time=0.039..0.052 rows=8 loops=1)

  • Filter: (NOT soft_deleted)
  • Rows Removed by Filter: 9
7. 0.136 0.136 ↑ 1.0 1 8

Index Only Scan using idx_uniq_links_media_types on links_media_types dir_mtl (cost=0.43..8.45 rows=1 width=8) (actual time=0.016..0.017 rows=1 loops=8)

  • Index Cond: ((media_type_linked_id = directives.id) AND (media_type_linked_type = 'Directive'::text))
  • Heap Fetches: 9
8. 2.097 2.295 ↓ 14.5 203 9

Bitmap Heap Scan on territories_countries dir_tc (cost=3.82..12.09 rows=14 width=8) (actual time=0.032..0.255 rows=203 loops=9)

  • Recheck Cond: (territory_id = directives.license_origin_id)
  • Heap Blocks: exact=144
9. 0.198 0.198 ↓ 14.5 203 9

Bitmap Index Scan on index_territories_countries_on_territory_id_and_country_id (cost=0.00..3.81 rows=14 width=0) (actual time=0.022..0.022 rows=203 loops=9)

  • Index Cond: (territory_id = directives.license_origin_id)
10. 9.135 9.135 ↑ 1.0 1 1,827

Index Only Scan using idx_media_types_with_contained_mt_cmt on media_types_with_contained_media_types dir_cmt (cost=0.13..8.15 rows=1 width=8) (actual time=0.003..0.005 rows=1 loops=1,827)

  • Index Cond: (media_type_id = dir_mtl.media_type_id)
  • Heap Fetches: 1827
11.          

SubPlan (for Nested Loop)

12. 18.270 179,605.062 ↑ 17.0 1 1,827

Nested Loop (cost=5.26..14,569.13 rows=17 width=0) (actual time=98.306..98.306 rows=1 loops=1,827)

13. 12.789 179,550.252 ↑ 1.0 1 1,827

Nested Loop (cost=0.85..14,547.51 rows=1 width=0) (actual time=98.276..98.276 rows=1 loops=1,827)

14. 179,451.594 179,451.594 ↑ 1.0 1 1,827

Index Scan using idx_not_disabled_deals on deals d (cost=0.42..14,537.98 rows=1 width=4) (actual time=98.222..98.222 rows=1 loops=1,827)

  • Filter: (((publishing_id = directives.publishing_id) OR ((publishing_id IS NULL) AND (directives.publishing_id IS NULL))) AND ((label_recording_id = directives.label_recording_id) OR ((label_recording_id IS NULL) AND (directives.label_recording_id IS NULL))) AND (dir_tc.territory_id = share_territory_id) AND ((dealable_type)::text = (directives.dealable_type)::text) AND (dealable_id = directives.dealable_id))
  • Rows Removed by Filter: 127587
15. 45.675 85.869 ↑ 1.0 1 1,827

Nested Loop (cost=0.43..9.52 rows=1 width=4) (actual time=0.047..0.047 rows=1 loops=1,827)

16. 7.308 7.308 ↑ 1.0 1 1,827

Seq Scan on media_types_with_contained_media_types d_cmt (cost=0.00..1.06 rows=1 width=4) (actual time=0.004..0.004 rows=1 loops=1,827)

  • Filter: (contained_media_type_id = dir_cmt.contained_media_type_id)
  • Rows Removed by Filter: 0
17. 32.886 32.886 ↑ 1.0 1 1,827

Index Only Scan using idx_uniq_links_media_types on links_media_types d_mtl (cost=0.43..8.45 rows=1 width=8) (actual time=0.018..0.018 rows=1 loops=1,827)

  • Index Cond: ((media_type_linked_id = d.id) AND (media_type_linked_type = 'Deal'::text) AND (media_type_id = d_cmt.media_type_id))
  • Heap Fetches: 1827
18. 16.443 36.540 ↑ 17.0 1 1,827

Bitmap Heap Scan on territories_countries d_tc (cost=4.41..21.45 rows=17 width=0) (actual time=0.020..0.020 rows=1 loops=1,827)

  • Recheck Cond: (country_id = dir_tc.country_id)
  • Heap Blocks: exact=1827
19. 20.097 20.097 ↑ 1.0 17 1,827

Bitmap Index Scan on index_territories_countries_on_country_id_and_territory_id (cost=0.00..4.41 rows=17 width=0) (actual time=0.011..0.011 rows=17 loops=1,827)

  • Index Cond: (country_id = dir_tc.country_id)
Planning time : 2.746 ms
Execution time : 179,632.852 ms