explain.depesz.com

PostgreSQL's explain analyze made readable

Result: SMeF

Settings
# exclusive inclusive rows x rows loops node
1. 0.002 36,427.490 ↓ 20.0 20 1

Limit (cost=21,109.11..21,109.12 rows=1 width=170) (actual time=36,427.488..36,427.490 rows=20 loops=1)

2.          

CTE imageDivDept

3. 0.853 311.438 ↓ 3.2 14,726 1

Result (cost=18,691.62..18,780.66 rows=4,546 width=13) (actual time=307.845..311.438 rows=14,726 loops=1)

4. 0.556 310.585 ↓ 3.2 14,726 1

Append (cost=18,691.62..18,780.66 rows=4,546 width=13) (actual time=307.844..310.585 rows=14,726 loops=1)

5. 9.638 309.993 ↓ 3.5 14,219 1

HashAggregate (cost=18,691.62..18,732.20 rows=4,058 width=13) (actual time=307.843..309.993 rows=14,219 loops=1)

6. 203.767 300.355 ↓ 5.0 20,198 1

Hash Join (cost=10,608.89..18,661.19 rows=4,058 width=13) (actual time=94.917..300.355 rows=20,198 loops=1)

  • Hash Cond: (((t_ipadapp_image_to_item_no.item_no)::text = (m_item_no.item_no)::text) AND ((t_ipadapp_image_to_item_no.item_category_code)::text = (m_item_no.item_category_code)::text) AND ((t_ipadapp_image_to_item_no.month)::text = (m_item_no.month)::text))
7. 2.152 2.152 ↓ 1.0 20,509 1

Seq Scan on t_ipadapp_image_to_item_no (cost=0.00..336.08 rows=20,508 width=19) (actual time=0.004..2.152 rows=20,509 loops=1)

8. 36.906 94.436 ↑ 1.0 136,214 1

Hash (cost=7,559.14..7,559.14 rows=136,214 width=16) (actual time=94.436..94.436 rows=136,214 loops=1)

  • Buckets: 4096 Batches: 8 Memory Usage: 823kB
9. 57.530 57.530 ↑ 1.0 136,214 1

Seq Scan on m_item_no (cost=0.00..7,559.14 rows=136,214 width=16) (actual time=0.019..57.530 rows=136,214 loops=1)

10. 0.036 0.036 ↓ 1.0 507 1

Seq Scan on t_ipadapp_image_not_item_no (cost=0.00..7.88 rows=488 width=12) (actual time=0.008..0.036 rows=507 loops=1)

11.          

CTE rankedInfo

12. 14.494 48.069 ↓ 1.0 14,956 1

WindowAgg (cost=1,323.27..1,733.48 rows=14,917 width=40) (actual time=26.360..48.069 rows=14,956 loops=1)

13. 26.606 33.575 ↓ 1.0 14,956 1

Sort (cost=1,323.27..1,360.56 rows=14,917 width=40) (actual time=26.353..33.575 rows=14,956 loops=1)

  • Sort Key: (date(t_map_image_update_info.entry_date)), t_map_image_update_info.image_id, t_map_image_update_info.update_type, t_map_image_update_info.entry_date
  • Sort Method: external sort Disk: 936kB
14. 6.969 6.969 ↓ 1.0 14,956 1

Seq Scan on t_map_image_update_info (cost=0.00..289.17 rows=14,917 width=40) (actual time=0.020..6.969 rows=14,956 loops=1)

15. 11.618 36,427.488 ↓ 20.0 20 1

Sort (cost=594.97..594.98 rows=1 width=170) (actual time=36,427.487..36,427.488 rows=20 loops=1)

  • Sort Key: ""rankedInfo"".entry_date, t_ipadapp_image.id
  • Sort Method: top-N heapsort Memory: 27kB
16. 12.922 36,415.870 ↓ 14,706.0 14,706 1

Nested Loop (cost=0.00..594.96 rows=1 width=170) (actual time=342.884..36,415.870 rows=14,706 loops=1)

17. 11,065.047 36,373.536 ↓ 14,706.0 14,706 1

Nested Loop (cost=0.00..587.38 rows=1 width=154) (actual time=342.871..36,373.536 rows=14,706 loops=1)

  • Join Filter: ("imageDivDept".image_id = "rankedInfo".image_id)
  • Rows Removed by Join Filter: 219653036
18. 68.925 68.925 ↓ 14,917.0 14,917 1

CTE Scan on "rankedInfo" (cost=0.00..484.80 rows=1 width=134) (actual time=26.366..68.925 rows=14,917 loops=1)

  • Filter: ((rn = 1) AND (date(entry_date) >= '2019-09-01'::date) AND (date(entry_date) <= '2019-09-30'::date))
  • Rows Removed by Filter: 39
19. 25,239.564 25,239.564 ↓ 640.3 14,726 14,917

CTE Scan on "imageDivDept" (cost=0.00..102.29 rows=23 width=20) (actual time=0.021..1.692 rows=14,726 loops=14,917)

  • Filter: ((div_code)::text = '01'::text)
20. 29.412 29.412 ↑ 1.0 1 14,706

Index Scan using t_ipadapp_image_1 on t_ipadapp_image (cost=0.00..7.58 rows=1 width=32) (actual time=0.002..0.002 rows=1 loops=14,706)

  • Index Cond: (id = "imageDivDept".image_id)