explain.depesz.com

PostgreSQL's explain analyze made readable

Result: BsxJ

Settings
# exclusive inclusive rows x rows loops node
1. 198,910.903 198,910.903 ↓ 2,395.8 19,166,602 1

CTE Scan on net_day_sales (cost=3,519,806.77..3,519,966.77 rows=8,000 width=72) (actual time=182,477.085..198,910.903 rows=19,166,602 loops=1)

2.          

CTE sam

3. 22,505.552 52,615.457 ↑ 1.2 39,900,889 1

Hash Semi Join (cost=33,470.73..2,134,792.05 rows=46,013,824 width=187) (actual time=3,695.590..52,615.457 rows=39,900,889 loops=1)

  • Hash Cond: ((payment_ft.website_id)::text = (payment_ft_1.website_id)::text)
4. 26,415.060 26,415.060 ↑ 1.0 46,012,898 1

Seq Scan on payment_ft (cost=0.00..1,468,631.24 rows=46,013,824 width=187) (actual time=0.393..26,415.060 rows=46,012,898 loops=1)

5. 30.505 3,694.845 ↓ 1.6 86,873 1

Hash (cost=32,779.47..32,779.47 rows=55,301 width=25) (actual time=3,694.845..3,694.845 rows=86,873 loops=1)

  • Buckets: 131072 (originally 65536) Batches: 1 (originally 1) Memory Usage: 5860kB
6. 600.542 3,664.340 ↓ 1.6 86,873 1

HashAggregate (cost=31,673.45..32,226.46 rows=55,301 width=25) (actual time=3,622.635..3,664.340 rows=86,873 loops=1)

  • Group Key: payment_ft_1.website_id
7. 3,063.798 3,063.798 ↓ 1.0 923,878 1

Sample Scan on payment_ft payment_ft_1 (cost=0.00..29,372.76 rows=920,276 width=25) (actual time=0.726..3,063.798 rows=923,878 loops=1)

  • Sampling: system ('2'::real)
8.          

CTE sales_per_day

9. 39,296.163 143,726.148 ↓ 311.5 12,458,629 1

HashAggregate (cost=1,380,414.72..1,380,914.72 rows=40,000 width=48) (actual time=135,458.438..143,726.148 rows=12,458,629 loops=1)

  • Group Key: sam.website_id, date_trunc('day'::text, sam.submitted_on_date)
10. 104,429.985 104,429.985 ↑ 1.2 39,900,889 1

CTE Scan on sam (cost=0.00..1,035,311.04 rows=46,013,824 width=44) (actual time=3,695.616..104,429.985 rows=39,900,889 loops=1)

11.          

CTE expanded_net_day_sales

12. 1,700.081 156,243.222 ↓ 311.5 24,917,258 1

Append (cost=0.00..1,800.00 rows=80,000 width=48) (actual time=135,458.451..156,243.222 rows=24,917,258 loops=1)

13. 147,469.216 147,469.216 ↓ 311.5 12,458,629 1

CTE Scan on sales_per_day (cost=0.00..800.00 rows=40,000 width=48) (actual time=135,458.446..147,469.216 rows=12,458,629 loops=1)

14. 7,073.925 7,073.925 ↓ 311.5 12,458,629 1

CTE Scan on sales_per_day sales_per_day_1 (cost=0.00..1,000.00 rows=40,000 width=48) (actual time=0.042..7,073.925 rows=12,458,629 loops=1)

15.          

CTE net_day_sales

16. 30,965.730 192,867.796 ↓ 2,395.8 19,166,602 1

HashAggregate (cost=2,200.00..2,300.00 rows=8,000 width=72) (actual time=182,477.080..192,867.796 rows=19,166,602 loops=1)

  • Group Key: expanded_net_day_sales.website_id, expanded_net_day_sales.sale_day
17. 161,902.066 161,902.066 ↓ 311.5 24,917,258 1

CTE Scan on expanded_net_day_sales (cost=0.00..1,600.00 rows=80,000 width=48) (actual time=135,458.456..161,902.066 rows=24,917,258 loops=1)

Planning time : 0.937 ms
Execution time : 204,804.118 ms