explain.depesz.com

PostgreSQL's explain analyze made readable

Result: VwCw

Settings
# exclusive inclusive rows x rows loops node
1. 0.134 1,760.359 ↑ 5.0 6 1

GroupAggregate (cost=36,073.04..36,073.94 rows=30 width=73) (actual time=1,760.255..1,760.359 rows=6 loops=1)

  • Group Key: ('GLOBAL'::text), ((tv.tv_transaction_dt >= (now() - '1 day'::interval)))
2. 0.160 1,760.225 ↓ 12.5 375 1

Sort (cost=36,073.04..36,073.11 rows=30 width=73) (actual time=1,760.206..1,760.225 rows=375 loops=1)

  • Sort Key: ('GLOBAL'::text), ((tv.tv_transaction_dt >= (now() - '1 day'::interval)))
  • Sort Method: quicksort Memory: 54kB
3. 0.031 1,760.065 ↓ 12.5 375 1

Append (cost=3,760.60..36,072.00 rows=30 width=73) (actual time=7.184..1,760.065 rows=375 loops=1)

4. 2.744 7.184 ↑ 1.0 2 1

HashAggregate (cost=3,760.60..3,760.64 rows=2 width=73) (actual time=7.184..7.184 rows=2 loops=1)

  • Group Key: (tv.tv_transaction_dt >= (now() - '1 day'::interval))
5. 3.756 4.440 ↓ 2.3 12,624 1

Bitmap Heap Scan on transaction_velocity tv (cost=248.52..3,719.36 rows=5,498 width=6) (actual time=0.701..4.440 rows=12,624 loops=1)

  • Recheck Cond: (((tv_transaction_dt >= (now() - '7 days'::interval)) AND (tv_transaction_dt < (date_trunc('hour'::text, (now() - '7 days'::interval)) + '01:00:00'::interval))) OR ((tv_transaction_dt >= date_trunc('hour'::text, (now() - '1 day'::interval))) AND (tv_transaction_dt < (date_trunc('hour'::text, (now() - '1 day'::interval)) + '01:00:00'::interval))) OR ((tv_transaction_dt >= (date_trunc('hour'::text, now()) - '01:00:00'::interval)) AND (tv_transaction_dt < now())))
  • Heap Blocks: exact=133
6. 0.001 0.684 ↓ 0.0 0 1

BitmapOr (cost=248.52..248.52 rows=5,508 width=0) (actual time=0.684..0.684 rows=0 loops=1)

7. 0.048 0.048 ↑ 1.4 519 1

Bitmap Index Scan on idx_transaction_velocity_dt_complex (cost=0.00..31.60 rows=716 width=0) (actual time=0.048..0.048 rows=519 loops=1)

  • Index Cond: ((tv_transaction_dt >= (now() - '7 days'::interval)) AND (tv_transaction_dt < (date_trunc('hour'::text, (now() - '7 days'::interval)) + '01:00:00'::interval)))
8. 0.218 0.218 ↑ 1.1 4,289 1

Bitmap Index Scan on idx_transaction_velocity_dt_complex (cost=0.00..208.36 rows=4,792 width=0) (actual time=0.218..0.218 rows=4,289 loops=1)

  • Index Cond: ((tv_transaction_dt >= date_trunc('hour'::text, (now() - '1 day'::interval))) AND (tv_transaction_dt < (date_trunc('hour'::text, (now() - '1 day'::interval)) + '01:00:00'::interval)))
9. 0.417 0.417 ↓ 7,816.0 7,816 1

Bitmap Index Scan on idx_transaction_velocity_dt_complex (cost=0.00..4.44 rows=1 width=0) (actual time=0.417..0.417 rows=7,816 loops=1)

  • Index Cond: ((tv_transaction_dt >= (date_trunc('hour'::text, now()) - '01:00:00'::interval)) AND (tv_transaction_dt < now()))
10. 0.003 184.184 ↓ 2.0 2 1

Subquery Scan on *SELECT* 2 (cost=37.77..37.82 rows=1 width=73) (actual time=184.181..184.184 rows=2 loops=1)

11. 0.016 184.181 ↓ 2.0 2 1

GroupAggregate (cost=37.77..37.81 rows=1 width=50) (actual time=184.179..184.181 rows=2 loops=1)

  • Group Key: c.c_bid, ((tv_1.tv_transaction_dt >= (now() - '1 day'::interval)))
12. 0.021 184.165 ↓ 5.0 5 1

Sort (cost=37.77..37.78 rows=1 width=15) (actual time=184.164..184.165 rows=5 loops=1)

  • Sort Key: ((tv_1.tv_transaction_dt >= (now() - '1 day'::interval)))
  • Sort Method: quicksort Memory: 25kB
13. 0.045 184.144 ↓ 5.0 5 1

Nested Loop (cost=1.29..37.76 rows=1 width=15) (actual time=2.409..184.144 rows=5 loops=1)

