explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 3QAV

Settings
# exclusive inclusive rows x rows loops node
1. 151.291 93,961.009 ↑ 182.4 1,519 1

WindowAgg (cost=1,327,599.65..1,333,832.15 rows=277,000 width=103) (actual time=93,808.544..93,961.009 rows=1,519 loops=1)

2. 30.403 93,809.718 ↑ 182.4 1,519 1

Sort (cost=1,327,599.65..1,328,292.15 rows=277,000 width=1,375) (actual time=93,808.510..93,809.718 rows=1,519 loops=1)

  • Sort Key: smart_collections.id, perf.store_roi_new DESC
  • Sort Method: quicksort Memory: 3039kB
3. 32.288 93,779.315 ↑ 182.4 1,519 1

Hash Right Join (cost=576,594.07..971,186.50 rows=277,000 width=1,375) (actual time=89,910.183..93,779.315 rows=1,519 loops=1)

  • Hash Cond: (products_1.id = products.id)
4. 1,087.013 92,536.968 ↑ 393.6 46,546 1

Merge Join (cost=557,685.42..880,810.23 rows=18,319,623 width=45) (actual time=88,258.622..92,536.968 rows=46,546 loops=1)

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

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

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

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

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

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

8. 14.960 14.960 ↑ 1.0 1,900 1

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

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

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

10. 0.154 1.692 ↑ 1.0 600 1

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

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

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

12. 904.023 88,747.353 ↓ 1.4 843,118 1

Materialize (cost=461,902.52..464,844.81 rows=588,458 width=28) (actual time=85,553.656..88,747.353 rows=843,118 loops=1)

13. 9,146.338 87,843.330 ↓ 1.4 843,118 1

Sort (cost=461,902.52..463,373.67 rows=588,458 width=28) (actual time=85,553.649..87,843.330 rows=843,118 loops=1)

  • Sort Key: products_1.store_id, collects.collection_id
  • Sort Method: external merge Disk: 34688kB
14. 2,370.688 78,696.992 ↓ 1.4 845,688 1

Merge Join (cost=358,416.98..391,428.38 rows=588,458 width=28) (actual time=70,938.937..78,696.992 rows=845,688 loops=1)

  • Merge Cond: (collects.product_id = products_1.id)
15. 2,122.222 55,841.977 ↓ 1.3 856,902 1

GroupAggregate (cost=241,825.76..257,848.97 rows=652,906 width=24) (actual time=51,170.188..55,841.977 rows=856,902 loops=1)

  • Group Key: collects.product_id, collects.collection_id
16. 8,314.649 53,719.755 ↑ 1.1 857,083 1

Sort (cost=241,825.76..244,199.30 rows=949,415 width=24) (actual time=51,170.180..53,719.755 rows=857,083 loops=1)

  • Sort Key: collects.product_id, collects.collection_id
  • Sort Method: external merge Disk: 28456kB
17. 45,405.106 45,405.106 ↑ 1.1 857,083 1

Index Scan using collects_status_index on collects (cost=0.56..128,094.12 rows=949,415 width=24) (actual time=7.743..45,405.106 rows=857,083 loops=1)

  • Index Cond: ((status)::text = 'active'::text)
18. 839.485 20,484.327 ↓ 10.1 847,615 1

Sort (cost=116,591.22..116,801.41 rows=84,076 width=12) (actual time=19,768.720..20,484.327 rows=847,615 loops=1)

  • Sort Key: products_1.id
  • Sort Method: quicksort Memory: 3768kB
19. 19,644.842 19,644.842 ↑ 1.6 51,237 1

Seq Scan on products products_1 (cost=0.00..109,714.05 rows=84,076 width=12) (actual time=0.800..19,644.842 rows=51,237 loops=1)

  • Filter: ((status)::text = 'active'::text)
  • Rows Removed by Filter: 5567
20. 1.235 1,210.059 ↑ 2.0 703 1

Hash (cost=18,891.02..18,891.02 rows=1,410 width=1,338) (actual time=1,210.059..1,210.059 rows=703 loops=1)

  • Buckets: 2048 Batches: 1 Memory Usage: 1150kB
21. 2.563 1,208.824 ↑ 2.0 703 1

Hash Join (cost=3,899.52..18,891.02 rows=1,410 width=1,338) (actual time=899.667..1,208.824 rows=703 loops=1)

  • Hash Cond: (products.id = perf.product_id)
22. 312.046 359.503 ↑ 1.5 2,939 1

Bitmap Heap Scan on products (cost=847.11..15,802.13 rows=4,476 width=1,324) (actual time=48.437..359.503 rows=2,939 loops=1)

  • Recheck Cond: (store_id = 11)
  • Heap Blocks: exact=839
23. 47.457 47.457 ↑ 1.5 2,939 1

Bitmap Index Scan on products_store_id_index (cost=0.00..845.99 rows=4,476 width=0) (actual time=47.457..47.457 rows=2,939 loops=1)

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

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

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