explain.depesz.com

PostgreSQL's explain analyze made readable

Result: wKpdj : Optimization for: plan #e6KB

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 5,237.841 155,460.950 ↓ 12.1 5,037,887 1

Merge Left Join (cost=1,438,599.97..1,448,511.12 rows=414,903 width=1,103) (actual time=126,988.908..155,460.950 rows=5,037,887 loops=1)

  • Merge Cond: ((o.month = (t4.vip_month)::text) AND ((t3.category)::text = (t4.category)::text) AND ((t3.subtrack)::text = (t4.subtrack)::text) AND ((t3.track)::text = (t4.track)::text) AND (o.customer_id = t4.customer_id) AND ((cg.country_group)::text = (t4.countrygroup)::text))
  • Join Filter: (t2.vip_period_id = t3.vip_period_id)
2. 111,542.949 146,910.755 ↓ 11.2 4,665,707 1

Sort (cost=1,397,978.67..1,399,015.93 rows=414,903 width=1,104) (actual time=124,368.603..146,910.755 rows=4,665,707 loops=1)

  • Sort Key: o.month, t3.category, t3.subtrack, t3.track, o.customer_id, cg.country_group
  • Sort Method: external merge Disk: 683080kB
3. 1,911.573 35,367.806 ↓ 11.2 4,665,707 1

Hash Left Join (cost=432,762.76..959,346.21 rows=414,903 width=1,104) (actual time=10,971.258..35,367.806 rows=4,665,707 loops=1)

  • Hash Cond: (o.country_group_id = cg.id)
4. 8,288.638 33,451.880 ↓ 11.2 4,665,707 1

Hash Left Join (cost=432,760.14..956,294.06 rows=414,903 width=1,098) (actual time=10,966.883..33,451.880 rows=4,665,707 loops=1)

  • Hash Cond: (((o.sku)::text = (t3.sku)::text) AND (t2.vip_period_id = t3.vip_period_id))
5. 9,850.631 24,981.541 ↓ 11.1 4,619,712 1

Hash Left Join (cost=426,474.78..726,804.25 rows=414,903 width=1,044) (actual time=10,776.348..24,981.541 rows=4,619,712 loops=1)

  • Hash Cond: (o.id = t2.orderdetails_id)
6. 4,401.192 4,401.192 ↓ 11.1 4,619,712 1

Seq Scan on od_month o (cost=0.00..142,450.03 rows=414,903 width=988) (actual time=0.192..4,401.192 rows=4,619,712 loops=1)

7. 3,461.195 10,729.718 ↑ 1.0 4,521,041 1

Hash (cost=320,576.02..320,576.02 rows=4,556,301 width=64) (actual time=10,729.718..10,729.718 rows=4,521,041 loops=1)

  • Buckets: 65536 Batches: 128 Memory Usage: 3609kB
8. 7,268.523 7,268.523 ↑ 1.0 4,521,041 1

Seq Scan on orderdetailsadditional t2 (cost=0.00..320,576.02 rows=4,556,301 width=64) (actual time=0.200..7,268.523 rows=4,521,041 loops=1)

  • Filter: (vip_period_id = ANY ('{14,15,16,17}'::integer[]))
  • Rows Removed by Filter: 3395160
9. 71.798 181.701 ↓ 1.0 77,811 1

Hash (cost=4,209.01..4,209.01 rows=77,690 width=70) (actual time=181.701..181.701 rows=77,811 loops=1)

  • Buckets: 65536 Batches: 4 Memory Usage: 2395kB
10. 109.903 109.903 ↓ 1.0 77,811 1

Index Scan using indx_ciscovipskus_vip_period_id on ciscovipskus t3 (cost=0.42..4,209.01 rows=77,690 width=70) (actual time=0.184..109.903 rows=77,811 loops=1)

  • Index Cond: (vip_period_id = ANY ('{14,15,16,17}'::integer[]))
11. 0.038 4.353 ↑ 1.0 72 1

Hash (cost=1.72..1.72 rows=72 width=14) (actual time=4.353..4.353 rows=72 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 12kB
12. 4.315 4.315 ↑ 1.0 72 1

Seq Scan on countrygroup cg (cost=0.00..1.72 rows=72 width=14) (actual time=4.300..4.315 rows=72 loops=1)

13. 120.934 3,312.354 ↓ 8.9 1,183,471 1

Materialize (cost=40,621.30..41,283.88 rows=132,515 width=88) (actual time=2,490.420..3,312.354 rows=1,183,471 loops=1)

14. 2,505.078 3,191.420 ↑ 1.0 130,447 1

Sort (cost=40,621.30..40,952.59 rows=132,515 width=88) (actual time=2,490.394..3,191.420 rows=130,447 loops=1)

  • Sort Key: t4.vip_month, t4.category, t4.subtrack, t4.track, t4.customer_id, t4.countrygroup
  • Sort Method: external merge Disk: 12592kB
15. 686.342 686.342 ↑ 1.0 130,450 1

Seq Scan on vip_payout_report t4 (cost=0.00..23,005.06 rows=132,515 width=88) (actual time=128.403..686.342 rows=130,450 loops=1)

  • Filter: (vip_period_id = ANY ('{14,15,16,17}'::integer[]))
  • Rows Removed by Filter: 379354
Planning time : 2.463 ms