14. 0.013 151.576 ↓ 18.5 37 1

Nested Loop (cost=0.86..36.44 rows=2 width=17) (actual time=0.053..151.576 rows=37 loops=1)

15. 0.021 0.021 ↑ 1.0 1 1

Index Scan using uk_c_bid on customer c (cost=0.43..8.45 rows=1 width=17) (actual time=0.019..0.021 rows=1 loops=1)

  • Index Cond: (c_bid = 'C210007U'::bpchar)
16. 151.542 151.542 ↓ 6.2 37 1

Index Scan using idx_a_customer_id on account a (cost=0.43..27.93 rows=6 width=16) (actual time=0.032..151.542 rows=37 loops=1)

  • Index Cond: (a_customer_id = c.id_customer)
17. 32.523 32.523 ↓ 0.0 0 37

Index Only Scan using idx_acct_transdt_amount on transaction_velocity tv_1 (cost=0.42..0.65 rows=1 width=21) (actual time=0.602..0.879 rows=0 loops=37)

  • Index Cond: (tv_account_id = a.id_account)
  • Filter: (((tv_transaction_dt >= (now() - '7 days'::interval)) AND (tv_transaction_dt < (date_trunc('hour'::text, (now() - '7 days'::interval)) + '01:00:00'::interval))) OR ((tv_transaction_dt >= date_trunc('hour'::text, (now() - '1 day'::interval))) AND (tv_transaction_dt < (date_trunc('hour'::text, (now() - '1 day'::interval)) + '01:00:00'::interval))) OR ((tv_transaction_dt >= (date_trunc('hour'::text, now()) - '01:00:00'::interval)) AND (tv_transaction_dt < now())))
  • Rows Removed by Filter: 3
  • Heap Fetches: 38
18. 0.008 1,519.276 ↑ 1.0 2 1

Subquery Scan on *SELECT* 3 (cost=30,947.79..30,949.29 rows=2 width=73) (actual time=1,516.920..1,519.276 rows=2 loops=1)

19. 2.823 1,519.268 ↑ 1.0 2 1

GroupAggregate (cost=30,947.79..30,949.27 rows=2 width=50) (actual time=1,516.914..1,519.268 rows=2 loops=1)

  • Group Key: p.p_bid, ((tv_2.tv_transaction_dt >= (now() - '1 day'::interval)))
20. 3.393 1,516.445 ↓ 71.6 8,233 1

Sort (cost=30,947.79..30,948.08 rows=115 width=15) (actual time=1,515.834..1,516.445 rows=8,233 loops=1)

  • Sort Key: ((tv_2.tv_transaction_dt >= (now() - '1 day'::interval)))
  • Sort Method: quicksort Memory: 828kB
21. 4.191 1,513.052 ↓ 71.6 8,233 1

Nested Loop (cost=249.39..30,943.85 rows=115 width=15) (actual time=13.352..1,513.052 rows=8,233 loops=1)

  • Join Filter: (c_1.c_partner_id = p.id_partner)
  • Rows Removed by Join Filter: 4391
22. 0.028 0.028 ↑ 1.0 1 1

Seq Scan on partner p (cost=0.00..6.60 rows=1 width=17) (actual time=0.008..0.028 rows=1 loops=1)

  • Filter: (p_bid = 'R2100005'::bpchar)
  • Rows Removed by Filter: 51
23. 9.392 1,508.833 ↓ 2.3 12,624 1

Nested Loop (cost=249.39..30,867.67 rows=5,498 width=21) (actual time=13.332..1,508.833 rows=12,624 loops=1)

24. 6.881 1,436.321 ↓ 2.3 12,624 1

Nested Loop (cost=248.96..27,180.23 rows=5,498 width=21) (actual time=13.317..1,436.321 rows=12,624 loops=1)

25. 2.239 2.928 ↓ 2.3 12,624 1

Bitmap Heap Scan on transaction_velocity tv_2 (cost=248.52..3,678.13 rows=5,498 width=21) (actual time=0.706..2.928 rows=12,624 loops=1)

  • Recheck Cond: (((tv_transaction_dt >= (now() - '7 days'::interval)) AND (tv_transaction_dt < (date_trunc('hour'::text, (now() - '7 days'::interval)) + '01:00:00'::interval))) OR ((tv_transaction_dt >= date_trunc('hour'::text, (now() - '1 day'::interval))) AND (tv_transaction_dt < (date_trunc('hour'::text, (now() - '1 day'::interval)) + '01:00:00'::interval))) OR ((tv_transaction_dt >= (date_trunc('hour'::text, now()) - '01:00:00'::interval)) AND (tv_transaction_dt < now())))
  • Heap Blocks: exact=133
26. 0.000 0.689 ↓ 0.0 0 1

BitmapOr (cost=248.52..248.52 rows=5,508 width=0) (actual time=0.689..0.689 rows=0 loops=1)

