explain.depesz.com

PostgreSQL's explain analyze made readable

Result: pjl1

Settings
# exclusive inclusive rows x rows loops node
1. 1.718 2,205.795 ↑ 1.0 41 1

Limit (cost=83,843.64..83,843.74 rows=41 width=7,032) (actual time=2,204.076..2,205.795 rows=41 loops=1)

2. 0.297 2,204.077 ↑ 1,653.2 41 1

Sort (cost=83,843.64..84,013.09 rows=67,781 width=7,032) (actual time=2,204.074..2,204.077 rows=41 loops=1)

  • Sort Key: t.date DESC, t.id
  • Sort Method: top-N heapsort Memory: 36kB
3. 564.444 2,203.780 ↑ 329.0 206 1

Hash Left Join (cost=7,287.76..81,689.03 rows=67,781 width=7,032) (actual time=68.478..2,203.780 rows=206 loops=1)

  • Hash Cond: (ta.user_id = tu.id)
  • Filter: ((t.by_id = '39551'::bigint) OR (bp.received_by_id = '39551'::bigint) OR ((fa.user_id IS NOT NULL) AND (fa.account_type_id = '4'::bigint) AND ((bp.id IS NULL) OR (bp.authorization_status IS NULL) OR ((bp.authorization_status)::text = 'AUTHORIZED'::text)) AND (alternatives: SubPlan 1 or hashed SubPlan 2)) OR ((ta.user_id IS NOT NULL) AND (ta.account_type_id = '4'::bigint) AND ((bp.id IS NULL) OR (bp.authorization_status IS NULL) OR ((bp.authorization_status)::text = 'AUTHORIZED'::text)) AND (alternatives: SubPlan 3 or hashed SubPlan 4)))
  • Rows Removed by Filter: 959,908
4. 553.835 1,629.462 ↓ 10.4 960,114 1

Hash Left Join (cost=5,412.55..79,494.56 rows=92,648 width=7,110) (actual time=48.883..1,629.462 rows=960,114 loops=1)

  • Hash Cond: (t.to_id = ta.id)
  • Filter: (((t.subclass)::text <> 'SCHEDULED_PAYMENT'::text) OR (ta.user_id <> '39550'::bigint) OR t.show_to_receiver)
5. 507.339 1,067.170 ↓ 10.4 960,114 1

Hash Join (cost=3,993.32..77,832.08 rows=92,648 width=7,094) (actual time=40.235..1,067.170 rows=960,114 loops=1)

  • Hash Cond: (ttfat.currency_id = c.id)
6. 0.000 559.802 ↓ 10.4 960,114 1

