explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 3L3k

Settings
# exclusive inclusive rows x rows loops node
1. 5,306.024 164,651.045 ↓ 12.1 5,037,887 1

Merge Left Join (cost=1,438,729.14..1,448,653.31 rows=414,903 width=1,103) (actual time=136,159.484..164,651.045 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. 119,385.374 155,567.574 ↓ 11.2 4,665,707 1

Sort (cost=1,397,978.67..1,399,015.93 rows=414,903 width=1,104) (actual time=133,050.881..155,567.574 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. 2,364.656 36,182.200 ↓ 11.2 4,665,707 1

Hash Left Join (cost=432,762.76..959,346.21 rows=414,903 width=1,104) (actual time=11,589.484..36,182.200 rows=4,665,707 loops=1)

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

Hash Left Join (cost=432,760.14..956,294.06 rows=414,903 width=1,098) (actual time=11,589.367..33,817.445 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,234.785 25,101.683 ↓ 11.1 4,619,712 1

Hash Left Join (cost=426,474.78..726,804.25 rows=414,903 width=1,044) (actual time=11,449.218..25,101.683 rows=4,619,712 loops=1)

  • Hash Cond: (o.id = t2.orderdetails_id)
6. 4,455.174 4,455.174 ↓ 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.027..4,455.174 rows=4,619,712 loops=1)

7. 3,702.592 11,411.724 ↑ 1.0 4,521,041 1

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

  • Buckets: 65536 Batches: 128 Memory Usage: 3609kB
8. 7,709.132 7,709.132 ↑ 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.025..7,709.132 rows=4,521,041 loops=1)

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

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

  • Buckets: 65536 Batches: 4 Memory Usage: 2395kB
10. 57.621 57.621 ↓ 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.048..57.621 rows=77,811 loops=1)

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

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

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

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

13. 118.439 3,777.447 ↓ 8.9 1,183,471 1

Materialize (cost=40,750.47..41,416.30 rows=133,166 width=88) (actual time=2,979.335..3,777.447 rows=1,183,471 loops=1)

14. 3,044.412 3,659.008 ↑ 1.0 130,447 1

Sort (cost=40,750.47..41,083.39 rows=133,166 width=88) (actual time=2,979.322..3,659.008 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. 614.596 614.596 ↑ 1.0 130,450 1

Seq Scan on vip_payout_report t4 (cost=0.00..23,042.63 rows=133,166 width=88) (actual time=124.554..614.596 rows=130,450 loops=1)

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