explain.depesz.com

PostgreSQL's explain analyze made readable

Result: WBTK

Settings
# exclusive inclusive rows x rows loops node
1. 0.016 1,992.128 ↑ 1.0 1 1

Subquery Scan on tt (cost=370,479.03..370,479.05 rows=1 width=32) (actual time=1,992.128..1,992.128 rows=1 loops=1)

2. 0.291 1,992.112 ↑ 1.0 1 1

Aggregate (cost=370,479.03..370,479.04 rows=1 width=64) (actual time=1,992.112..1,992.112 rows=1 loops=1)

3.          

Initplan (for Aggregate)

4. 0.082 0.082 ↑ 1.0 1 1

Result (cost=0.00..0.51 rows=1 width=32) (actual time=0.082..0.082 rows=1 loops=1)

5. 0.043 0.043 ↓ 1.2 14 1

Seq Scan on location loc (cost=0.00..1.39 rows=12 width=12) (actual time=0.024..0.043 rows=14 loops=1)

  • Filter: (is_open AND (company_id = 1))
  • Rows Removed by Filter: 17
6.          

SubPlan (for Aggregate)

7. 1.274 1,991.696 ↑ 1.0 1 14

Subquery Scan on elavon_quantity (cost=30,873.03..30,873.09 rows=1 width=32) (actual time=142.263..142.264 rows=1 loops=14)

8.          

Initplan (for Subquery Scan)

9. 0.140 52.990 ↑ 1.0 1 14

Aggregate (cost=1,170.89..1,170.90 rows=1 width=16) (actual time=3.785..3.785 rows=1 loops=14)

10. 0.540 52.850 ↓ 2.7 8 14

Nested Loop (cost=1,013.80..1,170.87 rows=3 width=9) (actual time=2.731..3.775 rows=8 loops=14)

11. 12.166 38.990 ↓ 53.0 159 14

Hash Join (cost=1,013.51..1,166.95 rows=3 width=14) (actual time=2.272..2.785 rows=159 loops=14)

  • Hash Cond: (gctlr.gift_card_to_redeem_id = gctt_redeem.gift_card_to_transaction_id)
12. 14.154 14.154 ↓ 1.4 9,167 14

Seq Scan on gift_card_to_load_redemptions gctlr (cost=0.00..135.68 rows=6,768 width=22) (actual time=0.004..1.011 rows=9,167 loops=14)

13. 0.336 12.670 ↓ 2.5 113 14

Hash (cost=1,012.95..1,012.95 rows=45 width=8) (actual time=0.905..0.905 rows=113 loops=14)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
14. 8.078 12.334 ↓ 2.5 113 14

Bitmap Heap Scan on gift_card_to_transaction gctt_redeem (cost=186.68..1,012.95 rows=45 width=8) (actual time=0.640..0.881 rows=113 loops=14)

  • Recheck Cond: ((location_id = loc.location_id) AND (transaction_type = 'SALE'::text))
  • Filter: ((created_at >= '2020-01-01 00:00:00+00'::timestamp with time zone) AND (created_at <= '2020-03-01 00:00:00+00'::timestamp with time zone))
  • Rows Removed by Filter: 383
  • Heap Blocks: exact=3,931
15. 4.256 4.256 ↓ 1.3 496 14

Bitmap Index Scan on gift_card_to_transaction_transaction_type_location_id_idx (cost=0.00..186.67 rows=373 width=0) (actual time=0.304..0.304 rows=496 loops=14)

  • Index Cond: (location_id = loc.location_id)
16. 13.320 13.320 ↓ 0.0 0 2,220

Index Scan using gift_card_to_transaction_pkey on gift_card_to_transaction gctt_load (cost=0.29..1.30 rows=1 width=11) (actual time=0.006..0.006 rows=0 loops=2,220)

  • Index Cond: (gift_card_to_transaction_id = gctlr.gift_card_to_load_id)
  • Filter: (location_id <> loc.location_id)
  • Rows Removed by Filter: 1
17. 0.182 276.108 ↑ 1.0 1 14

Aggregate (cost=1,745.43..1,745.44 rows=1 width=16) (actual time=19.722..19.722 rows=1 loops=14)

18. 7.976 275.926 ↓ 2.7 8 14

Nested Loop (cost=1,528.32..1,745.41 rows=3 width=9) (actual time=15.458..19.709 rows=8 loops=14)

19. 23.142 180.908 ↓ 42.3 2,072 14

Hash Join (cost=1,528.03..1,681.48 rows=49 width=14) (actual time=11.986..12.922 rows=2,072 loops=14)

  • Hash Cond: (gctlr_1.gift_card_to_redeem_id = gctt_redeem_1.gift_card_to_transaction_id)
20. 9.366 9.366 ↓ 1.4 9,167 14

Seq Scan on gift_card_to_load_redemptions gctlr_1 (cost=0.00..135.68 rows=6,768 width=22) (actual time=0.004..0.669 rows=9,167 loops=14)

21. 3.052 148.400 ↓ 1.9 1,483 14

