explain.depesz.com

PostgreSQL's explain analyze made readable

Result: o5vM

Settings
# exclusive inclusive rows x rows loops node
1. 5,730.056 265,321.039 ↓ 1.1 5,037,887 1

Merge Left Join (cost=4,164,249.49..4,676,946.62 rows=4,632,997 width=213) (actual time=236,121.959..265,321.039 rows=5,037,887 loops=1)

  • Merge Cond: (((CASE WHEN (date_part('month'::text, (t1.date)::timestamp without time zone) = '1'::double precision) THEN 'January'::text WHEN (date_part('month'::text, (t1.date)::timestamp without time zone) = '2'::double precision) THEN 'February'::text WHEN (date_part('month'::text, (t1.date)::timestamp without time zone) = '3'::double precision) THEN 'March'::text WHEN (date_part('month'::text, (t1.date)::timestamp without time zone) = '4'::double precision) THEN 'April'::text WHEN (date_part('month'::text, (t1.date)::timestamp without time zone) = '5'::double precision) THEN 'May'::text WHEN (date_part('month'::text, (t1.date)::timestamp without time zone) = '6'::double precision) THEN 'June'::text WHEN (date_part('month'::text, (t1.date)::timestamp without time zone) = '7'::double precision) THEN 'July'::text WHEN (date_part('month'::text, (t1.date)::timestamp without time zone) = '8'::double precision) THEN 'August'::text WHEN (date_part('month'::text, (t1.date)::timestamp without time zone) = '9'::double precision) THEN 'September'::text WHEN (date_part('month'::text, (t1.date)::timestamp without time zone) = '10'::double precision) THEN 'October'::text WHEN (date_part('month'::text, (t1.date)::timestamp without time zone) = '11'::double precision) THEN 'November'::text WHEN (date_part('month'::text, (t1.date)::timestamp without time zone) = '12'::double precision) THEN 'December'::text ELSE ''::text END) = (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 (t1.customer_id = t4.customer_id) AND ((cg.country_group)::text = (t4.countrygroup)::text))
  • Join Filter: (t2.vip_period_id = t3.vip_period_id)
2. 107,591.930 255,636.007 ↓ 1.0 4,665,707 1

Sort (cost=4,123,499.02..4,135,081.51 rows=4,632,997 width=182) (actual time=232,928.625..255,636.007 rows=4,665,707 loops=1)

  • Sort Key: (CASE WHEN (date_part('month'::text, (t1.date)::timestamp without time zone) = '1'::double precision) THEN 'January'::text WHEN (date_part('month'::text, (t1.date)::timestamp without time zone) = '2'::double precision) THEN 'February'::text WHEN (date_part('month'::text, (t1.date)::timestamp without time zone) = '3'::double precision) THEN 'March'::text WHEN (date_part('month'::text, (t1.date)::timestamp without time zone) = '4'::double precision) THEN 'April'::text WHEN (date_part('month'::text, (t1.date)::timestamp without time zone) = '5'::double precision) THEN 'May'::text WHEN (date_part('month'::text, (t1.date)::timestamp without time zone) = '6'::double precision) THEN 'June'::text WHEN (date_part('month'::text, (t1.date)::timestamp without time zone) = '7'::double precision) THEN 'July'::text WHEN (date_part('month'::text, (t1.date)::timestamp without time zone) = '8'::double precision) THEN 'August'::text WHEN (date_part('month'::text, (t1.date)::timestamp without time zone) = '9'::double precision) THEN 'September'::text WHEN (date_part('month'::text, (t1.date)::timestamp without time zone) = '10'::double precision) THEN 'October'::text WHEN (date_part('month'::text, (t1.date)::timestamp without time zone) = '11'::double precision) THEN 'November'::text WHEN (date_part('month'::text, (t1.date)::timestamp without time zone) = '12'::double precision) THEN 'December'::text ELSE ''::text END), t3.category, t3.subtrack, t3.track, t1.customer_id, cg.country_group
  • Sort Method: external merge Disk: 682808kB
3. 15,354.346 148,044.077 ↓ 1.0 4,665,707 1

Hash Left Join (cost=2,276,538.44..2,375,377.34 rows=4,632,997 width=182) (actual time=112,739.918..148,044.077 rows=4,665,707 loops=1)

  • Hash Cond: (t1.country_group_id = cg.id)
4. 6,409.991 132,689.653 ↓ 1.0 4,665,707 1

Merge Left Join (cost=2,276,535.82..2,322,947.73 rows=4,632,997 width=176) (actual time=112,739.737..132,689.653 rows=4,665,707 loops=1)

  • Merge Cond: ((t2.vip_period_id = t3.vip_period_id) AND ((t1.sku)::text = (t3.sku)::text))
5. 85,510.523 123,546.859 ↑ 1.0 4,619,712 1

Sort (cost=2,261,595.79..2,273,178.29 rows=4,632,997 width=122) (actual time=110,199.384..123,546.859 rows=4,619,712 loops=1)

  • Sort Key: t2.vip_period_id, t1.sku
  • Sort Method: external merge Disk: 590968kB
6. 11,859.475 38,036.336 ↑ 1.0 4,619,712 1

Hash Left Join (cost=426,474.78..1,146,893.62 rows=4,632,997 width=122) (actual time=13,367.496..38,036.336 rows=4,619,712 loops=1)

  • Hash Cond: (t1.id = t2.orderdetails_id)
7. 12,981.749 12,981.749 ↑ 1.0 4,619,712 1

Seq Scan on orderdetails t1 (cost=0.00..504,726.86 rows=4,632,997 width=66) (actual time=46.549..12,981.749 rows=4,619,712 loops=1)

  • Filter: (vip_period_id = ANY ('{14,15,16,17}'::integer[]))
  • Rows Removed by Filter: 7359079
8. 4,247.302 13,195.112 ↑ 1.0 4,521,041 1

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

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

  • Filter: (vip_period_id = ANY ('{14,15,16,17}'::integer[]))
  • Rows Removed by Filter: 3395160
10. 147.443 2,732.803 ↓ 11.9 924,917 1

Materialize (cost=13,773.22..14,161.67 rows=77,690 width=70) (actual time=2,540.337..2,732.803 rows=924,917 loops=1)

11. 1,052.385 2,585.360 ↓ 1.0 77,811 1

Sort (cost=13,773.22..13,967.44 rows=77,690 width=70) (actual time=2,540.321..2,585.360 rows=77,811 loops=1)

  • Sort Key: t3.vip_period_id, t3.sku
  • Sort Method: external sort Disk: 5920kB
12. 1,532.975 1,532.975 ↓ 1.0 77,811 1

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

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

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

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

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

15. 134.851 3,954.976 ↓ 8.9 1,183,471 1

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

16. 3,127.748 3,820.125 ↑ 1.0 130,447 1

Sort (cost=40,750.47..41,083.39 rows=133,166 width=88) (actual time=3,061.844..3,820.125 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
17. 692.377 692.377 ↑ 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=146.673..692.377 rows=130,450 loops=1)

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