27. 0.046 0.046 ↑ 1.4 519 1

Bitmap Index Scan on idx_transaction_velocity_dt_complex (cost=0.00..31.60 rows=716 width=0) (actual time=0.045..0.046 rows=519 loops=1)

  • Index Cond: ((tv_transaction_dt >= (now() - '7 days'::interval)) AND (tv_transaction_dt < (date_trunc('hour'::text, (now() - '7 days'::interval)) + '01:00:00'::interval)))
28. 0.213 0.213 ↑ 1.1 4,289 1

Bitmap Index Scan on idx_transaction_velocity_dt_complex (cost=0.00..208.36 rows=4,792 width=0) (actual time=0.213..0.213 rows=4,289 loops=1)

  • Index Cond: ((tv_transaction_dt >= date_trunc('hour'::text, (now() - '1 day'::interval))) AND (tv_transaction_dt < (date_trunc('hour'::text, (now() - '1 day'::interval)) + '01:00:00'::interval)))
29. 0.430 0.430 ↓ 7,816.0 7,816 1

Bitmap Index Scan on idx_transaction_velocity_dt_complex (cost=0.00..4.44 rows=1 width=0) (actual time=0.429..0.430 rows=7,816 loops=1)

  • Index Cond: ((tv_transaction_dt >= (date_trunc('hour'::text, now()) - '01:00:00'::interval)) AND (tv_transaction_dt < now()))
30. 1,426.512 1,426.512 ↑ 1.0 1 12,624

Index Only Scan using idx_id_account_customer_id on account a_1 (cost=0.43..4.27 rows=1 width=16) (actual time=0.113..0.113 rows=1 loops=12,624)

  • Index Cond: (id_account = tv_2.tv_account_id)
  • Heap Fetches: 8426
31. 63.120 63.120 ↑ 1.0 1 12,624

Index Scan using idx_id_customer on customer c_1 (cost=0.43..0.67 rows=1 width=16) (actual time=0.005..0.005 rows=1 loops=12,624)

  • Index Cond: (id_customer = a_1.a_customer_id)
32. 0.558 49.390 ↓ 14.8 369 1

Bitmap Heap Scan on entitlement_total et (cost=1,223.58..1,323.98 rows=25 width=22) (actual time=48.872..49.390 rows=369 loops=1)

  • Recheck Cond: (((transaction_dt >= (date_trunc('hour'::text, (now() - '7 days'::interval)) + '01:00:00'::interval)) AND (transaction_dt < date_trunc('hour'::text, (now() - '1 day'::interval))) AND (entity_bid = ANY ('{GLOBAL,C210007U,R2100005}'::text[]))) OR ((transaction_dt >= (date_trunc('hour'::text, (now() - '1 day'::interval)) + '01:00:00'::interval)) AND (transaction_dt < (date_trunc('hour'::text, now()) - '01:00:00'::interval)) AND (entity_bid = ANY ('{GLOBAL,C210007U,R2100005}'::text[]))))
  • Filter: ((entity_bid = ANY ('{GLOBAL,C210007U,R2100005}'::text[])) AND (((transaction_dt >= (date_trunc('hour'::text, (now() - '7 days'::interval)) + '01:00:00'::interval)) AND (transaction_dt < date_trunc('hour'::text, (now() - '1 day'::interval)))) OR ((transaction_dt >= (date_trunc('hour'::text, (now() - '1 day'::interval)) + '01:00:00'::interval)) AND (transaction_dt < (date_trunc('hour'::text, now()) - '01:00:00'::interval)))))
  • Heap Blocks: exact=189
33. 0.001 48.832 ↓ 0.0 0 1

BitmapOr (cost=1,223.58..1,223.58 rows=25 width=0) (actual time=48.832..48.832 rows=0 loops=1)

34. 42.016 42.016 ↓ 14.5 318 1

Bitmap Index Scan on entitlement_total_pkey (cost=0.00..1,066.44 rows=22 width=0) (actual time=42.016..42.016 rows=318 loops=1)

  • Index Cond: ((transaction_dt >= (date_trunc('hour'::text, (now() - '7 days'::interval)) + '01:00:00'::interval)) AND (transaction_dt < date_trunc('hour'::text, (now() - '1 day'::interval))) AND (entity_bid = ANY ('{GLOBAL,C210007U,R2100005}'::text[])))
35. 6.815 6.815 ↓ 17.0 51 1

Bitmap Index Scan on entitlement_total_pkey (cost=0.00..157.13 rows=3 width=0) (actual time=6.815..6.815 rows=51 loops=1)

  • Index Cond: ((transaction_dt >= (date_trunc('hour'::text, (now() - '1 day'::interval)) + '01:00:00'::interval)) AND (transaction_dt < (date_trunc('hour'::text, now()) - '01:00:00'::interval)) AND (entity_bid = ANY ('{GLOBAL,C210007U,R2100005}'::text[])))