explain.depesz.com

PostgreSQL's explain analyze made readable

Result: ND5t

Settings
# exclusive inclusive rows x rows loops node
1. 0.014 41,535.475 ↓ 3.0 3 1

Limit (cost=869.17..869.20 rows=1 width=251) (actual time=41,535.461..41,535.475 rows=3 loops=1)

2.          

CTE perm

3. 0.400 8.191 ↑ 1.9 37 1

Sort (cost=629.15..629.32 rows=70 width=1,082) (actual time=8.011..8.191 rows=37 loops=1)

  • Sort Key: pr.dsm_point DESC NULLS LAST, pr.dg_name, sp.name
  • Sort Method: quicksort Memory: 31kB
4. 0.095 7.791 ↑ 1.9 37 1

Nested Loop Left Join (cost=0.84..627.00 rows=70 width=1,082) (actual time=5.416..7.791 rows=37 loops=1)

5. 0.139 7.363 ↑ 1.9 37 1

Nested Loop Left Join (cost=0.42..593.33 rows=70 width=1,060) (actual time=5.311..7.363 rows=37 loops=1)

6. 5.004 5.004 ↑ 1.9 37 1

Seq Scan on cus_datasource_0000000185_0000000000000000050 pr (cost=0.00..10.70 rows=70 width=1,056) (actual time=4.983..5.004 rows=37 loops=1)

7. 2.220 2.220 ↑ 1.0 1 37

Index Scan using bi_tag_pkey on bi_tag (cost=0.42..8.32 rows=1 width=8) (actual time=0.060..0.060 rows=1 loops=37)

  • Index Cond: (id = pr.tag_id)
8. 0.333 0.333 ↑ 1.0 1 37

Index Scan using bi_brand_pkey on bi_brand sp (cost=0.42..0.48 rows=1 width=22) (actual time=0.009..0.009 rows=1 loops=37)

  • Index Cond: (bi_tag.brand = id)
9.          

CTE clip_with_media

10. 2.149 41,535.324 ↓ 3.0 3 1

GroupAggregate (cost=223.66..239.85 rows=1 width=247) (actual time=41,535.215..41,535.324 rows=3 loops=1)

  • Group Key: c_1.id
11. 0.079 41,533.175 ↓ 3.0 3 1

Nested Loop (cost=223.66..239.76 rows=1 width=1,175) (actual time=41,533.090..41,533.175 rows=3 loops=1)

12. 0.086 41,532.790 ↓ 3.0 3 1

Nested Loop Left Join (cost=223.38..231.45 rows=1 width=1,175) (actual time=41,532.732..41,532.790 rows=3 loops=1)

13. 0.105 41,531.732 ↓ 3.0 3 1

Merge Left Join (cost=223.10..223.13 rows=1 width=1,161) (actual time=41,531.722..41,531.732 rows=3 loops=1)

  • Merge Cond: (c_1.id = cctrl.clip_table_id)
14. 0.108 41,521.081 ↓ 3.0 3 1

Sort (cost=221.98..221.99 rows=1 width=1,160) (actual time=41,521.078..41,521.081 rows=3 loops=1)

  • Sort Key: c_1.id
  • Sort Method: quicksort Memory: 25kB
15. 913.333 41,520.973 ↓ 3.0 3 1

Nested Loop (cost=100.00..221.97 rows=1 width=1,160) (actual time=919.918..41,520.973 rows=3 loops=1)

  • Join Filter: ((t.tag = perm.tag_id) AND (((perm.dpl_news = 1) AND (c_1.type = 1)) OR ((perm.dpl_ads = 1) AND (c_1.type = 2))))
  • Rows Removed by Join Filter: 2048872
16. 8.724 8.724 ↓ 37.0 37 1

CTE Scan on perm (cost=0.00..1.75 rows=1 width=12) (actual time=8.022..8.724 rows=37 loops=1)

  • Filter: ((dpl_news = 1) OR (dpl_ads = 1))
17. 40,598.916 40,598.916 ↓ 4,259.6 55,375 37

Foreign Scan (cost=100.00..219.93 rows=13 width=1,164) (actual time=106.384..1,097.268 rows=55,375 loops=37)

  • Relations: ((dbarchive.bc_dataclip_201905 c) INNER JOIN (dbarchive.bc_datatag_201905 t)) LEFT JOIN (dbarchive.bc_dataclip_ignore_201905 ign)
18. 0.158 10.546 ↓ 0.0 0 1

Sort (cost=1.12..1.13 rows=1 width=5) (actual time=10.546..10.546 rows=0 loops=1)

  • Sort Key: cctrl.clip_table_id
  • Sort Method: quicksort Memory: 25kB
19. 10.388 10.388 ↓ 0.0 0 1

Seq Scan on bc_dataclip_control cctrl (cost=0.00..1.11 rows=1 width=5) (actual time=10.388..10.388 rows=0 loops=1)

  • Filter: (clip_table_date = 201905)
  • Rows Removed by Filter: 14
20. 0.972 0.972 ↓ 0.0 0 3

Index Scan using emp_user_pkey on emp_user empcuser (cost=0.28..8.29 rows=1 width=22) (actual time=0.324..0.324 rows=0 loops=3)

  • Index Cond: (id = c_1.cuser)
21. 0.306 0.306 ↑ 1.0 1 3

Index Scan using bc_media_pkey on bc_media med (cost=0.28..8.30 rows=1 width=4) (actual time=0.102..0.102 rows=1 loops=3)

  • Index Cond: (id = c_1.media)
  • Filter: (type = ANY ('{1,12,11}'::integer[]))
22. 0.081 41,535.461 ↓ 3.0 3 1

WindowAgg (cost=0.00..0.03 rows=1 width=251) (actual time=41,535.449..41,535.461 rows=3 loops=1)

23. 41,535.380 41,535.380 ↓ 3.0 3 1

CTE Scan on clip_with_media c (cost=0.00..0.02 rows=1 width=243) (actual time=41,535.257..41,535.380 rows=3 loops=1)

Planning time : 51.521 ms
Execution time : 41,557.825 ms