explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Fll

Settings
# exclusive inclusive rows x rows loops node
1. 78.706 1,618.761 ↑ 4.6 50,000 1

Group (cost=49,644.79..6,253,880.44 rows=227,900 width=284) (actual time=28.840..1,618.761 rows=50,000 loops=1)

  • Group Key: c.customer_id, p.product_id
2. 30.879 40.055 ↑ 4.6 50,000 1

Sort (cost=49,644.79..50,214.54 rows=227,900 width=244) (actual time=28.563..40.055 rows=50,000 loops=1)

  • Sort Key: c.customer_id, p.product_id
  • Sort Method: external merge Disk: 2184kB
3. 5.991 9.176 ↑ 4.6 50,000 1

Nested Loop (cost=0.00..2,879.43 rows=227,900 width=244) (actual time=0.053..9.176 rows=50,000 loops=1)

4. 0.185 0.185 ↓ 1.9 1,000 1

Seq Scan on customer c (cost=0.00..15.30 rows=530 width=122) (actual time=0.022..0.185 rows=1,000 loops=1)

5. 2.975 3.000 ↑ 8.6 50 1,000

Materialize (cost=0.00..16.45 rows=430 width=122) (actual time=0.000..0.003 rows=50 loops=1,000)

6. 0.025 0.025 ↑ 8.6 50 1

Seq Scan on product p (cost=0.00..14.30 rows=430 width=122) (actual time=0.018..0.025 rows=50 loops=1)

7.          

SubPlan (forGroup)

8. 100.000 750.000 ↑ 1.0 1 50,000

Aggregate (cost=13.59..13.60 rows=1 width=8) (actual time=0.015..0.015 rows=1 loops=50,000)

9. 50.000 650.000 ↓ 0.0 0 50,000

Bitmap Heap Scan on sale s (cost=9.57..13.59 rows=1 width=4) (actual time=0.013..0.013 rows=0 loops=50,000)

  • Recheck Cond: ((product_id = p.product_id) AND (customer_id = c.customer_id))
  • Heap Blocks: exact=9987
10. 50.000 600.000 ↓ 0.0 0 50,000

BitmapAnd (cost=9.57..9.57 rows=1 width=0) (actual time=0.012..0.012 rows=0 loops=50,000)

11. 450.000 450.000 ↓ 4.0 200 50,000

Bitmap Index Scan on sale_product_id (cost=0.00..4.66 rows=50 width=0) (actual time=0.009..0.009 rows=200 loops=50,000)

  • Index Cond: (product_id = p.product_id)
12. 100.000 100.000 ↑ 5.0 10 50,000

Bitmap Index Scan on sale_customer_id (cost=0.00..4.66 rows=50 width=0) (actual time=0.002..0.002 rows=10 loops=50,000)

  • Index Cond: (customer_id = c.customer_id)
13. 150.000 750.000 ↑ 1.0 1 50,000

Aggregate (cost=13.61..13.62 rows=1 width=32) (actual time=0.015..0.015 rows=1 loops=50,000)

14. 0.000 600.000 ↓ 0.0 0 50,000

Bitmap Heap Scan on sale s_1 (cost=9.57..13.59 rows=1 width=36) (actual time=0.012..0.012 rows=0 loops=50,000)

  • Recheck Cond: ((product_id = p.product_id) AND (customer_id = c.customer_id))
  • Heap Blocks: exact=9987
15. 100.000 600.000 ↓ 0.0 0 50,000

BitmapAnd (cost=9.57..9.57 rows=1 width=0) (actual time=0.012..0.012 rows=0 loops=50,000)

16. 400.000 400.000 ↓ 4.0 200 50,000

Bitmap Index Scan on sale_product_id (cost=0.00..4.66 rows=50 width=0) (actual time=0.008..0.008 rows=200 loops=50,000)

  • Index Cond: (product_id = p.product_id)
17. 100.000 100.000 ↑ 5.0 10 50,000

Bitmap Index Scan on sale_customer_id (cost=0.00..4.66 rows=50 width=0) (actual time=0.002..0.002 rows=10 loops=50,000)

  • Index Cond: (customer_id = c.customer_id)