explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 6foX

Settings

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 68.496 1,327.294 ↑ 1.0 1 1

GroupAggregate (cost=3,934.27..3,934.34 rows=1 width=97) (actual time=1,327.294..1,327.294 rows=1 loops=1)

  • Group Key: orders.validity
2. 33.755 1,258.798 ↓ 64,554.0 64,554 1

Sort (cost=3,934.27..3,934.27 rows=1 width=93) (actual time=1,251.768..1,258.798 rows=64,554 loops=1)

  • Sort Key: orders.validity
  • Sort Method: external merge Disk: 6,704kB
3. 7.779 1,225.043 ↓ 64,554.0 64,554 1

Subquery Scan on orders (cost=3,934.22..3,934.26 rows=1 width=93) (actual time=1,165.915..1,225.043 rows=64,554 loops=1)

4. 44.356 1,217.264 ↓ 64,554.0 64,554 1

WindowAgg (cost=3,934.22..3,934.25 rows=1 width=93) (actual time=1,165.914..1,217.264 rows=64,554 loops=1)

5. 44.415 1,172.908 ↓ 64,554.0 64,554 1

Sort (cost=3,934.22..3,934.22 rows=1 width=118) (actual time=1,165.881..1,172.908 rows=64,554 loops=1)

  • Sort Key: p.id
  • Sort Method: external merge Disk: 7,680kB
6. 42.001 1,128.493 ↓ 64,554.0 64,554 1

WindowAgg (cost=3,934.19..3,934.21 rows=1 width=118) (actual time=1,078.566..1,128.493 rows=64,554 loops=1)

7. 48.358 1,086.492 ↓ 64,554.0 64,554 1

Sort (cost=3,934.19..3,934.19 rows=1 width=110) (actual time=1,078.513..1,086.492 rows=64,554 loops=1)

  • Sort Key: p.id DESC
  • Sort Method: external merge Disk: 7,176kB
8. 42.507 1,038.134 ↓ 64,554.0 64,554 1

WindowAgg (cost=3,934.16..3,934.18 rows=1 width=110) (actual time=989.389..1,038.134 rows=64,554 loops=1)

9. 40.432 995.627 ↓ 64,554.0 64,554 1

Sort (cost=3,934.16..3,934.16 rows=1 width=102) (actual time=989.117..995.627 rows=64,554 loops=1)

  • Sort Key: p.brand_id
  • Sort Method: external merge Disk: 6,680kB
10. 41.252 955.195 ↓ 64,554.0 64,554 1

WindowAgg (cost=3,934.13..3,934.15 rows=1 width=102) (actual time=906.518..955.195 rows=64,554 loops=1)

11. 60.272 913.943 ↓ 64,554.0 64,554 1

Sort (cost=3,934.13..3,934.13 rows=1 width=94) (actual time=906.483..913.943 rows=64,554 loops=1)

  • Sort Key: p.brand_id DESC
  • Sort Method: external merge Disk: 5,848kB
12. 73.265 853.671 ↓ 64,554.0 64,554 1

Nested Loop (cost=304.92..3,934.12 rows=1 width=94) (actual time=0.427..853.671 rows=64,554 loops=1)

13. 0.000 715.812 ↓ 64,594.0 64,594 1

Nested Loop Left Join (cost=304.63..3,933.79 rows=1 width=129) (actual time=0.419..715.812 rows=64,594 loops=1)

  • Join Filter: (rcspo.survey_product_id = sp.id)
14. 51.082 656.133 ↓ 64,594.0 64,594 1

Nested Loop Left Join (cost=304.48..3,933.58 rows=1 width=153) (actual time=0.416..656.133 rows=64,594 loops=1)

15. 26.138 540.457 ↓ 64,594.0 64,594 1

Nested Loop (cost=304.06..3,925.46 rows=1 width=159) (actual time=0.408..540.457 rows=64,594 loops=1)

16. 44.737 449.725 ↓ 64,594.0 64,594 1

Nested Loop (cost=303.91..3,917.29 rows=1 width=159) (actual time=0.394..449.725 rows=64,594 loops=1)

17. 2.636 275.800 ↓ 32,297.0 64,594 1

Nested Loop (cost=303.49..3,916.16 rows=2 width=117) (actual time=0.360..275.800 rows=64,594 loops=1)

18. 22.041 143.976 ↓ 2,392.4 64,594 1

Hash Join (cost=303.20..3,907.15 rows=27 width=85) (actual time=0.355..143.976 rows=64,594 loops=1)

  • Hash Cond: (o.wave_id = w.id)
19. 12.841 121.892 ↓ 33.1 79,887 1

Nested Loop (cost=291.87..3,889.41 rows=2,414 width=85) (actual time=0.305..121.892 rows=79,887 loops=1)

20. 1.394 3.410 ↑ 1.0 149 1