Hash (cost=1,518.38..1,518.38 rows=772 width=8) (actual time=10.600..10.600 rows=1,483 loops=14)

  • Buckets: 2,048 (originally 1024) Batches: 1 (originally 1) Memory Usage: 78kB
22. 138.992 145.348 ↓ 1.9 1,483 14

Bitmap Heap Scan on gift_card_to_transaction gctt_redeem_1 (cost=142.57..1,518.38 rows=772 width=8) (actual time=1.026..10.382 rows=1,483 loops=14)

  • Recheck Cond: ((created_at >= '2020-01-01 00:00:00+00'::timestamp with time zone) AND (created_at <= '2020-03-01 00:00:00+00'::timestamp with time zone) AND (transaction_type = 'SALE'::text))
  • Rows Removed by Index Recheck: 61,650
  • Filter: (location_id <> loc.location_id)
  • Rows Removed by Filter: 113
  • Heap Blocks: lossy=10,220
23. 0.308 6.356 ↓ 0.0 0 14

BitmapAnd (cost=142.57..142.57 rows=1,260 width=0) (actual time=0.454..0.454 rows=0 loops=14)

24. 0.476 0.476 ↑ 1.7 11,520 14

Bitmap Index Scan on gift_card_to_transaction_created_at_idx (cost=0.00..12.07 rows=20,014 width=0) (actual time=0.034..0.034 rows=11,520 loops=14)

  • Index Cond: ((created_at >= '2020-01-01 00:00:00+00'::timestamp with time zone) AND (created_at <= '2020-03-01 00:00:00+00'::timestamp with time zone))
25. 5.572 5.572 ↓ 1.1 7,193 14

Bitmap Index Scan on gift_card_to_transaction_transaction_type_idx1 (cost=0.00..129.87 rows=6,718 width=0) (actual time=0.398..0.398 rows=7,193 loops=14)

26. 87.042 87.042 ↓ 0.0 0 29,014

Index Scan using gift_card_to_transaction_pkey on gift_card_to_transaction gctt_load_1 (cost=0.29..1.30 rows=1 width=11) (actual time=0.003..0.003 rows=0 loops=29,014)

  • Index Cond: (gift_card_to_transaction_id = gctlr_1.gift_card_to_load_id)
  • Filter: (location_id = loc.location_id)
  • Rows Removed by Filter: 1
27. 0.182 275.478 ↑ 1.0 1 14

Aggregate (cost=1,745.43..1,745.44 rows=1 width=16) (actual time=19.677..19.677 rows=1 loops=14)

28. 0.794 275.296 ↓ 2.7 8 14

Nested Loop (cost=1,528.32..1,745.41 rows=3 width=9) (actual time=16.067..19.664 rows=8 loops=14)

29. 26.572 187.460 ↓ 42.3 2,072 14

Hash Join (cost=1,528.03..1,681.48 rows=49 width=14) (actual time=11.952..13.390 rows=2,072 loops=14)

  • Hash Cond: (gctlr_2.gift_card_to_redeem_id = gctt_redeem_2.gift_card_to_transaction_id)
30. 9.380 9.380 ↓ 1.4 9,167 14

Seq Scan on gift_card_to_load_redemptions gctlr_2 (cost=0.00..135.68 rows=6,768 width=22) (actual time=0.005..0.670 rows=9,167 loops=14)

31. 3.136 151.508 ↓ 1.9 1,483 14

Hash (cost=1,518.38..1,518.38 rows=772 width=8) (actual time=10.822..10.822 rows=1,483 loops=14)

  • Buckets: 2,048 (originally 1024) Batches: 1 (originally 1) Memory Usage: 78kB
32. 142.240 148.372 ↓ 1.9 1,483 14

Bitmap Heap Scan on gift_card_to_transaction gctt_redeem_2 (cost=142.57..1,518.38 rows=772 width=8) (actual time=1.011..10.598 rows=1,483 loops=14)

  • Recheck Cond: ((created_at >= '2020-01-01 00:00:00+00'::timestamp with time zone) AND (created_at <= '2020-03-01 00:00:00+00'::timestamp with time zone) AND (transaction_type = 'SALE'::text))
  • Rows Removed by Index Recheck: 61,650
  • Filter: (location_id <> loc.location_id)
  • Rows Removed by Filter: 113
  • Heap Blocks: lossy=10,220
33. 0.336 6.132 ↓ 0.0 0 14

BitmapAnd (cost=142.57..142.57 rows=1,260 width=0) (actual time=0.438..0.438 rows=0 loops=14)

34. 0.504 0.504 ↑ 1.7 11,520 14

Bitmap Index Scan on gift_card_to_transaction_created_at_idx (cost=0.00..12.07 rows=20,014 width=0) (actual time=0.036..0.036 rows=11,520 loops=14)

  • Index Cond: ((created_at >= '2020-01-01 00:00:00+00'::timestamp with time zone) AND (created_at <= '2020-03-01 00:00:00+00'::timestamp with time zone))
35. 5.292 5.292 ↓ 1.1 7,193 14

