explain.depesz.com

PostgreSQL's explain analyze made readable

Result: WJpF

Settings
# exclusive inclusive rows x rows loops node
1. 45.067 46,104.990 ↑ 263.2 1,519 1

WindowAgg (cost=1,338,397.37..1,347,392.65 rows=399,790 width=103) (actual time=46,047.996..46,104.990 rows=1,519 loops=1)

2. 61.684 46,059.923 ↑ 263.2 1,519 1

Sort (cost=1,338,397.37..1,339,396.85 rows=399,790 width=1,366) (actual time=46,047.955..46,059.923 rows=1,519 loops=1)

  • Sort Key: smart_collections.id, perf.store_roi_new DESC
  • Sort Method: quicksort Memory: 3039kB
3. 89.100 45,998.239 ↑ 263.2 1,519 1

Hash Right Join (cost=486,394.27..825,661.14 rows=399,790 width=1,366) (actual time=41,657.662..45,998.239 rows=1,519 loops=1)

  • Hash Cond: (products_1.id = products.id)
4. 1,340.227 45,008.997 ↑ 335.7 46,546 1

Merge Join (cost=471,633.66..748,314.10 rows=15,623,615 width=45) (actual time=40,315.334..45,008.997 rows=46,546 loops=1)

  • Merge Cond: ((_main_collections.store_id = products_1.store_id) AND (smart_collections.id = collects.collection_id))
5. 82.123 3,308.112 ↑ 41.9 12,957 1

Sort (cost=93,997.93..95,356.61 rows=543,471 width=33) (actual time=3,281.527..3,308.112 rows=12,957 loops=1)

  • Sort Key: _main_collections.store_id, smart_collections.id
  • Sort Method: quicksort Memory: 1391kB
6. 141.315 3,225.989 ↑ 41.9 12,957 1

Hash Join (cost=17.50..27,366.31 rows=543,471 width=33) (actual time=14.639..3,225.989 rows=12,957 loops=1)

  • Hash Cond: ((smart_collections.handle)::text = (_main_collections.collection_handle)::text)
7. 33.881 3,078.694 ↑ 1.0 235,504 1

Append (cost=0.00..11,022.04 rows=235,504 width=44) (actual time=6.603..3,078.694 rows=235,504 loops=1)

8. 54.713 54.713 ↑ 1.0 1,900 1

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

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

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

10. 0.226 5.980 ↑ 1.0 600 1

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

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

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

12. 774.097 40,360.658 ↓ 1.7 843,118 1

Materialize (cost=376,285.68..378,824.21 rows=507,706 width=28) (actual time=37,004.173..40,360.658 rows=843,118 loops=1)

13. 9,303.609 39,586.561 ↓ 1.7 843,118 1

Sort (cost=376,285.68..377,554.94 rows=507,706 width=28) (actual time=37,003.898..39,586.561 rows=843,118 loops=1)

  • Sort Key: products_1.store_id, collects.collection_id
  • Sort Method: external merge Disk: 34688kB
14. 2,136.910 30,282.952 ↓ 1.7 845,688 1

Hash Join (cost=289,419.48..316,022.81 rows=507,706 width=28) (actual time=22,252.503..30,282.952 rows=845,688 loops=1)

  • Hash Cond: (collects.product_id = products_1.id)
15. 2,852.410 27,415.662 ↓ 1.5 856,902 1

GroupAggregate (cost=196,121.48..209,917.91 rows=562,170 width=24) (actual time=21,522.009..27,415.662 rows=856,902 loops=1)

  • Group Key: collects.product_id, collects.collection_id
16. 9,512.861 24,563.252 ↓ 1.0 857,083 1

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

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

18. 91.332 730.380 ↑ 1.1 51,237 1

Hash (cost=92,623.45..92,623.45 rows=53,964 width=12) (actual time=730.380..730.380 rows=51,237 loops=1)

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

Bitmap Heap Scan on products products_1 (cost=883.60..92,623.45 rows=53,964 width=12) (actual time=150.165..639.048 rows=51,237 loops=1)

  • Recheck Cond: ((status)::text = 'active'::text)
  • Heap Blocks: exact=16526
20. 110.877 110.877 ↓ 1.0 54,443 1

Bitmap Index Scan on products_active_index (cost=0.00..870.11 rows=53,964 width=0) (actual time=110.877..110.877 rows=54,443 loops=1)

21. 19.457 900.142 ↑ 2.2 703 1

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

  • Buckets: 2048 Batches: 1 Memory Usage: 1150kB
22. 1.474 880.685 ↑ 2.2 703 1

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

  • Hash Cond: (products.id = perf.product_id)
23. 5.566 46.994 ↑ 1.1 2,939 1

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

  • Recheck Cond: (store_id = 11)
  • Heap Blocks: exact=854
24. 41.428 41.428 ↑ 1.0 2,980 1

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

  • Index Cond: (store_id = 11)
25. 20.077 832.217 ↑ 1.0 29,396 1

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

  • Buckets: 32768 Batches: 1 Memory Usage: 1949kB
26. 812.140 812.140 ↑ 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.979..812.140 rows=29,396 loops=1)

Planning time : 50.893 ms
Execution time : 46,194.108 ms