explain.depesz.com

PostgreSQL's explain analyze made readable

Result: dK9m

Settings
# exclusive inclusive rows x rows loops node
1. 699.745 24,478.155 ↓ 32,058.0 32,058 1

Hash Join (cost=1,480,429,578.92..1,481,074,916.49 rows=1 width=1,178) (actual time=7,677.647..24,478.155 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))
2.          

CTE ref_fee

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

Hash Join (cost=29,400.16..1,479,670,471.86 rows=315,904 width=79) (actual time=1,194.926..17,998.045 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.023 0.023 ↑ 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.022..0.023 rows=1 loops=1)

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

Seq Scan on fact_orders (cost=0.00..533,998.68 rows=788,701 width=60) (actual time=947.605..5,075.728 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.517 246.892 ↑ 1.1 155,800 1

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

  • Buckets: 65,536 Batches: 4 Memory Usage: 2,951kB
8. 211.375 211.375 ↑ 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.016..211.375 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. 18,017.664 18,017.664 ↑ 9.9 32,058 1

CTE Scan on ref_fee (cost=0.00..6,318.08 rows=315,904 width=252) (actual time=1,194.929..18,017.664 rows=32,058 loops=1)

15. 3,034.079 5,760.746 ↑ 1.0 2,423,334 1

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

  • Buckets: 8,192 Batches: 1,024 Memory Usage: 931kB
16. 2,726.667 2,726.667 ↑ 1.0 2,423,334 1

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

  • Filter: (for_account_sid = '1'::numeric)
  • Rows Removed by Filter: 1,679,668
Planning time : 2.971 ms
Execution time : 24,480.417 ms