Bitmap Index Scan on gift_card_to_transaction_transaction_type_idx1 (cost=0.00..129.87 rows=6,718 width=0) (actual time=0.378..0.378 rows=7,193 loops=14)

36. 87.042 87.042 ↓ 0.0 0 29,014

Index Scan using gift_card_to_transaction_pkey on gift_card_to_transaction gctt_load_2 (cost=0.29..1.30 rows=1 width=11) (actual time=0.003..0.003 rows=0 loops=29,014)

  • Index Cond: (gift_card_to_transaction_id = gctlr_2.gift_card_to_load_id)
  • Filter: (location_id = loc.location_id)
  • Rows Removed by Filter: 1
37. 0.126 39.914 ↑ 1.0 1 14

Aggregate (cost=1,170.89..1,170.90 rows=1 width=16) (actual time=2.851..2.851 rows=1 loops=14)

38. 0.214 39.788 ↓ 2.7 8 14

Nested Loop (cost=1,013.80..1,170.87 rows=3 width=9) (actual time=2.301..2.842 rows=8 loops=14)

39. 11.970 32.914 ↓ 53.0 159 14

Hash Join (cost=1,013.51..1,166.95 rows=3 width=14) (actual time=1.900..2.351 rows=159 loops=14)

  • Hash Cond: (gctlr_3.gift_card_to_redeem_id = gctt_redeem_3.gift_card_to_transaction_id)
40. 9.128 9.128 ↓ 1.4 9,167 14

Seq Scan on gift_card_to_load_redemptions gctlr_3 (cost=0.00..135.68 rows=6,768 width=22) (actual time=0.004..0.652 rows=9,167 loops=14)

41. 0.308 11.816 ↓ 2.5 113 14

Hash (cost=1,012.95..1,012.95 rows=45 width=8) (actual time=0.844..0.844 rows=113 loops=14)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
42. 7.448 11.508 ↓ 2.5 113 14

Bitmap Heap Scan on gift_card_to_transaction gctt_redeem_3 (cost=186.68..1,012.95 rows=45 width=8) (actual time=0.607..0.822 rows=113 loops=14)

  • Recheck Cond: ((location_id = loc.location_id) AND (transaction_type = 'SALE'::text))
  • Filter: ((created_at >= '2020-01-01 00:00:00+00'::timestamp with time zone) AND (created_at <= '2020-03-01 00:00:00+00'::timestamp with time zone))
  • Rows Removed by Filter: 383
  • Heap Blocks: exact=3,931
43. 4.060 4.060 ↓ 1.3 496 14

Bitmap Index Scan on gift_card_to_transaction_transaction_type_location_id_idx (cost=0.00..186.67 rows=373 width=0) (actual time=0.290..0.290 rows=496 loops=14)

  • Index Cond: (location_id = loc.location_id)
44. 6.660 6.660 ↓ 0.0 0 2,220

Index Scan using gift_card_to_transaction_pkey on gift_card_to_transaction gctt_load_3 (cost=0.29..1.30 rows=1 width=11) (actual time=0.003..0.003 rows=0 loops=2,220)

  • Index Cond: (gift_card_to_transaction_id = gctlr_3.gift_card_to_load_id)
  • Filter: (location_id <> loc.location_id)
  • Rows Removed by Filter: 1
45. 0.280 35.112 ↑ 1.0 1 14

Aggregate (cost=1,214.68..1,214.70 rows=1 width=32) (actual time=2.508..2.508 rows=1 loops=14)

46. 0.070 34.832 ↑ 1.0 2 14

Subquery Scan on locs (cost=1,211.68..1,214.67 rows=2 width=28) (actual time=2.456..2.488 rows=2 loops=14)

47. 0.833 34.762 ↑ 1.0 2 14

GroupAggregate (cost=1,211.68..1,214.65 rows=2 width=52) (actual time=2.452..2.483 rows=2 loops=14)

  • Group Key: gctt_load_4.location_id
  • Filter: ((sum(gctt_redeem_4.credit_amount))::numeric(12,4) > '0'::numeric)
  • Rows Removed by Filter: 1
48. 0.770 33.684 ↓ 26.5 159 14

Sort (cost=1,211.68..1,211.69 rows=6 width=16) (actual time=2.397..2.406 rows=159 loops=14)

  • Sort Key: gctt_load_4.location_id
  • Sort Method: quicksort Memory: 27kB
49. 1.678 32.914 ↓ 26.5 159 14

Nested Loop (cost=1,016.12..1,211.60 rows=6 width=16) (actual time=1.414..2.351 rows=159 loops=14)

50. 3.164 26.796 ↓ 26.5 159 14

Hash Join (cost=1,015.83..1,189.67 rows=6 width=17) (actual time=1.398..1.914 rows=159 loops=14)

  • Hash Cond: (gctlr_4.gift_card_to_redeem_id = gctt_redeem_4.gift_card_to_transaction_id)
51. 11.270 11.270 ↓ 1.4 2,231 14

