explain.depesz.com

PostgreSQL's explain analyze made readable

Result: KO12 : Optimization for: plan #ucHg

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 0.053 930.618 ↑ 1.0 100 1

Limit (cost=138,717.24..138,717.49 rows=100 width=1,247) (actual time=930.481..930.618 rows=100 loops=1)

2. 47.682 930.565 ↑ 548.8 100 1

Sort (cost=138,717.24..138,854.44 rows=54,879 width=1,247) (actual time=930.479..930.565 rows=100 loops=1)

  • Sort Key: ugc_content.created_at DESC, ugc_content.likes DESC, ugc_dist_content.id_ugc_dist_content
  • Sort Method: top-N heapsort Memory: 377kB
3. 168.366 882.883 ↑ 1.2 46,442 1

WindowAgg (cost=135,110.64..136,619.81 rows=54,879 width=1,247) (actual time=830.512..882.883 rows=46,442 loops=1)

4. 37.975 714.517 ↑ 1.2 46,442 1

Group (cost=135,110.64..135,933.82 rows=54,879 width=1,114) (actual time=633.137..714.517 rows=46,442 loops=1)

  • Group Key: ugc_dist_content.id_ugc_dist_content, ugc_content.id_ugc_content, ugc_delete_recycle_bin.username, ugc_delete_recycle_bin.deleted_at, (CASE WHEN (ugc_custom_tag_hierarchy.id_ugc_custom_tag_hierarchy IS NULL) THEN 0 ELSE 1 END)
5. 198.987 676.542 ↑ 1.2 46,444 1

Sort (cost=135,110.64..135,247.83 rows=54,879 width=1,114) (actual time=633.134..676.542 rows=46,444 loops=1)

  • Sort Key: ugc_dist_content.id_ugc_dist_content, ugc_content.id_ugc_content, ugc_delete_recycle_bin.username, ugc_delete_recycle_bin.deleted_at, (CASE WHEN (ugc_custom_tag_hierarchy.id_ugc_custom_tag_hierarchy IS NULL) THEN 0 ELSE 1 END)
  • Sort Method: external merge Disk: 51,608kB
6. 28.011 477.555 ↑ 1.2 46,444 1

Hash Left Join (cost=5,122.74..77,142.57 rows=54,879 width=1,114) (actual time=78.959..477.555 rows=46,444 loops=1)

  • Hash Cond: (ugc_dist_content.id_ugc_dist_content = ugc_delete_recycle_bin.id_ugc_dist_content)
7. 24.318 449.452 ↑ 1.2 46,440 1

Hash Left Join (cost=5,117.99..76,657.20 rows=54,879 width=1,094) (actual time=78.852..449.452 rows=46,440 loops=1)

  • Hash Cond: (ugc_dist_content.id_ugc_dist_content = ugc_custom_tag_hierarchy.id_ugc_dist_content)
8. 26.338 425.061 ↑ 1.2 46,440 1

Hash Left Join (cost=5,102.26..76,092.62 rows=54,879 width=1,090) (actual time=78.768..425.061 rows=46,440 loops=1)

  • Hash Cond: (ugc_dist_content.id_ugc_dist_content = rel_ugc_dist_content_dist_custom_tag.id_ugc_dist_content)
9. 114.457 398.272 ↑ 1.2 46,438 1

Hash Join (cost=5,077.25..75,651.96 rows=54,879 width=1,090) (actual time=78.297..398.272 rows=46,438 loops=1)

  • Hash Cond: (ugc_content.id_ugc_content = ugc_dist_content.id_ugc_content)
10. 62.016 206.588 ↑ 2.4 46,438 1

Hash Join (cost=82.49..39,044.75 rows=111,909 width=1,068) (actual time=0.523..206.588 rows=46,438 loops=1)

  • Hash Cond: (ugc_content.id_ugc_board = ugc_board.id_ugc_board)
11. 144.091 144.091 ↑ 2.0 102,979 1

Seq Scan on ugc_content (cost=0.00..37,062.58 rows=208,158 width=1,072) (actual time=0.028..144.091 rows=102,979 loops=1)

12. 0.122 0.481 ↑ 2.1 393 1

Hash (cost=72.13..72.13 rows=829 width=4) (actual time=0.481..0.481 rows=393 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 22kB
13. 0.359 0.359 ↑ 2.1 393 1

Seq Scan on ugc_board (cost=0.00..72.13 rows=829 width=4) (actual time=0.020..0.359 rows=393 loops=1)

  • Filter: ((id_ugc_brand = 1) AND (id_ugc_region = 1))
  • Rows Removed by Filter: 418
14. 40.190 77.227 ↓ 1.0 102,979 1

Hash (cost=3,020.78..3,020.78 rows=102,078 width=26) (actual time=77.227..77.227 rows=102,979 loops=1)

  • Buckets: 65,536 Batches: 2 Memory Usage: 2,950kB
15. 37.037 37.037 ↓ 1.0 102,979 1

Seq Scan on ugc_dist_content (cost=0.00..3,020.78 rows=102,078 width=26) (actual time=0.013..37.037 rows=102,979 loops=1)

16. 0.223 0.451 ↑ 1.0 755 1

Hash (cost=15.56..15.56 rows=756 width=8) (actual time=0.450..0.451 rows=755 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 38kB
17. 0.228 0.228 ↑ 1.0 755 1

Seq Scan on rel_ugc_dist_content_dist_custom_tag (cost=0.00..15.56 rows=756 width=8) (actual time=0.011..0.228 rows=755 loops=1)

18. 0.023 0.073 ↑ 6.4 12 1

Hash (cost=14.77..14.77 rows=77 width=8) (actual time=0.072..0.073 rows=12 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
19. 0.050 0.050 ↑ 1.0 77 1

Seq Scan on ugc_custom_tag_hierarchy (cost=0.00..14.77 rows=77 width=8) (actual time=0.007..0.050 rows=77 loops=1)

20. 0.042 0.092 ↓ 1.2 92 1

Hash (cost=3.78..3.78 rows=78 width=24) (actual time=0.092..0.092 rows=92 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 14kB
21. 0.050 0.050 ↓ 1.2 92 1

Seq Scan on ugc_delete_recycle_bin (cost=0.00..3.78 rows=78 width=24) (actual time=0.014..0.050 rows=92 loops=1)

Planning time : 2.816 ms
Execution time : 939.907 ms