explain.depesz.com

PostgreSQL's explain analyze made readable

Result: q8WiX

Settings
# exclusive inclusive rows x rows loops node
1. 0.109 9,146.067 ↑ 1.7 29 1

Group (cost=56,080.34..14,282,854.38 rows=48 width=45) (actual time=474.751..9,146.067 rows=29 loops=1)

  • Group Key: payment_systems.id
2.          

CTE date_ranges

3. 0.014 0.014 ↑ 125.0 8 1

Function Scan on generate_series days (cost=0.02..12.52 rows=1,000 width=16) (actual time=0.011..0.014 rows=8 loops=1)

4. 0.065 358.697 ↑ 1.7 29 1

Merge Join (cost=56,067.82..56,068.80 rows=48 width=9) (actual time=358.609..358.697 rows=29 loops=1)

  • Merge Cond: (payment_systems.id = transactions.payment_system)
5. 0.055 0.103 ↓ 1.8 94 1

Sort (cost=3.00..3.13 rows=52 width=9) (actual time=0.082..0.103 rows=94 loops=1)

  • Sort Key: payment_systems.id
  • Sort Method: quicksort Memory: 29kB
6. 0.048 0.048 ↓ 1.8 96 1

Seq Scan on payment_systems (cost=0.00..1.52 rows=52 width=9) (actual time=0.013..0.048 rows=96 loops=1)

7. 0.020 358.529 ↑ 1.7 29 1

Sort (cost=56,064.82..56,064.94 rows=48 width=2) (actual time=358.522..358.529 rows=29 loops=1)

  • Sort Key: transactions.payment_system
  • Sort Method: quicksort Memory: 26kB
8. 5.769 358.509 ↑ 1.7 29 1

HashAggregate (cost=56,062.52..56,063.00 rows=48 width=2) (actual time=358.506..358.509 rows=29 loops=1)

  • Group Key: transactions.payment_system
9. 352.740 352.740 ↓ 1.1 48,291 1

Index Scan using transaction_created on transactions (cost=0.45..55,956.74 rows=42,312 width=2) (actual time=0.189..352.740 rows=48,291 loops=1)

  • Index Cond: ((created < (now())::timestamp without time zone) AND (created >= ((now() - '7 days'::interval))::timestamp without time zone))
  • Filter: (amount_currency = 1)
  • Rows Removed by Filter: 1,016,645
10.          

SubPlan (for Group)

11. 0.116 8,787.261 ↑ 1.0 1 29

Aggregate (cost=296,391.09..296,391.10 rows=1 width=32) (actual time=303.009..303.009 rows=1 loops=29)

12. 8.787 8,787.145 ↑ 28.6 7 29

HashAggregate (cost=296,386.09..296,388.59 rows=200 width=48) (actual time=303.003..303.005 rows=7 loops=29)

  • Group Key: date_ranges.start_dates, date_ranges.end_dates
13. 298.903 8,778.358 ↑ 866.3 1,881 29

Nested Loop (cost=12,828.77..284,164.42 rows=1,629,556 width=21) (actual time=234.923..302.702 rows=1,881 loops=29)

  • Join Filter: ((transactions_1.created < date_ranges.end_dates) AND (transactions_1.created >= date_ranges.start_dates))
  • Rows Removed by Join Filter: 198,393
14. 0.087 0.087 ↑ 125.0 8 29

CTE Scan on date_ranges (cost=0.00..20.00 rows=1,000 width=16) (actual time=0.001..0.003 rows=8 loops=29)

15. 363.196 8,479.368 ↓ 1.7 25,034 232

Materialize (cost=12,828.77..27,526.09 rows=14,666 width=13) (actual time=9.844..36.549 rows=25,034 loops=232)

16. 5,895.845 8,116.172 ↓ 1.7 25,034 29

Bitmap Heap Scan on transactions transactions_1 (cost=12,828.77..27,452.76 rows=14,666 width=13) (actual time=78.163..279.868 rows=25,034 loops=29)

  • Recheck Cond: ((payment_system = payment_systems.id) AND (amount_currency = 1))
  • Rows Removed by Index Recheck: 984,428
  • Heap Blocks: exact=304,642 lossy=673,099
17. 37.874 2,220.327 ↓ 0.0 0 29

BitmapAnd (cost=12,828.77..12,828.77 rows=14,666 width=0) (actual time=76.563..76.563 rows=0 loops=29)

18. 532.498 532.498 ↓ 1.0 366,235 29

Bitmap Index Scan on idx_eaa81a4cec758a1d (cost=0.00..4,329.99 rows=358,874 width=0) (actual time=18.362..18.362 rows=366,235 loops=29)

  • Index Cond: (payment_system = payment_systems.id)
19. 1,649.955 1,649.955 ↓ 1.0 726,006 29

Bitmap Index Scan on transaction_currency (cost=0.00..8,491.19 rows=703,967 width=0) (actual time=56.895..56.895 rows=726,006 loops=29)

  • Index Cond: (amount_currency = 1)