Seq Scan on gift_card_to_load_redemptions gctlr_4 (cost=0.00..169.52 rows=1,648 width=22) (actual time=0.498..0.805 rows=2,231 loops=14)

  • Filter: ((created_at >= '2020-01-01 00:00:00+00'::timestamp with time zone) AND (created_at <= '2020-03-01 00:00:00+00'::timestamp with time zone))
  • Rows Removed by Filter: 6,936
52. 1.078 12.362 ↓ 1.3 496 14

Hash (cost=1,011.16..1,011.16 rows=373 width=11) (actual time=0.883..0.883 rows=496 loops=14)

  • Buckets: 1,024 Batches: 1 Memory Usage: 14kB
53. 7.350 11.284 ↓ 1.3 496 14

Bitmap Heap Scan on gift_card_to_transaction gctt_redeem_4 (cost=186.76..1,011.16 rows=373 width=11) (actual time=0.319..0.806 rows=496 loops=14)

  • Recheck Cond: ((location_id = loc.location_id) AND (transaction_type = 'SALE'::text))
  • Heap Blocks: exact=3,931
54. 3.934 3.934 ↓ 1.3 496 14

Bitmap Index Scan on gift_card_to_transaction_transaction_type_location_id_idx (cost=0.00..186.67 rows=373 width=0) (actual time=0.281..0.281 rows=496 loops=14)

  • Index Cond: (location_id = loc.location_id)
55. 4.440 4.440 ↑ 1.0 1 2,220

Index Scan using gift_card_to_transaction_pkey on gift_card_to_transaction gctt_load_4 (cost=0.29..3.65 rows=1 width=15) (actual time=0.002..0.002 rows=1 loops=2,220)

  • Index Cond: (gift_card_to_transaction_id = gctlr_4.gift_card_to_load_id)
56.          

SubPlan (for GroupAggregate)

57. 0.245 0.245 ↑ 1.0 1 35

Seq Scan on location loc_load (cost=0.00..1.39 rows=1 width=8) (actual time=0.006..0.007 rows=1 loops=35)

  • Filter: (location_id = gctt_load_4.location_id)
  • Rows Removed by Filter: 30
58. 0.252 108.052 ↑ 1.0 1 14

Aggregate (cost=2,171.78..2,171.79 rows=1 width=32) (actual time=7.718..7.718 rows=1 loops=14)

59. 0.084 107.800 ↓ 1.5 3 14

Subquery Scan on locs_1 (cost=2,168.77..2,171.77 rows=2 width=28) (actual time=7.670..7.700 rows=3 loops=14)

60. 0.892 107.716 ↓ 1.5 3 14

GroupAggregate (cost=2,168.77..2,171.75 rows=2 width=52) (actual time=7.665..7.694 rows=3 loops=14)

  • Group Key: gctt_redeem_5.location_id
  • Filter: ((sum(gctt_redeem_5.credit_amount))::numeric(12,4) > '0'::numeric)
  • Rows Removed by Filter: 1
61. 0.756 106.596 ↓ 26.5 159 14

Sort (cost=2,168.77..2,168.79 rows=6 width=16) (actual time=7.606..7.614 rows=159 loops=14)

  • Sort Key: gctt_redeem_5.location_id
  • Sort Method: quicksort Memory: 27kB
62. 17.220 105.840 ↓ 26.5 159 14

Nested Loop (cost=1,614.79..2,168.69 rows=6 width=16) (actual time=0.937..7.560 rows=159 loops=14)

63. 9.012 26.152 ↓ 21.5 2,231 14

Hash Join (cost=1,614.50..1,788.35 rows=104 width=21) (actual time=0.825..1.868 rows=2,231 loops=14)

  • Hash Cond: (gctlr_5.gift_card_to_redeem_id = gctt_redeem_5.gift_card_to_transaction_id)
64. 12.138 12.138 ↓ 1.4 2,231 14

Seq Scan on gift_card_to_load_redemptions gctlr_5 (cost=0.00..169.52 rows=1,648 width=22) (actual time=0.459..0.867 rows=2,231 loops=14)

  • Filter: ((created_at >= '2020-01-01 00:00:00+00'::timestamp with time zone) AND (created_at <= '2020-03-01 00:00:00+00'::timestamp with time zone))
  • Rows Removed by Filter: 6,936
65. 1.111 5.002 ↓ 1.1 7,193 1

Hash (cost=1,530.53..1,530.53 rows=6,718 width=15) (actual time=5.002..5.002 rows=7,193 loops=1)

  • Buckets: 8,192 Batches: 1 Memory Usage: 433kB
66. 3.505 3.891 ↓ 1.1 7,193 1

Bitmap Heap Scan on gift_card_to_transaction gctt_redeem_5 (cost=131.55..1,530.53 rows=6,718 width=15) (actual time=0.516..3.891 rows=7,193 loops=1)

  • Recheck Cond: (transaction_type = 'SALE'::text)
  • Heap Blocks: exact=939
67. 0.386 0.386 ↓ 1.1 7,193 1

Bitmap Index Scan on gift_card_to_transaction_transaction_type_idx1 (cost=0.00..129.87 rows=6,718 width=0) (actual time=0.386..0.386 rows=7,193 loops=1)

