explain.depesz.com

PostgreSQL's explain analyze made readable

Result: ZbOG

Settings
# exclusive inclusive rows x rows loops node
1. 140.150 58,140.257 ↑ 263.2 1,519 1

WindowAgg (cost=1,355,068.83..1,364,064.10 rows=399,790 width=103) (actual time=57,999.014..58,140.257 rows=1,519 loops=1)

2. 81.961 58,000.107 ↑ 263.2 1,519 1

Sort (cost=1,355,068.83..1,356,068.30 rows=399,790 width=1,366) (actual time=57,998.967..58,000.107 rows=1,519 loops=1)

  • Sort Key: smart_collections.id, perf.store_roi_new DESC
  • Sort Method: quicksort Memory: 3039kB
3. 36.597 57,918.146 ↑ 263.2 1,519 1

Hash Right Join (cost=503,065.73..842,332.60 rows=399,790 width=1,366) (actual time=54,591.683..57,918.146 rows=1,519 loops=1)

  • Hash Cond: (products_1.id = products.id)
4. 1,169.207 56,244.461 ↑ 335.7 46,546 1

Merge Join (cost=488,305.12..764,985.56 rows=15,623,615 width=45) (actual time=52,076.895..56,244.461 rows=46,546 loops=1)

  • Merge Cond: ((_main_collections.store_id = products_1.store_id) AND (smart_collections.id = collects.collection_id))
5. 51.304 2,366.358 ↑ 41.9 12,957 1

Sort (cost=93,997.93..95,356.61 rows=543,471 width=33) (actual time=2,354.821..2,366.358 rows=12,957 loops=1)

  • Sort Key: _main_collections.store_id, smart_collections.id
  • Sort Method: quicksort Memory: 1391kB
6. 126.584 2,315.054 ↑ 41.9 12,957 1

Hash Join (cost=17.50..27,366.31 rows=543,471 width=33) (actual time=4.122..2,315.054 rows=12,957 loops=1)

  • Hash Cond: ((smart_collections.handle)::text = (_main_collections.collection_handle)::text)
7. 54.387 2,186.852 ↑ 1.0 235,504 1

Append (cost=0.00..11,022.04 rows=235,504 width=44) (actual time=0.653..2,186.852 rows=235,504 loops=1)

8. 6.420 6.420 ↑ 1.0 1,900 1

Seq Scan on smart_collections (cost=0.00..130.00 rows=1,900 width=24) (actual time=0.651..6.420 rows=1,900 loops=1)

9. 2,126.045 2,126.045 ↑ 1.0 233,604 1

Seq Scan on custom_collections (cost=0.00..10,892.04 rows=233,604 width=44) (actual time=10.632..2,126.045 rows=233,604 loops=1)

10. 0.174 1.618 ↑ 1.0 600 1

Hash (cost=10.00..10.00 rows=600 width=15) (actual time=1.618..1.618 rows=600 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 36kB
11. 1.444 1.444 ↑ 1.0 600 1

Seq Scan on _main_collections (cost=0.00..10.00 rows=600 width=15) (actual time=0.773..1.444 rows=600 loops=1)

12. 772.871 52,708.896 ↓ 1.7 843,118 1

Materialize (cost=392,957.14..395,495.67 rows=507,706 width=28) (actual time=49,642.429..52,708.896 rows=843,118 loops=1)

13. 7,837.276 51,936.025 ↓ 1.7 843,118 1

Sort (cost=392,957.14..394,226.40 rows=507,706 width=28) (actual time=49,642.424..51,936.025 rows=843,118 loops=1)

  • Sort Key: products_1.store_id, collects.collection_id
  • Sort Method: external merge Disk: 34688kB
14. 2,348.909 44,098.749 ↓ 1.7 845,688 1

Hash Join (cost=306,090.94..332,694.27 rows=507,706 width=28) (actual time=37,059.254..44,098.749 rows=845,688 loops=1)

  • Hash Cond: (collects.product_id = products_1.id)
15. 2,323.279 26,503.857 ↓ 1.5 856,902 1

GroupAggregate (cost=196,121.48..209,917.91 rows=562,170 width=24) (actual time=21,812.578..26,503.857 rows=856,902 loops=1)

  • Group Key: collects.product_id, collects.collection_id
16. 7,785.007 24,180.578 ↓ 1.0 857,083 1

Sort (cost=196,121.48..198,165.16 rows=817,473 width=24) (actual time=21,812.569..24,180.578 rows=857,083 loops=1)

  • Sort Key: collects.product_id, collects.collection_id
  • Sort Method: external merge Disk: 28456kB
17. 16,395.571 16,395.571 ↓ 1.0 857,083 1

Index Scan using collects_active_index on collects (cost=0.42..99,077.31 rows=817,473 width=24) (actual time=3.543..16,395.571 rows=857,083 loops=1)

18. 50.412 15,245.983 ↑ 1.1 51,237 1

Hash (cost=109,294.91..109,294.91 rows=53,964 width=12) (actual time=15,245.983..15,245.983 rows=51,237 loops=1)

  • Buckets: 65536 Batches: 1 Memory Usage: 2714kB
19. 15,195.571 15,195.571 ↑ 1.1 51,237 1

Seq Scan on products products_1 (cost=0.00..109,294.91 rows=53,964 width=12) (actual time=13.498..15,195.571 rows=51,237 loops=1)

  • Filter: ((status)::text = 'active'::text)
  • Rows Removed by Filter: 5567
20. 3.069 1,637.088 ↑ 2.2 703 1

Hash (cost=14,741.49..14,741.49 rows=1,529 width=1,329) (actual time=1,637.088..1,637.088 rows=703 loops=1)

  • Buckets: 2048 Batches: 1 Memory Usage: 1150kB
21. 2.787 1,634.019 ↑ 2.2 703 1

Hash Join (cost=3,956.91..14,741.49 rows=1,529 width=1,329) (actual time=1,223.067..1,634.019 rows=703 loops=1)

  • Hash Cond: (products.id = perf.product_id)
22. 417.223 471.527 ↑ 1.1 2,939 1

Bitmap Heap Scan on products (cost=904.50..11,658.25 rows=3,108 width=1,315) (actual time=55.318..471.527 rows=2,939 loops=1)

  • Recheck Cond: (store_id = 11)
  • Heap Blocks: exact=847
23. 54.304 54.304 ↑ 1.1 2,960 1

Bitmap Index Scan on products_store_id_index (cost=0.00..903.72 rows=3,108 width=0) (actual time=54.304..54.304 rows=2,960 loops=1)

  • Index Cond: (store_id = 11)
24. 17.285 1,159.705 ↑ 1.0 29,396 1

Hash (cost=2,684.96..2,684.96 rows=29,396 width=22) (actual time=1,159.705..1,159.705 rows=29,396 loops=1)

  • Buckets: 32768 Batches: 1 Memory Usage: 1949kB
25. 1,142.420 1,142.420 ↑ 1.0 29,396 1

Seq Scan on _product_performance perf (cost=0.00..2,684.96 rows=29,396 width=22) (actual time=0.918..1,142.420 rows=29,396 loops=1)

Planning time : 102.455 ms
Execution time : 58,197.819 ms