Gather (cost=3,992.30..77,330.18 rows=92,648 width=7,102) (actual time=40.197..559.802 rows=960,114 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
7. 177.247 1,462.529 ↓ 8.3 320,038 3 / 3

Parallel Hash Left Join (cost=2,992.30..67,065.38 rows=38,603 width=7,102) (actual time=28.399..1,462.529 rows=320,038 loops=3)

  • Hash Cond: (fa.user_id = fu.id)
8. 254.149 1,275.837 ↓ 8.3 320,038 3 / 3

Nested Loop Left Join (cost=1,421.88..65,393.62 rows=38,603 width=7,094) (actual time=18.441..1,275.837 rows=320,038 loops=3)

9. 140.650 701.650 ↓ 8.3 320,038 3 / 3

Hash Join (cost=1,421.45..42,271.49 rows=38,603 width=7,056) (actual time=18.420..701.650 rows=320,038 loops=3)

  • Hash Cond: (tt.from_account_type_id = ttfat.id)
10. 214.545 560.969 ↓ 8.3 320,038 3 / 3

Hash Left Join (cost=1,420.37..42,126.13 rows=38,603 width=7,056) (actual time=18.367..560.969 rows=320,038 loops=3)

  • Hash Cond: (t.from_id = fa.id)
  • Filter: (((fa.user_id = '39550'::bigint) OR (t.authorization_type IS NULL) OR ((t.authorization_type)::text <> 'OPERATOR'::text)) AND (((t.subclass)::text <> 'TICKET'::text) OR (fa.user_id IS NULL) OR (fa.user_id <> '39550'::bigint)))
11. 131.909 335.803 ↓ 8.0 320,038 3 / 3

Hash Join (cost=1.14..40,601.91 rows=39,990 width=7,040) (actual time=7.472..335.803 rows=320,038 loops=3)

  • Hash Cond: (t.type_id = tt.id)
12. 203.867 203.867 ↑ 1.2 320,038 3 / 3

Parallel Seq Scan on transactions t (cost=0.00..39,106.14 rows=399,901 width=7,032) (actual time=7.422..203.867 rows=320,038 loops=3)

  • Filter: ((subclass)::text = ANY ('{PAYMENT,ORDER_PAYMENT,SCHEDULED_PAYMENT,RECURRING_PAYMENT,IMPORT,CHARGEBACK,PAYMENT_REQUEST}'::text[]))
13. 0.006 0.027 ↓ 5.0 5 3 / 3

Hash (cost=1.12..1.12 rows=1 width=16) (actual time=0.027..0.027 rows=5 loops=3)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
14. 0.021 0.021 ↓ 5.0 5 3 / 3

Seq Scan on transfer_types tt (cost=0.00..1.12 rows=1 width=16) (actual time=0.019..0.021 rows=5 loops=3)

  • Filter: ((subclass)::text = 'PAYMENT'::text)
  • Rows Removed by Filter: 5
15. 4.893 10.621 ↓ 1.0 32,900 3 / 3

Hash (cost=1,007.99..1,007.99 rows=32,899 width=24) (actual time=10.621..10.621 rows=32,900 loops=3)

  • Buckets: 65,536 Batches: 1 Memory Usage: 2,312kB
16. 5.728 5.728 ↓ 1.0 32,900 3 / 3

Seq Scan on accounts fa (cost=0.00..1,007.99 rows=32,899 width=24) (actual time=0.015..5.728 rows=32,900 loops=3)

17. 0.009 0.031 ↑ 1.0 4 3 / 3

Hash (cost=1.04..1.04 rows=4 width=16) (actual time=0.031..0.031 rows=4 loops=3)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
18. 0.022 0.022 ↑ 1.0 4 3 / 3

Seq Scan on account_types ttfat (cost=0.00..1.04 rows=4 width=16) (actual time=0.021..0.022 rows=4 loops=3)

19. 320.038 320.038 ↑ 1.0 1 960,114 / 3

Index Scan using transactions_pkey on transactions bp (cost=0.42..0.60 rows=1 width=38) (actual time=0.001..0.001 rows=1 loops=960,114)

  • Index Cond: (t.id = id)
20. 4.108 9.445 ↑ 1.8 10,967 3 / 3

Parallel Hash (cost=1,328.52..1,328.52 rows=19,352 width=16) (actual time=9.445..9.445 rows=10,967 loops=3)

  • Buckets: 65,536 Batches: 1 Memory Usage: 2,112kB
21. 5.337 5.337 ↑ 1.8 10,967 3 / 3

Parallel Seq Scan on users fu (cost=0.00..1,328.52 rows=19,352 width=16) (actual time=0.014..5.337 rows=10,967 loops=3)

22. 0.005 0.029 ↑ 1.0 1 1

Hash (cost=1.01..1.01 rows=1 width=8) (actual time=0.029..0.029 rows=1 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
23. 0.024 0.024 ↑ 1.0 1 1

Seq Scan on currencies c (cost=0.00..1.01 rows=1 width=8) (actual time=0.023..0.024 rows=1 loops=1)

  • Filter: (network_id = '1'::bigint)
24. 3.770 8.457 ↓ 1.0 32,900 1

Hash (cost=1,007.99..1,007.99 rows=32,899 width=24) (actual time=8.457..8.457 rows=32,900 loops=1)

  • Buckets: 65,536 Batches: 1 Memory Usage: 2,312kB
25. 4.687 4.687 ↓ 1.0 32,900 1

Seq Scan on accounts ta (cost=0.00..1,007.99 rows=32,899 width=24) (actual time=0.004..4.687 rows=32,900 loops=1)

26. 3.426 9.865 ↓ 1.0 32,900 1

Hash (cost=1,463.98..1,463.98 rows=32,898 width=16) (actual time=9.865..9.865 rows=32,900 loops=1)

  • Buckets: 65,536 Batches: 1 Memory Usage: 2,055kB
27. 6.439 6.439 ↓ 1.0 32,900 1

Seq Scan on users tu (cost=0.00..1,463.98 rows=32,898 width=16) (actual time=0.005..6.439 rows=32,900 loops=1)

28.          

SubPlan (for Hash Left Join)

29. 0.000 0.000 ↓ 0.0 0

Bitmap Heap Scan on brokerings t3 (cost=8.65..12.67 rows=1 width=0) (never executed)

  • Recheck Cond: ((user_id = fu.id) AND (broker_id = '39550'::bigint))
  • Filter: ((end_date IS NULL) OR (end_date > CURRENT_TIMESTAMP))
30. 0.000 0.000 ↓ 0.0 0

BitmapAnd (cost=8.65..8.65 rows=1 width=0) (never executed)

31. 0.000 0.000 ↓ 0.0 0

Bitmap Index Scan on ix_brokerings_fk_brokerings_user_id (cost=0.00..4.20 rows=6 width=0) (never executed)

  • Index Cond: (user_id = fu.id)
32. 0.000 0.000 ↓ 0.0 0

Bitmap Index Scan on ix_brokerings_fk_brokerings_broker_id (cost=0.00..4.20 rows=6 width=0) (never executed)

  • Index Cond: (broker_id = '39550'::bigint)
33. 0.004 0.007 ↓ 2.5 5 1

Bitmap Heap Scan on brokerings t3_1 (cost=4.20..13.70 rows=2 width=8) (actual time=0.006..0.007 rows=5 loops=1)

  • Recheck Cond: (broker_id = '39550'::bigint)
  • Filter: ((end_date IS NULL) OR (end_date > CURRENT_TIMESTAMP))
  • Heap Blocks: exact=1
34. 0.003 0.003 ↑ 1.2 5 1

Bitmap Index Scan on ix_brokerings_fk_brokerings_broker_id (cost=0.00..4.20 rows=6 width=0) (actual time=0.003..0.003 rows=5 loops=1)

  • Index Cond: (broker_id = '39550'::bigint)
35. 0.000 0.000 ↓ 0.0 0

Bitmap Heap Scan on brokerings t4 (cost=8.65..12.67 rows=1 width=0) (never executed)

  • Recheck Cond: ((user_id = tu.id) AND (broker_id = '39550'::bigint))
  • Filter: ((end_date IS NULL) OR (end_date > CURRENT_TIMESTAMP))
36. 0.000 0.000 ↓ 0.0 0

BitmapAnd (cost=8.65..8.65 rows=1 width=0) (never executed)

37. 0.000 0.000 ↓ 0.0 0

Bitmap Index Scan on ix_brokerings_fk_brokerings_user_id (cost=0.00..4.20 rows=6 width=0) (never executed)

  • Index Cond: (user_id = tu.id)
38. 0.000 0.000 ↓ 0.0 0

Bitmap Index Scan on ix_brokerings_fk_brokerings_broker_id (cost=0.00..4.20 rows=6 width=0) (never executed)

  • Index Cond: (broker_id = '39550'::bigint)
39. 0.001 0.002 ↓ 2.5 5 1

Bitmap Heap Scan on brokerings t4_1 (cost=4.20..13.70 rows=2 width=8) (actual time=0.002..0.002 rows=5 loops=1)

  • Recheck Cond: (broker_id = '39550'::bigint)
  • Filter: ((end_date IS NULL) OR (end_date > CURRENT_TIMESTAMP))
  • Heap Blocks: exact=1
40. 0.001 0.001 ↑ 1.2 5 1

Bitmap Index Scan on ix_brokerings_fk_brokerings_broker_id (cost=0.00..4.20 rows=6 width=0) (actual time=0.001..0.001 rows=5 loops=1)

  • Index Cond: (broker_id = '39550'::bigint)
Planning time : 22.233 ms
Execution time : 2,206.289 ms