68. 62.468 62.468 ↓ 0.0 0 31,234

Index Scan using gift_card_to_transaction_pkey on gift_card_to_transaction gctt_load_5 (cost=0.29..3.66 rows=1 width=11) (actual time=0.002..0.002 rows=0 loops=31,234)

  • Index Cond: (gift_card_to_transaction_id = gctlr_5.gift_card_to_load_id)
  • Filter: (location_id = loc.location_id)
  • Rows Removed by Filter: 1
69.          

SubPlan (for GroupAggregate)

70. 0.228 0.228 ↑ 1.0 1 38

Seq Scan on location loc_redeem (cost=0.00..1.39 rows=1 width=8) (actual time=0.005..0.006 rows=1 loops=38)

  • Filter: (location_id = gctt_redeem_5.location_id)
  • Rows Removed by Filter: 30
71. 0.168 191.142 ↑ 1.0 1 14

Aggregate (cost=2,158.80..2,158.82 rows=1 width=16) (actual time=13.653..13.653 rows=1 loops=14)

72. 7.738 190.974 ↓ 1.6 8 14

Nested Loop (cost=1,626.82..2,158.78 rows=5 width=11) (actual time=9.295..13.641 rows=8 loops=14)

73. 10.612 96.194 ↓ 21.1 2,072 14

Hash Join (cost=1,626.53..1,800.37 rows=98 width=14) (actual time=5.643..6.871 rows=2,072 loops=14)

  • Hash Cond: (gctlr_6.gift_card_to_redeem_id = gctt_redeem_6.gift_card_to_transaction_id)
74. 14.574 14.574 ↓ 1.4 2,231 14

Seq Scan on gift_card_to_load_redemptions gctlr_6 (cost=0.00..169.52 rows=1,648 width=22) (actual time=0.553..1.041 rows=2,231 loops=14)

  • Filter: ((created_at >= '2020-01-01 00:00:00+00'::timestamp with time zone) AND (created_at <= '2020-03-01 00:00:00+00'::timestamp with time zone))
  • Rows Removed by Filter: 6,936
75. 12.054 71.008 ↓ 1.1 6,697 14

Hash (cost=1,547.23..1,547.23 rows=6,344 width=8) (actual time=5.072..5.072 rows=6,697 loops=14)

  • Buckets: 8,192 Batches: 1 Memory Usage: 341kB
76. 53.634 58.954 ↓ 1.1 6,697 14

Bitmap Heap Scan on gift_card_to_transaction gctt_redeem_6 (cost=131.46..1,547.23 rows=6,344 width=8) (actual time=0.511..4.211 rows=6,697 loops=14)

  • Recheck Cond: (transaction_type = 'SALE'::text)
  • Filter: (location_id <> loc.location_id)
  • Rows Removed by Filter: 496
  • Heap Blocks: exact=13,146
77. 5.320 5.320 ↓ 1.1 7,193 14

Bitmap Index Scan on gift_card_to_transaction_transaction_type_idx1 (cost=0.00..129.87 rows=6,718 width=0) (actual time=0.380..0.380 rows=7,193 loops=14)

78. 87.042 87.042 ↓ 0.0 0 29,014

Index Scan using gift_card_to_transaction_pkey on gift_card_to_transaction gctt_load_6 (cost=0.29..3.66 rows=1 width=13) (actual time=0.003..0.003 rows=0 loops=29,014)

  • Index Cond: (gift_card_to_transaction_id = gctlr_6.gift_card_to_load_id)
  • Filter: (location_id = loc.location_id)
  • Rows Removed by Filter: 1
79. 0.126 38.864 ↑ 1.0 1 14

Aggregate (cost=1,211.64..1,211.65 rows=1 width=16) (actual time=2.776..2.776 rows=1 loops=14)

80. 0.606 38.738 ↓ 1.6 8 14

Nested Loop (cost=1,016.12..1,211.61 rows=5 width=11) (actual time=1.856..2.767 rows=8 loops=14)

81. 7.322 31.472 ↓ 26.5 159 14

Hash Join (cost=1,015.83..1,189.67 rows=6 width=14) (actual time=1.422..2.248 rows=159 loops=14)

  • Hash Cond: (gctlr_7.gift_card_to_redeem_id = gctt_redeem_7.gift_card_to_transaction_id)
82. 11.984 11.984 ↓ 1.4 2,231 14

Seq Scan on gift_card_to_load_redemptions gctlr_7 (cost=0.00..169.52 rows=1,648 width=22) (actual time=0.524..0.856 rows=2,231 loops=14)

  • Filter: ((created_at >= '2020-01-01 00:00:00+00'::timestamp with time zone) AND (created_at <= '2020-03-01 00:00:00+00'::timestamp with time zone))
  • Rows Removed by Filter: 6,936
83. 0.938 12.166 ↓ 1.3 496 14

Hash (cost=1,011.16..1,011.16 rows=373 width=8) (actual time=0.869..0.869 rows=496 loops=14)

  • Buckets: 1,024 Batches: 1 Memory Usage: 13kB
