explain.depesz.com

PostgreSQL's explain analyze made readable

Result: JUjg

Settings
# exclusive inclusive rows x rows loops node
1. 172.335 61,616.749 ↑ 272.1 1,519 1

WindowAgg (cost=1,371,947.63..1,381,245.67 rows=413,246 width=103) (actual time=61,424.495..61,616.749 rows=1,519 loops=1)

2. 72.523 61,444.414 ↑ 272.1 1,519 1

Sort (cost=1,371,947.63..1,372,980.75 rows=413,246 width=1,366) (actual time=61,424.464..61,444.414 rows=1,519 loops=1)

  • Sort Key: smart_collections.id, perf.store_roi_new DESC
  • Sort Method: quicksort Memory: 3039kB
3. 49.049 61,371.891 ↑ 272.1 1,519 1

Hash Right Join (cost=502,460.07..841,858.72 rows=413,246 width=1,366) (actual time=56,908.000..61,371.891 rows=1,519 loops=1)

  • Hash Cond: (products_1.id = products.id)
4. 1,283.207 59,866.093 ↑ 335.7 46,546 1

Merge Join (cost=488,258.43..764,936.70 rows=15,623,492 width=45) (actual time=54,597.046..59,866.093 rows=46,546 loops=1)

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

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

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

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

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

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

8. 76.993 76.993 ↑ 1.0 1,900 1

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

9. 1,984.798 1,984.798 ↑ 1.0 233,604 1

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

10. 0.393 14.881 ↑ 1.0 600 1

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

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

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

12. 883.355 56,209.332 ↓ 1.7 843,118 1

Materialize (cost=392,910.44..395,448.95 rows=507,702 width=28) (actual time=52,253.451..56,209.332 rows=843,118 loops=1)

13. 9,970.442 55,325.977 ↓ 1.7 843,118 1

Sort (cost=392,910.44..394,179.69 rows=507,702 width=28) (actual time=52,253.447..55,325.977 rows=843,118 loops=1)

  • Sort Key: products_1.store_id, collects.collection_id
  • Sort Method: external merge Disk: 34688kB
14. 1,707.489 45,355.535 ↓ 1.7 845,688 1

Hash Join (cost=306,044.69..332,647.98 rows=507,702 width=28) (actual time=38,756.195..45,355.535 rows=845,688 loops=1)

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

GroupAggregate (cost=196,121.48..209,917.91 rows=562,170 width=24) (actual time=22,032.470..26,924.433 rows=856,902 loops=1)

  • Group Key: collects.product_id, collects.collection_id
16. 8,526.097 24,242.424 ↓ 1.0 857,083 1

Sort (cost=196,121.48..198,165.16 rows=817,473 width=24) (actual time=22,032.460..24,242.424 rows=857,083 loops=1)

  • Sort Key: collects.product_id, collects.collection_id
  • Sort Method: external merge Disk: 28456kB
17. 15,716.327 15,716.327 ↓ 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=0.802..15,716.327 rows=857,083 loops=1)

18. 44.656 16,723.613 ↑ 1.0 51,237 1

Hash (cost=109,270.61..109,270.61 rows=52,208 width=12) (actual time=16,723.613..16,723.613 rows=51,237 loops=1)

  • Buckets: 65536 Batches: 1 Memory Usage: 2714kB
19. 16,678.957 16,678.957 ↑ 1.0 51,237 1

Seq Scan on products products_1 (cost=0.00..109,270.61 rows=52,208 width=12) (actual time=1.268..16,678.957 rows=51,237 loops=1)

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

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

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

Hash Join (cost=11,372.05..14,182.53 rows=1,529 width=1,329) (actual time=748.043..1,455.546 rows=703 loops=1)

  • Hash Cond: (perf.product_id = products.id)
22. 734.973 734.973 ↑ 1.0 29,396 1

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

23. 5.184 686.766 ↑ 1.0 2,939 1

Hash (cost=11,334.46..11,334.46 rows=3,007 width=1,315) (actual time=686.766..686.766 rows=2,939 loops=1)

  • Buckets: 4096 Batches: 1 Memory Usage: 3979kB
24. 633.709 681.582 ↑ 1.0 2,939 1

Bitmap Heap Scan on products (cost=903.72..11,334.46 rows=3,007 width=1,315) (actual time=50.879..681.582 rows=2,939 loops=1)

  • Recheck Cond: (store_id = 11)
  • Heap Blocks: exact=839
25. 47.873 47.873 ↑ 1.0 2,939 1

Bitmap Index Scan on products_store_id_index (cost=0.00..902.97 rows=3,007 width=0) (actual time=47.873..47.873 rows=2,939 loops=1)

  • Index Cond: (store_id = 11)