explain.depesz.com

PostgreSQL's explain analyze made readable

Result: zOp

Settings
# exclusive inclusive rows x rows loops node
1. 0.035 14,793.722 ↑ 1.0 50 1

Limit (cost=305,247.20..305,247.32 rows=50 width=508) (actual time=14,793.658..14,793.722 rows=50 loops=1)

2.          

CTE isrcs_with_unsigned_composers

3. 379.922 9,028.987 ↓ 1.8 305,454 1

Unique (cost=115,155.39..118,583.18 rows=169,352 width=13) (actual time=6,613.005..9,028.987 rows=305,454 loops=1)

4. 7,330.656 8,649.065 ↓ 1.1 736,615 1

Sort (cost=115,155.39..116,869.28 rows=685,558 width=13) (actual time=6,613.003..8,649.065 rows=736,615 loops=1)

  • Sort Key: composer_isrcs.isrc
  • Sort Method: external merge Disk: 16536kB
5. 851.549 1,318.409 ↓ 1.1 736,615 1

Hash Join (cost=8,586.98..36,983.10 rows=685,558 width=13) (actual time=138.359..1,318.409 rows=736,615 loops=1)

  • Hash Cond: (composer_isrcs.composer_id = composers.id)
6. 328.756 328.756 ↓ 1.1 775,213 1

Seq Scan on composer_isrcs (cost=0.00..14,100.58 rows=685,558 width=21) (actual time=0.005..328.756 rows=775,213 loops=1)

  • Filter: current
7. 69.219 138.104 ↑ 1.2 175,841 1

Hash (cost=4,987.99..4,987.99 rows=219,359 width=8) (actual time=138.104..138.104 rows=175,841 loops=1)

  • Buckets: 262144 Batches: 2 Memory Usage: 5496kB
8. 68.885 68.885 ↑ 1.2 175,841 1

Seq Scan on composers (cost=0.00..4,987.99 rows=219,359 width=8) (actual time=0.007..68.885 rows=175,841 loops=1)

  • Filter: ((published_status = ANY ('{0,1}'::integer[])) OR (published_status IS NULL))
  • Rows Removed by Filter: 317
9.          

CTE isrcs_with_composers

10. 423.599 1,119.589 ↓ 1.8 309,498 1

Unique (cost=0.42..61,327.08 rows=169,352 width=13) (actual time=0.041..1,119.589 rows=309,498 loops=1)

11. 695.990 695.990 ↓ 1.1 775,213 1

Index Only Scan using index_composer_isrcs_on_isrc_and_composer_id on composer_isrcs composer_isrcs_1 (cost=0.42..59,613.18 rows=685,558 width=13) (actual time=0.040..695.990 rows=775,213 loops=1)

  • Heap Fetches: 775884
12. 0.258 14,793.687 ↑ 40,318.9 50 1

Sort (cost=125,336.95..130,376.81 rows=2,015,944 width=508) (actual time=14,793.655..14,793.687 rows=50 loops=1)

  • Sort Key: publisher_discovery_listings.position_this_week, publisher_discovery_listings.id
  • Sort Method: quicksort Memory: 121kB
13. 195.061 14,793.429 ↑ 20,159.4 100 1

Merge Left Join (cost=27,667.97..58,368.74 rows=2,015,944 width=508) (actual time=14,379.760..14,793.429 rows=100 loops=1)

  • Merge Cond: ((publisher_discovery_listings.expected_isrc)::text = (isrcs_with_composers.isrc)::text)
14. 0.486 9,378.976 ↑ 76.5 100 1

Sort (cost=5,519.99..5,539.10 rows=7,646 width=539) (actual time=9,378.893..9,378.976 rows=100 loops=1)

  • Sort Key: publisher_discovery_listings.expected_isrc
  • Sort Method: quicksort Memory: 122kB
15. 116.897 9,378.490 ↑ 76.5 100 1

Hash Right Join (cost=293.16..5,026.80 rows=7,646 width=539) (actual time=7,327.998..9,378.490 rows=100 loops=1)

  • Hash Cond: ((isrcs_with_unsigned_composers.isrc)::text = (publisher_discovery_listings.expected_isrc)::text)
16. 9,260.894 9,260.894 ↓ 1.8 305,454 1

CTE Scan on isrcs_with_unsigned_composers (cost=0.00..3,387.04 rows=169,352 width=32) (actual time=6,613.008..9,260.894 rows=305,454 loops=1)

17. 0.156 0.699 ↓ 3.4 100 1

Hash (cost=292.80..292.80 rows=29 width=507) (actual time=0.699..0.699 rows=100 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 63kB
18. 0.259 0.543 ↓ 3.4 100 1

Nested Loop Left Join (cost=0.56..292.80 rows=29 width=507) (actual time=0.024..0.543 rows=100 loops=1)

19. 0.084 0.084 ↓ 3.4 100 1

Index Scan using ix_publisher_discovery_listings_on_chart_type_source_position on publisher_discovery_listings (cost=0.28..67.52 rows=29 width=506) (actual time=0.012..0.084 rows=100 loops=1)

  • Index Cond: ((chart_type = 0) AND (chart_source_id = 1))
20. 0.200 0.200 ↓ 0.0 0 100

Index Scan using index_trackings_on_user_id_and_artist_id on trackings (cost=0.29..7.76 rows=1 width=5) (actual time=0.002..0.002 rows=0 loops=100)

  • Index Cond: ((user_id = 1) AND (artist_id = publisher_discovery_listings.artist_id))
  • Filter: active
  • Rows Removed by Filter: 0
21. 187.530 5,219.392 ↓ 1.8 309,382 1

Materialize (cost=22,147.98..22,994.74 rows=169,352 width=32) (actual time=4,907.955..5,219.392 rows=309,382 loops=1)

22. 3,621.430 5,031.862 ↓ 1.8 309,382 1

Sort (cost=22,147.98..22,571.36 rows=169,352 width=32) (actual time=4,907.951..5,031.862 rows=309,382 loops=1)

  • Sort Key: isrcs_with_composers.isrc
  • Sort Method: external sort Disk: 6952kB
23. 1,410.432 1,410.432 ↓ 1.8 309,498 1

CTE Scan on isrcs_with_composers (cost=0.00..3,387.04 rows=169,352 width=32) (actual time=0.043..1,410.432 rows=309,498 loops=1)

Planning time : 0.837 ms
Execution time : 14,804.962 ms