Hash Join (cost=291.45..813.68 rows=151 width=49) (actual time=0.290..3.410 rows=149 loops=1)

  • Hash Cond: (st.id = ss.store_id)
21. 1.895 1.895 ↑ 1.0 13,831 1

Seq Scan on stores st (cost=0.00..485.17 rows=14,117 width=32) (actual time=0.006..1.895 rows=13,831 loops=1)

22. 0.025 0.121 ↑ 1.0 149 1

Hash (cost=289.56..289.56 rows=151 width=17) (actual time=0.120..0.121 rows=149 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 16kB
23. 0.056 0.096 ↑ 1.0 149 1

Bitmap Heap Scan on survey_stores ss (cost=5.46..289.56 rows=151 width=17) (actual time=0.044..0.096 rows=149 loops=1)

  • Recheck Cond: (survey_id = '7eb6b6de-76f5-478c-b277-eeb3685752d9'::uuid)
  • Heap Blocks: exact=8
24. 0.040 0.040 ↓ 4.3 652 1

Bitmap Index Scan on index_survey_stores_on_survey_id (cost=0.00..5.42 rows=151 width=0) (actual time=0.039..0.040 rows=652 loops=1)

  • Index Cond: (survey_id = '7eb6b6de-76f5-478c-b277-eeb3685752d9'::uuid)
25. 105.641 105.641 ↓ 1.7 536 149

Index Scan using index_orders_on_store_id on orders o (cost=0.42..17.29 rows=308 width=68) (actual time=0.011..0.709 rows=536 loops=149)

  • Index Cond: (store_id = st.id)
  • Filter: (amount > 0)
  • Rows Removed by Filter: 791
26. 0.004 0.043 ↑ 1.0 4 1

Hash (cost=11.28..11.28 rows=4 width=32) (actual time=0.043..0.043 rows=4 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
27. 0.008 0.039 ↑ 1.0 4 1

Bitmap Heap Scan on waves w (cost=4.30..11.28 rows=4 width=32) (actual time=0.037..0.039 rows=4 loops=1)

  • Recheck Cond: (survey_id = '7eb6b6de-76f5-478c-b277-eeb3685752d9'::uuid)
  • Heap Blocks: exact=2
28. 0.031 0.031 ↑ 1.0 4 1

Bitmap Index Scan on index_waves_on_survey_id_and_number (cost=0.00..4.30 rows=4 width=0) (actual time=0.031..0.031 rows=4 loops=1)

  • Index Cond: (survey_id = '7eb6b6de-76f5-478c-b277-eeb3685752d9'::uuid)
29. 129.188 129.188 ↑ 1.0 1 64,594

Index Scan using index_survey_products_on_product_id on survey_products sp (cost=0.29..0.33 rows=1 width=48) (actual time=0.002..0.002 rows=1 loops=64,594)

  • Index Cond: (product_id = o.product_id)
  • Filter: (survey_id = '7eb6b6de-76f5-478c-b277-eeb3685752d9'::uuid)
  • Rows Removed by Filter: 0
30. 129.188 129.188 ↑ 1.0 1 64,594

Index Scan using index_products_by_id_desc on products p (cost=0.42..0.56 rows=1 width=42) (actual time=0.002..0.002 rows=1 loops=64,594)

  • Index Cond: (id = o.product_id)
31. 64.594 64.594 ↑ 1.0 1 64,594

Index Only Scan using surveys_pkey on surveys s (cost=0.14..8.16 rows=1 width=16) (actual time=0.001..0.001 rows=1 loops=64,594)

  • Index Cond: (id = '7eb6b6de-76f5-478c-b277-eeb3685752d9'::uuid)
  • Heap Fetches: 64,594
32. 64.594 64.594 ↓ 0.0 0 64,594

Index Scan using index_store_survey_product_overrides_uniq on store_survey_product_overrides sto (cost=0.42..8.12 rows=1 width=42) (actual time=0.001..0.001 rows=0 loops=64,594)

  • Index Cond: ((store_id = st.id) AND (survey_product_id = sp.id))
33. 64.594 64.594 ↓ 0.0 0 64,594

Index Scan using index_retail_chain_survey_product_overrides_on_retail_chain_id on retail_chain_survey_product_overrides rcspo (cost=0.15..0.19 rows=2 width=40) (actual time=0.001..0.001 rows=0 loops=64,594)

  • Index Cond: (retail_chain_id = st.retail_chain_id)
34. 64.594 64.594 ↑ 1.0 1 64,594

Index Scan using index_on_survey_product_overrides_ids_and_selling on survey_product_overrides spo (cost=0.29..0.33 rows=1 width=45) (actual time=0.001..0.001 rows=1 loops=64,594)

  • Index Cond: ((survey_id = '7eb6b6de-76f5-478c-b277-eeb3685752d9'::uuid) AND (product_id = o.product_id))
Planning time : 19.619 ms
Execution time : 1,334.545 ms