explain.depesz.com

PostgreSQL's explain analyze made readable

Result: QHni

Settings
# exclusive inclusive rows x rows loops node
1. 180.298 117,250.763 ↑ 299.2 1,519 1

WindowAgg (cost=3,506,422.33..3,516,648.42 rows=454,493 width=103) (actual time=117,015.631..117,250.763 rows=1,519 loops=1)

2. 118.326 117,070.465 ↑ 299.2 1,519 1

Sort (cost=3,506,422.33..3,507,558.56 rows=454,493 width=1,375) (actual time=117,015.588..117,070.465 rows=1,519 loops=1)

  • Sort Key: smart_collections.id, perf.store_roi_new DESC
  • Sort Method: quicksort Memory: 3039kB
3. 12.260 116,952.139 ↑ 299.2 1,519 1

Hash Right Join (cost=2,523,636.13..2,920,002.85 rows=454,493 width=1,375) (actual time=111,638.231..116,952.139 rows=1,519 loops=1)

  • Hash Cond: (products_1.id = products.id)
4. 1,840.595 115,502.846 ↑ 393.6 46,546 1

Merge Join (cost=2,510,401.39..2,833,526.20 rows=18,319,623 width=45) (actual time=109,373.100..115,502.846 rows=46,546 loops=1)

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

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

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

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

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

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

8. 23.678 23.678 ↑ 1.0 1,900 1

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

9. 3,011.288 3,011.288 ↑ 1.0 233,604 1

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

10. 0.200 0.330 ↑ 1.0 600 1

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

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

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

12. 1,062.067 110,330.667 ↓ 1.4 843,118 1

Materialize (cost=2,414,618.49..2,417,560.78 rows=588,458 width=28) (actual time=106,012.884..110,330.667 rows=843,118 loops=1)

13. 14,377.909 109,268.600 ↓ 1.4 843,118 1

Sort (cost=2,414,618.49..2,416,089.63 rows=588,458 width=28) (actual time=106,012.878..109,268.600 rows=843,118 loops=1)

  • Sort Key: products_1.store_id, collects.collection_id
  • Sort Method: external merge Disk: 34688kB
14. 3,776.746 94,890.691 ↓ 1.4 845,688 1

Merge Join (cost=2,320,035.54..2,344,144.35 rows=588,458 width=28) (actual time=83,284.691..94,890.691 rows=845,688 loops=1)

  • Merge Cond: (collects.product_id = products_1.id)
15. 1,882.155 84,661.438 ↓ 1.3 856,902 1

Unique (cost=2,208,346.26..2,215,466.87 rows=652,906 width=1,605) (actual time=78,102.713..84,661.438 rows=856,902 loops=1)

16. 13,902.291 82,779.283 ↑ 1.1 857,083 1

Sort (cost=2,208,346.26..2,210,719.80 rows=949,415 width=1,605) (actual time=78,102.711..82,779.283 rows=857,083 loops=1)

  • Sort Key: collects.product_id, collects.collection_id
  • Sort Method: external merge Disk: 35176kB
17. 68,876.992 68,876.992 ↑ 1.1 857,083 1

Index Scan using collects_status_index on collects (cost=0.56..128,094.12 rows=949,415 width=1,605) (actual time=9.116..68,876.992 rows=857,083 loops=1)

  • Index Cond: ((status)::text = 'active'::text)
18. 1,401.550 6,452.507 ↓ 16.5 847,615 1

Sort (cost=111,689.28..111,817.39 rows=51,242 width=12) (actual time=5,181.948..6,452.507 rows=847,615 loops=1)

  • Sort Key: products_1.id
  • Sort Method: quicksort Memory: 3768kB
19. 3,131.393 5,050.957 ↑ 1.0 51,237 1

Bitmap Heap Scan on products products_1 (cost=18,073.54..107,680.87 rows=51,242 width=12) (actual time=1,927.042..5,050.957 rows=51,237 loops=1)

  • Recheck Cond: ((status)::text = 'active'::text)
  • Heap Blocks: exact=19238
20. 1,919.564 1,919.564 ↓ 1.5 77,533 1

Bitmap Index Scan on products_status_index (cost=0.00..18,060.73 rows=51,242 width=0) (actual time=1,919.564..1,919.564 rows=77,533 loops=1)

  • Index Cond: ((status)::text = 'active'::text)
21. 2.951 1,437.033 ↑ 2.0 703 1

Hash (cost=13,217.11..13,217.11 rows=1,410 width=1,338) (actual time=1,437.033..1,437.033 rows=703 loops=1)

  • Buckets: 2048 Batches: 1 Memory Usage: 1150kB
22. 17.266 1,434.082 ↑ 2.0 703 1

Hash Join (cost=10,407.82..13,217.11 rows=1,410 width=1,338) (actual time=379.633..1,434.082 rows=703 loops=1)

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

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

24. 70.749 182.756 ↓ 1.1 2,939 1

Hash (cost=10,373.72..10,373.72 rows=2,728 width=1,324) (actual time=182.756..182.756 rows=2,939 loops=1)

  • Buckets: 4096 Batches: 1 Memory Usage: 3979kB
25. 90.046 112.007 ↓ 1.1 2,939 1

Bitmap Heap Scan on products (cost=833.56..10,373.72 rows=2,728 width=1,324) (actual time=22.109..112.007 rows=2,939 loops=1)

  • Recheck Cond: (store_id = 11)
  • Heap Blocks: exact=839
26. 21.961 21.961 ↓ 1.1 2,939 1

Bitmap Index Scan on products_store_id_index (cost=0.00..832.88 rows=2,728 width=0) (actual time=21.960..21.961 rows=2,939 loops=1)

  • Index Cond: (store_id = 11)