84. 6.888 11.228 ↓ 1.3 496 14

Bitmap Heap Scan on gift_card_to_transaction gctt_redeem_7 (cost=186.76..1,011.16 rows=373 width=8) (actual time=0.348..0.802 rows=496 loops=14)

  • Recheck Cond: ((location_id = loc.location_id) AND (transaction_type = 'SALE'::text))
  • Heap Blocks: exact=3,931
85. 4.340 4.340 ↓ 1.3 496 14

Bitmap Index Scan on gift_card_to_transaction_transaction_type_location_id_idx (cost=0.00..186.67 rows=373 width=0) (actual time=0.310..0.310 rows=496 loops=14)

  • Index Cond: (location_id = loc.location_id)
86. 6.660 6.660 ↓ 0.0 0 2,220

Index Scan using gift_card_to_transaction_pkey on gift_card_to_transaction gctt_load_7 (cost=0.29..3.66 rows=1 width=13) (actual time=0.003..0.003 rows=0 loops=2,220)

  • Index Cond: (gift_card_to_transaction_id = gctlr_7.gift_card_to_load_id)
  • Filter: (location_id <> loc.location_id)
  • Rows Removed by Filter: 1
87. 0.126 35.154 ↑ 1.0 1 14

Aggregate (cost=1,211.64..1,211.65 rows=1 width=16) (actual time=2.511..2.511 rows=1 loops=14)

88. 1.622 35.028 ↓ 1.6 8 14

Nested Loop (cost=1,016.12..1,211.61 rows=5 width=11) (actual time=1.713..2.502 rows=8 loops=14)

89. 6.174 28.966 ↓ 26.5 159 14

Hash Join (cost=1,015.83..1,189.67 rows=6 width=14) (actual time=1.331..2.069 rows=159 loops=14)

  • Hash Cond: (gctlr_8.gift_card_to_redeem_id = gctt_redeem_8.gift_card_to_transaction_id)
90. 11.508 11.508 ↓ 1.4 2,231 14

Seq Scan on gift_card_to_load_redemptions gctlr_8 (cost=0.00..169.52 rows=1,648 width=22) (actual time=0.507..0.822 rows=2,231 loops=14)

  • Filter: ((created_at >= '2020-01-01 00:00:00+00'::timestamp with time zone) AND (created_at <= '2020-03-01 00:00:00+00'::timestamp with time zone))
  • Rows Removed by Filter: 6,936
91. 0.882 11.284 ↓ 1.3 496 14

Hash (cost=1,011.16..1,011.16 rows=373 width=8) (actual time=0.806..0.806 rows=496 loops=14)

  • Buckets: 1,024 Batches: 1 Memory Usage: 13kB
92. 6.426 10.402 ↓ 1.3 496 14

Bitmap Heap Scan on gift_card_to_transaction gctt_redeem_8 (cost=186.76..1,011.16 rows=373 width=8) (actual time=0.324..0.743 rows=496 loops=14)

  • Recheck Cond: ((location_id = loc.location_id) AND (transaction_type = 'SALE'::text))
  • Heap Blocks: exact=3,931
93. 3.976 3.976 ↓ 1.3 496 14

Bitmap Index Scan on gift_card_to_transaction_transaction_type_location_id_idx (cost=0.00..186.67 rows=373 width=0) (actual time=0.284..0.284 rows=496 loops=14)

  • Index Cond: (location_id = loc.location_id)
94. 4.440 4.440 ↓ 0.0 0 2,220

Index Scan using gift_card_to_transaction_pkey on gift_card_to_transaction gctt_load_8 (cost=0.29..3.66 rows=1 width=13) (actual time=0.002..0.002 rows=0 loops=2,220)

  • Index Cond: (gift_card_to_transaction_id = gctlr_8.gift_card_to_load_id)
  • Filter: (location_id <> loc.location_id)
  • Rows Removed by Filter: 1
95. 0.182 182.532 ↑ 1.0 1 14

Aggregate (cost=2,158.80..2,158.82 rows=1 width=16) (actual time=13.038..13.038 rows=1 loops=14)

96. 1.774 182.350 ↓ 1.6 8 14

Nested Loop (cost=1,626.82..2,158.78 rows=5 width=11) (actual time=9.157..13.025 rows=8 loops=14)

97. 7.742 93.534 ↓ 21.1 2,072 14

Hash Join (cost=1,626.53..1,800.37 rows=98 width=14) (actual time=5.670..6.681 rows=2,072 loops=14)

  • Hash Cond: (gctlr_9.gift_card_to_redeem_id = gctt_redeem_9.gift_card_to_transaction_id)
98. 17.234 17.234 ↓ 1.4 2,231 14

Seq Scan on gift_card_to_load_redemptions gctlr_9 (cost=0.00..169.52 rows=1,648 width=22) (actual time=0.756..1.231 rows=2,231 loops=14)

  • Filter: ((created_at >= '2020-01-01 00:00:00+00'::timestamp with time zone) AND (created_at <= '2020-03-01 00:00:00+00'::timestamp with time zone))
  • Rows Removed by Filter: 6,936
