explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 6R6m

Settings
# exclusive inclusive rows x rows loops node
1. 2.989 208,739.697 ↓ 13.2 422 1

Sort (cost=8,385,672.06..8,385,672.14 rows=32 width=125) (actual time=208,739.610..208,739.697 rows=422 loops=1)

  • Sort Key: ((hashed SubPlan 1)) DESC, ((hashed SubPlan 2)) DESC, ((SubPlan 3)) DESC, transaction_reviews.normalized_risk_score DESC, transaction_reviews.id
  • Sort Method: quicksort Memory: 137kB
2. 91,763.287 208,736.708 ↓ 13.2 422 1

Seq Scan on transaction_reviews (cost=745,425.46..8,385,671.26 rows=32 width=125) (actual time=24,512.156..208,736.708 rows=422 loops=1)

  • Filter: ((resolved_at IS NULL) AND (normalized_risk_score >= '4'::double precision))
  • Rows Removed by Filter: 7,526,619
3.          

SubPlan (for Seq Scan)

4. 6,147.360 12,589.103 ↓ 1.0 344,465 1

Hash Semi Join (cost=192,253.62..572,530.80 rows=337,954 width=4) (actual time=1,757.458..12,589.103 rows=344,465 loops=1)

  • Hash Cond: (payment_records.payment_id = payments.id)
5. 4,686.870 4,686.870 ↑ 1.0 7,633,187 1

Seq Scan on payment_records (cost=0.00..356,413.69 rows=7,658,569 width=8) (actual time=0.014..4,686.870 rows=7,633,187 loops=1)

  • Filter: (id IS NOT NULL)
6. 168.760 1,754.873 ↓ 1.0 344,331 1

Hash (cost=188,029.20..188,029.20 rows=337,954 width=4) (actual time=1,754.873..1,754.873 rows=344,331 loops=1)

  • Buckets: 524,288 Batches: 1 Memory Usage: 16,202kB
7. 1,586.113 1,586.113 ↓ 1.0 344,331 1

Seq Scan on payments (cost=0.00..188,029.20 rows=337,954 width=4) (actual time=0.015..1,586.113 rows=344,331 loops=1)

  • Filter: ((used_express_payouts IS TRUE) AND (id IS NOT NULL))
  • Rows Removed by Filter: 7,283,694
8. 0.598 5.150 ↓ 511.0 511 1

Nested Loop (cost=0.99..14.05 rows=1 width=4) (actual time=0.064..5.150 rows=511 loops=1)

9. 1.498 1.498 ↓ 509.0 509 1

Index Scan using payments_payments_state_index on payments payments_1 (cost=0.56..5.59 rows=1 width=4) (actual time=0.046..1.498 rows=509 loops=1)

  • Index Cond: (state = 'cleared'::text)
  • Filter: (id IS NOT NULL)
10. 3.054 3.054 ↑ 1.0 1 509

Index Scan using payments_payment_records_payment_id_index on payment_records payment_records_1 (cost=0.43..8.45 rows=1 width=8) (actual time=0.005..0.006 rows=1 loops=509)

  • Index Cond: (payment_id = payments_1.id)
  • Filter: (id IS NOT NULL)
11. 94,328.964 104,379.168 ↑ 1.1 828,379 422

Materialize (cost=172,035.73..631,017.46 rows=945,383 width=4) (actual time=2.924..247.344 rows=828,379 loops=422)

12. 4,719.070 10,050.204 ↓ 1.0 953,155 1

Hash Semi Join (cost=172,035.73..622,597.55 rows=945,383 width=4) (actual time=1,232.400..10,050.204 rows=953,155 loops=1)

  • Hash Cond: (payment_records_2.payment_response_id = payment_responses.id)
13. 4,101.145 4,101.145 ↑ 1.0 7,633,187 1

Seq Scan on payment_records payment_records_2 (cost=0.00..356,413.69 rows=7,658,569 width=8) (actual time=0.007..4,101.145 rows=7,633,187 loops=1)

  • Filter: (id IS NOT NULL)
14. 338.626 1,229.989 ↓ 1.0 952,177 1

Hash (cost=156,525.44..156,525.44 rows=945,383 width=4) (actual time=1,229.989..1,229.989 rows=952,177 loops=1)

  • Buckets: 524,288 Batches: 4 Memory Usage: 12,469kB
15. 766.263 891.363 ↓ 1.0 952,177 1

Bitmap Heap Scan on payment_responses (cost=33,147.15..156,525.44 rows=945,383 width=4) (actual time=152.542..891.363 rows=952,177 loops=1)

  • Recheck Cond: (payment_system = 'card'::text)
  • Filter: (id IS NOT NULL)
  • Heap Blocks: exact=110,240
16. 125.100 125.100 ↓ 1.0 977,508 1

Bitmap Index Scan on payments_payment_responses_payment_system_index (cost=0.00..32,910.81 rows=945,383 width=0) (actual time=125.100..125.100 rows=977,508 loops=1)

  • Index Cond: (payment_system = 'card'::text)
Planning time : 1.188 ms
Execution time : 208,746.884 ms