explain.depesz.com

PostgreSQL's explain analyze made readable

Result: yn5N

Settings
# exclusive inclusive rows x rows loops node
1. 374.690 5,785.663 ↑ 1,864.4 730 1

GroupAggregate (cost=787,425.14..821,561.69 rows=1,360,981 width=40) (actual time=5,263.137..5,785.663 rows=730 loops=1)

  • Group Key: next_assorted_query.product_upc
2. 498.088 5,410.973 ↑ 1.0 1,359,365 1

Sort (cost=787,425.14..790,846.26 rows=1,368,449 width=10) (actual time=5,263.097..5,410.973 rows=1,359,365 loops=1)

  • Sort Key: next_assorted_query.product_upc
  • Sort Method: external merge Disk: 26640kB
3. 316.821 4,912.885 ↑ 1.0 1,359,365 1

Subquery Scan on next_assorted_query (cost=529,180.62..624,565.07 rows=1,368,449 width=10) (actual time=3,108.243..4,912.885 rows=1,359,365 loops=1)

  • Filter: (next_assorted_query.optimisation_id = 30)
  • Rows Removed by Filter: 1365915
4. 953.242 4,596.064 ↓ 1.0 2,725,280 1

WindowAgg (cost=529,180.62..590,499.19 rows=2,725,270 width=18) (actual time=3,108.242..4,596.064 rows=2,725,280 loops=1)

5. 1,970.625 3,642.822 ↓ 1.0 2,725,280 1

Sort (cost=529,180.62..535,993.79 rows=2,725,270 width=17) (actual time=3,108.234..3,642.822 rows=2,725,280 loops=1)

  • Sort Key: assortment_products.store_id, products.upc, assortment_products.optimisation_id DESC
  • Sort Method: external merge Disk: 72032kB
6. 332.928 1,672.197 ↓ 1.0 2,725,280 1

Nested Loop (cost=39.78..126,093.94 rows=2,725,270 width=17) (actual time=0.501..1,672.197 rows=2,725,280 loops=1)

7. 0.025 0.025 ↑ 1.0 1 1

Index Only Scan using customer_groups_pkey on customer_groups (cost=0.15..8.17 rows=1 width=0) (actual time=0.022..0.025 rows=1 loops=1)

  • Index Cond: (id = 1)
  • Heap Fetches: 1
8. 610.148 1,339.244 ↓ 1.0 2,725,280 1

Hash Join (cost=39.63..98,833.08 rows=2,725,270 width=17) (actual time=0.477..1,339.244 rows=2,725,280 loops=1)

  • Hash Cond: (assortment_products.product_id = products.id)
9. 728.770 728.770 ↓ 1.0 2,725,280 1

Seq Scan on assortment_products (cost=0.00..91,606.55 rows=2,725,270 width=13) (actual time=0.139..728.770 rows=2,725,280 loops=1)

  • Filter: (optimisation_id = ANY ('{30,29}'::integer[]))
  • Rows Removed by Filter: 560
10. 0.161 0.326 ↑ 1.0 917 1

Hash (cost=28.17..28.17 rows=917 width=12) (actual time=0.326..0.326 rows=917 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 48kB
11. 0.165 0.165 ↑ 1.0 917 1

Seq Scan on products (cost=0.00..28.17 rows=917 width=12) (actual time=0.003..0.165 rows=917 loops=1)

Planning time : 1.885 ms
Execution time : 5,803.981 ms