99. 11.396 68.558 ↓ 1.1 6,697 14

Hash (cost=1,547.23..1,547.23 rows=6,344 width=8) (actual time=4.897..4.897 rows=6,697 loops=14)

  • Buckets: 8,192 Batches: 1 Memory Usage: 341kB
100. 51.842 57.162 ↓ 1.1 6,697 14

Bitmap Heap Scan on gift_card_to_transaction gctt_redeem_9 (cost=131.46..1,547.23 rows=6,344 width=8) (actual time=0.509..4.083 rows=6,697 loops=14)

  • Recheck Cond: (transaction_type = 'SALE'::text)
  • Filter: (location_id <> loc.location_id)
  • Rows Removed by Filter: 496
  • Heap Blocks: exact=13,146
101. 5.320 5.320 ↓ 1.1 7,193 14

Bitmap Index Scan on gift_card_to_transaction_transaction_type_idx1 (cost=0.00..129.87 rows=6,718 width=0) (actual time=0.380..0.380 rows=7,193 loops=14)

102. 87.042 87.042 ↓ 0.0 0 29,014

Index Scan using gift_card_to_transaction_pkey on gift_card_to_transaction gctt_load_9 (cost=0.29..3.66 rows=1 width=13) (actual time=0.003..0.003 rows=0 loops=29,014)

  • Index Cond: (gift_card_to_transaction_id = gctlr_9.gift_card_to_load_id)
  • Filter: (location_id = loc.location_id)
  • Rows Removed by Filter: 1
103. 0.308 39.102 ↑ 1.0 1 14

Aggregate (cost=1,214.68..1,214.70 rows=1 width=32) (actual time=2.793..2.793 rows=1 loops=14)

104. 0.084 38.794 ↑ 1.0 2 14

Subquery Scan on locs_2 (cost=1,211.68..1,214.67 rows=2 width=28) (actual time=2.743..2.771 rows=2 loops=14)

105. 0.903 38.710 ↑ 1.0 2 14

GroupAggregate (cost=1,211.68..1,214.65 rows=2 width=52) (actual time=2.737..2.765 rows=2 loops=14)

  • Group Key: gctt_load_10.location_id
  • Filter: ((sum(gctt_redeem_10.gratuity_amount))::numeric(12,4) > '0'::numeric)
  • Rows Removed by Filter: 1
106. 0.770 37.576 ↓ 26.5 159 14

Sort (cost=1,211.68..1,211.69 rows=6 width=20) (actual time=2.676..2.684 rows=159 loops=14)

  • Sort Key: gctt_load_10.location_id
  • Sort Method: quicksort Memory: 27kB
107. 0.000 36.806 ↓ 26.5 159 14

Nested Loop (cost=1,016.12..1,211.60 rows=6 width=20) (actual time=1.635..2.629 rows=159 loops=14)

108. 3.332 30.184 ↓ 26.5 159 14

Hash Join (cost=1,015.83..1,189.67 rows=6 width=19) (actual time=1.612..2.156 rows=159 loops=14)

  • Hash Cond: (gctlr_10.gift_card_to_redeem_id = gctt_redeem_10.gift_card_to_transaction_id)
109. 11.438 11.438 ↓ 1.4 2,231 14

Seq Scan on gift_card_to_load_redemptions gctlr_10 (cost=0.00..169.52 rows=1,648 width=22) (actual time=0.493..0.817 rows=2,231 loops=14)

  • Filter: ((created_at >= '2020-01-01 00:00:00+00'::timestamp with time zone) AND (created_at <= '2020-03-01 00:00:00+00'::timestamp with time zone))
  • Rows Removed by Filter: 6,936
110. 1.092 15.414 ↓ 1.3 496 14

Hash (cost=1,011.16..1,011.16 rows=373 width=13) (actual time=1.101..1.101 rows=496 loops=14)

  • Buckets: 1,024 Batches: 1 Memory Usage: 14kB
111. 10.234 14.322 ↓ 1.3 496 14

Bitmap Heap Scan on gift_card_to_transaction gctt_redeem_10 (cost=186.76..1,011.16 rows=373 width=13) (actual time=0.330..1.023 rows=496 loops=14)

  • Recheck Cond: ((location_id = loc.location_id) AND (transaction_type = 'SALE'::text))
  • Heap Blocks: exact=3,931
112. 4.088 4.088 ↓ 1.3 496 14

Bitmap Index Scan on gift_card_to_transaction_transaction_type_location_id_idx (cost=0.00..186.67 rows=373 width=0) (actual time=0.292..0.292 rows=496 loops=14)

  • Index Cond: (location_id = loc.location_id)
113. 6.660 6.660 ↑ 1.0 1 2,220

Index Scan using gift_card_to_transaction_pkey on gift_card_to_transaction gctt_load_10 (cost=0.29..3.65 rows=1 width=17) (actual time=0.003..0.003 rows=1 loops=2,220)

  • Index Cond: (gift_card_to_transaction_id = gctlr_10.gift_card_to_load_id)
