explain.depesz.com

PostgreSQL's explain analyze made readable

Result: jDNu

Settings
# exclusive inclusive rows x rows loops node
1. 0.000 0.000 ↓ 0.0

Limit (cost=1,624,062.51..1,624,183.80 rows=10 width=150) (actual rows= loops=)

2.          

CTE test

3. 0.000 0.000 ↓ 0.0

GroupAggregate (cost=1,611,184.74..1,620,619.00 rows=171,532 width=151) (actual rows= loops=)

  • Group Key: jms.bookingday, jms.acquireraccountid, jms.accountid, jms.txvariantid, jms.sdp
4.          

Initplan (for GroupAggregate)

5. 0.000 0.000 ↓ 0.0

Index Scan using journaltype_journaltypecode_key on journaltype (cost=0.27..2.29 rows=1 width=2) (actual rows= loops=)

  • Index Cond: ((journaltypecode)::text = 'Settled'::text)
6. 0.000 0.000 ↓ 0.0

Sort (cost=1,611,182.45..1,611,611.28 rows=171,532 width=33) (actual rows= loops=)

  • Sort Key: jms.acquireraccountid, jms.accountid, jms.txvariantid, jms.sdp
7. 0.000 0.000 ↓ 0.0

Index Scan using jms_bd on journalmonthlysummaries jms (cost=3,496.61..1,596,269.36 rows=171,532 width=33) (actual rows= loops=)

  • Index Cond: (bookingday = '2019-06-01'::date)
  • Filter: ((journaltypeid = $5) AND ((hashed SubPlan 2) OR (hashed SubPlan 4)))
8.          

SubPlan (for Index Scan)

9. 0.000 0.000 ↓ 0.0

HashAggregate (cost=3,021.30..3,030.04 rows=874 width=8) (actual rows= loops=)

  • Group Key: maa.merchantaccountid
10.          

Initplan (for HashAggregate)

11. 0.000 0.000 ↓ 0.0

Index Scan using account_accountcode on account (cost=0.42..2.44 rows=1 width=8) (actual rows= loops=)

  • Index Cond: ((accountcode)::text = 'Dotpay'::text)
12. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.85..3,016.67 rows=874 width=8) (actual rows= loops=)

13. 0.000 0.000 ↓ 0.0

Index Scan using account_parentid on account account_1 (cost=0.42..460.14 rows=493 width=8) (actual rows= loops=)

  • Index Cond: (parentid = $0)
14. 0.000 0.000 ↓ 0.0

Index Only Scan using merchantacquireraccount_aama on merchantacquireraccount maa (cost=0.43..4.95 rows=24 width=16) (actual rows= loops=)

  • Index Cond: (acquireraccountid = account_1.accountid)
15. 0.000 0.000 ↓ 0.0

Index Scan using account_parentid on account a (cost=2.87..462.58 rows=493 width=8) (actual rows= loops=)

  • Index Cond: (parentid = $3)
16.          

Initplan (for Index Scan)

17. 0.000 0.000 ↓ 0.0

Index Scan using account_accountcode on account account_2 (cost=0.42..2.44 rows=1 width=8) (actual rows= loops=)

  • Index Cond: ((accountcode)::text = 'Dotpay'::text)
18. 0.000 0.000 ↓ 0.0

Hash Join (cost=3,443.51..25,761.07 rows=1,840 width=150) (actual rows= loops=)

  • Hash Cond: ((t2.acquireraccountid = t1.accountid) AND (t2.accountid = t1.acquireraccountid))
19. 0.000 0.000 ↓ 0.0

CTE Scan on test t2 (cost=0.00..3,430.64 rows=85,766 width=80) (actual rows= loops=)

  • Filter: (NOT sdp)
20. 0.000 0.000 ↓ 0.0

Hash (cost=3,430.64..3,430.64 rows=858 width=86) (actual rows= loops=)

21. 0.000 0.000 ↓ 0.0

CTE Scan on test t1 (cost=0.00..3,430.64 rows=858 width=86) (actual rows= loops=)

  • Filter: (sdp IS NULL)