explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 9dwx

Settings
# exclusive inclusive rows x rows loops node
1. 0.031 21,875.294 ↓ 150.0 150 1

Limit (cost=9.52..168.71 rows=1 width=615) (actual time=44.631..21,875.294 rows=150 loops=1)

2. 0.286 21,875.263 ↓ 150.0 150 1

Nested Loop Left Join (cost=9.52..168.71 rows=1 width=615) (actual time=44.631..21,875.263 rows=150 loops=1)

  • Filter: (((COALESCE(rappi_credit_transactions.ends_at, rappi_credit_restrictions.ends_at) IS NULL) OR (COALESCE(rappi_credit_transactions.ends_at, rappi_credit_restrictions.ends_at) > now())) AND ((rappi_credit_restrictions.starts_at IS NULL) OR (rappi_credit_restrictions.starts_at < now())) AND ((rappi_credit_restrictions.ends_at IS NULL) OR (rappi_credit_restrictions.ends_at > now())))
  • Rows Removed by Filter: 4
3. 13,374.141 21,874.823 ↓ 9.1 154 1

Nested Loop Left Join (cost=8.95..157.92 rows=17 width=591) (actual time=44.612..21,874.823 rows=154 loops=1)

  • Join Filter: (debited_transactions.paid_id = rappi_credit_transactions.id)
  • Rows Removed by Join Filter: 196514813
  • Filter: (rappi_credit_transactions.amount > COALESCE(debited_transactions.amount, '0'::double precision))
  • Rows Removed by Filter: 13925
4. 2.976 25.124 ↓ 276.1 14,079 1

Nested Loop (cost=1.01..149.05 rows=51 width=583) (actual time=0.021..25.124 rows=14,079 loops=1)

5. 0.007 0.007 ↑ 1.0 1 1

Index Scan using rappi_credits_application_user_id_index_dba_20191006 on rappi_credits (cost=0.43..2.65 rows=1 width=5) (actual time=0.007..0.007 rows=1 loops=1)

  • Index Cond: (application_user_id = '307984336'::bigint)
6. 22.141 22.141 ↓ 117.3 14,079 1

Index Scan using rappi_credit_transactions_rappi_credit_id_index_dba on rappi_credit_transactions (cost=0.57..145.20 rows=120 width=582) (actual time=0.013..22.141 rows=14,079 loops=1)

  • Index Cond: (rappi_credit_id = rappi_credits.id)
  • Filter: (((state)::text = 'paid'::text) AND ((starts_at IS NULL) OR (starts_at < now())))
  • Rows Removed by Filter: 13386
7. 8,433.322 8,475.558 ↓ 13,959.0 13,959 14,079

Materialize (cost=7.94..7.98 rows=1 width=12) (actual time=0.003..0.602 rows=13,959 loops=14,079)

8. 1.174 42.236 ↓ 13,959.0 13,959 1

Subquery Scan on debited_transactions (cost=7.94..7.97 rows=1 width=12) (actual time=35.906..42.236 rows=13,959 loops=1)

9. 3.630 41.062 ↓ 13,959.0 13,959 1

GroupAggregate (cost=7.94..7.96 rows=1 width=16) (actual time=35.906..41.062 rows=13,959 loops=1)

  • Group Key: rcdo.rappi_credit_paid_transaction_id
10. 5.502 37.432 ↓ 15,938.0 15,938 1

Sort (cost=7.94..7.95 rows=1 width=12) (actual time=35.898..37.432 rows=15,938 loops=1)

  • Sort Key: rcdo.rappi_credit_paid_transaction_id
  • Sort Method: quicksort Memory: 1132kB
11. 8.909 31.930 ↓ 15,938.0 15,938 1

Nested Loop (cost=1.45..7.93 rows=1 width=12) (actual time=0.020..31.930 rows=15,938 loops=1)

12. 1.571 7.054 ↓ 15,967.0 15,967 1

Nested Loop (cost=1.01..7.45 rows=1 width=12) (actual time=0.014..7.054 rows=15,967 loops=1)

13. 0.003 0.003 ↑ 1.0 1 1

Index Scan using rappi_credits_application_user_id_index_dba_20191006 on rappi_credits rc (cost=0.43..2.65 rows=1 width=4) (actual time=0.001..0.003 rows=1 loops=1)

  • Index Cond: (application_user_id = '307984336'::bigint)
14. 5.480 5.480 ↓ 5,322.3 15,967 1

Index Scan using rappi_credit_transactions_rappi_credit_id_state_dba on rappi_credit_transactions rct (cost=0.57..4.77 rows=3 width=16) (actual time=0.012..5.480 rows=15,967 loops=1)

  • Index Cond: ((rappi_credit_id = rc.id) AND ((state)::text = 'debited'::text))
  • Filter: (order_id <> 58529974)
15. 15.967 15.967 ↑ 1.0 1 15,967

Index Scan using rappi_credit_debit_origins_rappi_credit_debit_transaction_id_in on rappi_credit_debit_origins rcdo (cost=0.44..0.47 rows=1 width=8) (actual time=0.001..0.001 rows=1 loops=15,967)

  • Index Cond: (rappi_credit_debit_transaction_id = rct.id)
16. 0.154 0.154 ↓ 0.0 0 154

Index Scan using rappi_credit_restrictions_pkey on rappi_credit_restrictions (cost=0.56..0.61 rows=1 width=20) (actual time=0.001..0.001 rows=0 loops=154)

  • Index Cond: (id = rappi_credit_transactions.rappi_credit_restriction_id)
Planning time : 0.800 ms
Execution time : 21,875.608 ms