114.          

SubPlan (for GroupAggregate)

115. 0.231 0.231 ↑ 1.0 1 33

Seq Scan on location loc_load_1 (cost=0.00..1.39 rows=1 width=8) (actual time=0.006..0.007 rows=1 loops=33)

  • Filter: (location_id = gctt_load_10.location_id)
  • Rows Removed by Filter: 30
116. 0.266 126.966 ↑ 1.0 1 14

Aggregate (cost=2,171.78..2,171.79 rows=1 width=32) (actual time=9.069..9.069 rows=1 loops=14)

117. 0.070 126.700 ↑ 1.0 2 14

Subquery Scan on locs_3 (cost=2,168.77..2,171.77 rows=2 width=28) (actual time=9.017..9.050 rows=2 loops=14)

118. 0.889 126.630 ↑ 1.0 2 14

GroupAggregate (cost=2,168.77..2,171.75 rows=2 width=52) (actual time=9.012..9.045 rows=2 loops=14)

  • Group Key: gctt_redeem_11.location_id
  • Filter: ((sum(gctt_redeem_11.gratuity_amount))::numeric(12,4) > '0'::numeric)
  • Rows Removed by Filter: 1
119. 0.826 125.510 ↓ 26.5 159 14

Sort (cost=2,168.77..2,168.79 rows=6 width=20) (actual time=8.956..8.965 rows=159 loops=14)

  • Sort Key: gctt_redeem_11.location_id
  • Sort Method: quicksort Memory: 27kB
120. 3.388 124.684 ↓ 26.5 159 14

Nested Loop (cost=1,614.79..2,168.69 rows=6 width=20) (actual time=0.939..8.906 rows=159 loops=14)

121. 9.309 27.594 ↓ 21.5 2,231 14

Hash Join (cost=1,614.50..1,788.35 rows=104 width=23) (actual time=0.834..1.971 rows=2,231 loops=14)

  • Hash Cond: (gctlr_11.gift_card_to_redeem_id = gctt_redeem_11.gift_card_to_transaction_id)
122. 13.272 13.272 ↓ 1.4 2,231 14

Seq Scan on gift_card_to_load_redemptions gctlr_11 (cost=0.00..169.52 rows=1,648 width=22) (actual time=0.472..0.948 rows=2,231 loops=14)

  • Filter: ((created_at >= '2020-01-01 00:00:00+00'::timestamp with time zone) AND (created_at <= '2020-03-01 00:00:00+00'::timestamp with time zone))
  • Rows Removed by Filter: 6,936
123. 1.119 5.013 ↓ 1.1 7,193 1

Hash (cost=1,530.53..1,530.53 rows=6,718 width=17) (actual time=5.013..5.013 rows=7,193 loops=1)

  • Buckets: 8,192 Batches: 1 Memory Usage: 427kB
124. 3.459 3.894 ↓ 1.1 7,193 1

Bitmap Heap Scan on gift_card_to_transaction gctt_redeem_11 (cost=131.55..1,530.53 rows=6,718 width=17) (actual time=0.677..3.894 rows=7,193 loops=1)

  • Recheck Cond: (transaction_type = 'SALE'::text)
  • Heap Blocks: exact=939
125. 0.435 0.435 ↓ 1.1 7,193 1

Bitmap Index Scan on gift_card_to_transaction_transaction_type_idx1 (cost=0.00..129.87 rows=6,718 width=0) (actual time=0.435..0.435 rows=7,193 loops=1)

126. 93.702 93.702 ↓ 0.0 0 31,234

Index Scan using gift_card_to_transaction_pkey on gift_card_to_transaction gctt_load_11 (cost=0.29..3.66 rows=1 width=13) (actual time=0.003..0.003 rows=0 loops=31,234)

  • Index Cond: (gift_card_to_transaction_id = gctlr_11.gift_card_to_load_id)
  • Filter: (location_id = loc.location_id)
  • Rows Removed by Filter: 1
127.          

SubPlan (for GroupAggregate)

128. 0.231 0.231 ↑ 1.0 1 33

Seq Scan on location loc_redeem_1 (cost=0.00..1.39 rows=1 width=8) (actual time=0.006..0.007 rows=1 loops=33)

  • Filter: (location_id = gctt_redeem_11.location_id)
  • Rows Removed by Filter: 30
129. 1.498 589.008 ↑ 1.0 1 14

Aggregate (cost=11,526.43..11,526.44 rows=1 width=8) (actual time=42.072..42.072 rows=1 loops=14)

130. 587.510 587.510 ↓ 2.5 1,075 14

Seq Scan on gift_card gc (cost=0.00..11,525.37 rows=425 width=8) (actual time=0.159..41.965 rows=1,075 loops=14)

  • Filter: ((created_at >= '2020-01-01 00:00:00+00'::timestamp with time zone) AND (created_at <= '2020-03-01 00:00:00+00'::timestamp with time zone) AND (created_location_id = loc.location_id) AND (char_length(gift_card_number) > 32))
  • Rows Removed by Filter: 272,614