explain.depesz.com

PostgreSQL's explain analyze made readable

Result: sc8e

Settings
# exclusive inclusive rows x rows loops node
1. 0.058 6,539.916 ↓ 500.0 500 1

Limit (cost=15,084.10..15,084.10 rows=1 width=62) (actual time=6,539.824..6,539.916 rows=500 loops=1)

2.          

CTE params

3. 0.002 0.002 ↑ 1.0 1 1

Result (cost=0.00..0.01 rows=1 width=93) (actual time=0.001..0.002 rows=1 loops=1)

4. 10.077 6,539.858 ↓ 500.0 500 1

Sort (cost=15,084.09..15,084.09 rows=1 width=62) (actual time=6,539.823..6,539.858 rows=500 loops=1)

  • Sort Key: cih.account_transaction_id DESC
  • Sort Method: top-N heapsort Memory: 99kB
5. 0.000 6,529.781 ↓ 35,267.0 35,267 1

Nested Loop (cost=1.57..15,084.08 rows=1 width=62) (actual time=5.403..6,529.781 rows=35,267 loops=1)

6. 3,320.918 5,274.321 ↓ 659,405.0 659,405 1

Nested Loop (cost=1.14..15,082.75 rows=1 width=4) (actual time=0.087..5,274.321 rows=659,405 loops=1)

  • Join Filter: (((params.sourcetype IS NULL) OR (ats.source_type = params.sourcetype)) AND (at.source_id = ats.id))
  • Rows Removed by Join Filter: 15323907
7. 331.023 634.593 ↓ 659,405.0 659,405 1

Nested Loop (cost=1.14..15,080.60 rows=1 width=42) (actual time=0.071..634.593 rows=659,405 loops=1)

  • Join Filter: (((params.createdbefore IS NULL) OR (at.created_at <= params.createdbefore)) AND ((params.createdafter IS NULL) OR (at.created_at >= params.createdafter)) AND ((params.amountlessthan IS NULL) OR (at.amount < params.amountlessthan)) AND ((params.amountgreaterthan IS NULL) OR (at.amount > params.amountgreaterthan)) AND ((params.amountnotequalzero IS NULL) OR ((at.amount = 0) AND params.amountnotequalzero) OR ((at.amount <> 0) AND (NOT params.amountnotequalzero))) AND ((params.anytext IS NULL) OR (params.anytext ~* at.source_transaction_id) OR (params.anytext ~* at.notes)))
8. 0.019 0.064 ↑ 1.0 1 1

Nested Loop (cost=0.57..2.50 rows=1 width=93) (actual time=0.041..0.064 rows=1 loops=1)

9. 0.002 0.030 ↑ 1.0 1 1

Nested Loop (cost=0.29..1.33 rows=1 width=97) (actual time=0.027..0.030 rows=1 loops=1)

10. 0.006 0.006 ↑ 1.0 1 1

CTE Scan on params (cost=0.00..0.02 rows=1 width=93) (actual time=0.004..0.006 rows=1 loops=1)

  • Filter: ((amountnotequalzero IS NULL) OR amountnotequalzero OR (NOT amountnotequalzero))
11. 0.022 0.022 ↑ 1.0 1 1

Index Only Scan using player_pkey on player p (cost=0.29..1.31 rows=1 width=4) (actual time=0.022..0.022 rows=1 loops=1)

  • Index Cond: (id = params.playerid)
  • Heap Fetches: 0
12. 0.015 0.015 ↑ 1.0 1 1

Index Scan using account_player_id_currency_id_key on account a (cost=0.29..1.17 rows=1 width=8) (actual time=0.013..0.015 rows=1 loops=1)

  • Index Cond: (player_id = p.id)
  • Filter: active
13. 303.506 303.506 ↓ 21.4 659,405 1

Index Scan using account_transaction_account_id_idx on account_transaction at (cost=0.56..14,155.84 rows=30,742 width=52) (actual time=0.029..303.506 rows=659,405 loops=1)

  • Index Cond: (account_id = a.id)
14. 1,318.810 1,318.810 ↑ 1.9 24 659,405

Seq Scan on account_transaction_source ats (cost=0.00..1.46 rows=46 width=26) (actual time=0.001..0.002 rows=24 loops=659,405)

15. 1,318.810 1,318.810 ↓ 0.0 0 659,405

Index Scan using casino_incentive_history_account_transaction_idx on casino_incentive_history cih (cost=0.43..1.32 rows=1 width=58) (actual time=0.002..0.002 rows=0 loops=659,405)

  • Index Cond: (account_transaction_id = at.id)