explain.depesz.com

PostgreSQL's explain analyze made readable

Result: E6t3

Settings
# exclusive inclusive rows x rows loops node
1. 649.995 25,125.629 ↓ 0.0 0 1

Update on fact_orders f (cost=1,480,429,578.92..1,481,094,042.49 rows=1 width=1,208) (actual time=25,125.629..25,125.629 rows=0 loops=1)

2.          

CTE ref_fee

3. 0.000 17,897.015 ↑ 9.9 32,058 1

Hash Join (cost=29,400.16..1,479,670,471.86 rows=315,904 width=79) (actual time=1,184.730..17,897.015 rows=32,058 loops=1)

  • Hash Cond: ((fact_orders.for_adcode)::text = f_1.ffp_asin)
  • Join Filter: ((SubPlan 2) = f_1.ffp_date_sid)
  • Rows Removed by Join Filter: 3,137,120
4.          

Initplan (for Hash Join)

5. 0.011 0.011 ↑ 1.0 1 1

Index Scan using dim_date_ddt_date_idx on dim_date (cost=0.29..8.31 rows=1 width=7) (actual time=0.010..0.011 rows=1 loops=1)

  • Index Cond: (ddt_date = (('now'::cstring)::date - 5))
6. 4,992.723 4,992.723 ↑ 87.7 8,996 1

Seq Scan on fact_orders (cost=0.00..533,998.68 rows=788,701 width=60) (actual time=936.713..4,992.723 rows=8,996 loops=1)

  • Filter: ((for_quantity_ordered IS NOT NULL) AND (for_quantity_ordered <> '0'::numeric) AND (for_account_sid = '1'::numeric) AND (((to_char(((timezone('America/Los_Angeles'::text, timezone('utc'::text, for_purchase_date_time)))::date)::timestamp with time zone, 'YYYYMMDD'::text))::integer)::numeric >= $3))
  • Rows Removed by Filter: 4,094,006
7. 35.954 247.864 ↑ 1.1 155,800 1

Hash (cost=26,012.49..26,012.49 rows=174,749 width=32) (actual time=247.864..247.864 rows=155,800 loops=1)

  • Buckets: 65,536 Batches: 4 Memory Usage: 2,951kB
8. 211.910 211.910 ↑ 1.0 174,749 1

Seq Scan on fact_fee_and_price f_1 (cost=0.00..26,012.49 rows=174,749 width=32) (actual time=0.009..211.910 rows=174,749 loops=1)

9.          

SubPlan (for Hash Join)

10. 3,169.178 12,676.712 ↑ 1.0 1 3,169,178

Result (cost=12.23..12.24 rows=1 width=4) (actual time=0.004..0.004 rows=1 loops=3,169,178)

11.          

Initplan (for Result)

12. 0.000 9,507.534 ↑ 1.0 1 3,169,178

Limit (cost=0.42..12.23 rows=1 width=4) (actual time=0.003..0.003 rows=1 loops=3,169,178)

13. 9,507.534 9,507.534 ↑ 26.0 1 3,169,178

Index Only Scan Backward using fact_fee_and_price_ffp_ct_sid_idx on fact_fee_and_price ffap1 (cost=0.42..307.47 rows=26 width=4) (actual time=0.003..0.003 rows=1 loops=3,169,178)

  • Index Cond: ((ffp_ct_sid = f_1.ffp_ct_sid) AND (ffp_date_sid IS NOT NULL))
  • Filter: ((ffp_date_sid)::numeric <= fact_orders.for_purchase_date_sid)
  • Heap Fetches: 3,169,178
14. 780.987 24,475.634 ↓ 32,058.0 32,058 1

Hash Join (cost=759,107.05..1,423,570.63 rows=1 width=1,208) (actual time=7,673.621..24,475.634 rows=32,058 loops=1)

  • Hash Cond: (((ref_fee.for_adcode)::text = (f.for_adcode)::text) AND ((ref_fee.for_order_sid)::text = (f.for_order_sid)::text) AND (ref_fee.for_order_item_id = f.for_order_item_id))
15. 17,915.837 17,915.837 ↑ 9.9 32,058 1

CTE Scan on ref_fee (cost=0.00..6,318.08 rows=315,904 width=500) (actual time=1,184.737..17,915.837 rows=32,058 loops=1)

16. 3,034.224 5,778.810 ↑ 1.0 2,423,334 1

Hash (cost=433,082.22..433,082.22 rows=2,438,276 width=924) (actual time=5,778.810..5,778.810 rows=2,423,334 loops=1)

  • Buckets: 8,192 Batches: 1,024 Memory Usage: 945kB
17. 2,744.586 2,744.586 ↑ 1.0 2,423,334 1

Seq Scan on fact_orders f (cost=0.00..433,082.22 rows=2,438,276 width=924) (actual time=0.009..2,744.586 rows=2,423,334 loops=1)

  • Filter: (for_account_sid = '1'::numeric)
  • Rows Removed by Filter: 1,679,668
Planning time : 0.893 ms
Execution time : 25,126.107 ms