explain.depesz.com

PostgreSQL's explain analyze made readable

Result: ySv7

Settings
# exclusive inclusive rows x rows loops node
1. 0.011 6,923.591 ↑ 1.0 50 1

Limit (cost=336,078.22..336,078.35 rows=50 width=742) (actual time=6,923.583..6,923.591 rows=50 loops=1)

2. 252.739 6,923.580 ↑ 586.1 150 1

Sort (cost=336,077.97..336,297.76 rows=87,915 width=742) (actual time=6,923.571..6,923.580 rows=150 loops=1)

  • Sort Key: t.transaction_date, t.transaction_time
  • Sort Method: top-N heapsort Memory: 108kB
3. 153.128 6,670.841 ↓ 1.7 150,145 1

Hash Left Join (cost=76,293.68..332,460.79 rows=87,915 width=742) (actual time=1,915.043..6,670.841 rows=150,145 loops=1)

  • Hash Cond: ((t.entity_number)::text = (cl_cards.card_number)::text)
4. 138.942 6,515.292 ↓ 1.7 150,145 1

Hash Left Join (cost=76,124.14..330,805.58 rows=87,915 width=726) (actual time=1,912.589..6,515.292 rows=150,145 loops=1)

  • Hash Cond: (t.pos_id = pos.id)
5. 523.762 6,367.466 ↓ 1.7 150,145 1

Hash Left Join (cost=75,818.68..328,961.61 rows=87,915 width=707) (actual time=1,903.672..6,367.466 rows=150,145 loops=1)

  • Hash Cond: ((t.entity_number)::text = (cards.card_number)::text)
6. 225.095 5,707.167 ↓ 1.7 150,145 1

Hash Left Join (cost=65,993.61..301,207.03 rows=87,915 width=685) (actual time=1,766.699..5,707.167 rows=150,145 loops=1)

  • Hash Cond: ((t.contract_id)::text = (con.id)::text)
7. 442.144 5,368.868 ↓ 1.7 150,145 1

Hash Left Join (cost=61,146.33..294,838.35 rows=87,915 width=683) (actual time=1,653.438..5,368.868 rows=150,145 loops=1)

  • Hash Cond: (t.id = rn_rrn.transaction_id)
8. 97.213 3,330.830 ↓ 1.7 150,145 1

Hash Left Join (cost=2,536.04..220,107.23 rows=87,915 width=679) (actual time=57.508..3,330.830 rows=150,145 loops=1)

  • Hash Cond: (t.user_modify = aum.user_id)
9. 89.352 3,233.391 ↓ 1.7 150,145 1

Hash Left Join (cost=2,493.87..218,856.22 rows=87,915 width=662) (actual time=57.269..3,233.391 rows=150,145 loops=1)

  • Hash Cond: (t.user_create = auc.user_id)
10. 99.039 3,140.067 ↓ 1.7 150,145 1

Hash Left Join (cost=2,451.69..217,605.21 rows=87,915 width=645) (actual time=53.279..3,140.067 rows=150,145 loops=1)

  • Hash Cond: (t.emitent = ac.company_id)
11. 220.328 3,039.568 ↓ 1.7 150,145 1

Nested Loop Left Join (cost=2,448.42..216,393.11 rows=87,915 width=371) (actual time=51.799..3,039.568 rows=150,145 loops=1)

  • Join Filter: (t.transaction_type = tt.id)
  • Rows Removed by Join Filter: 150,145
12. 66.329 2,819.240 ↓ 1.7 150,145 1

Hash Left Join (cost=2,448.42..213,754.63 rows=87,915 width=255) (actual time=46.000..2,819.240 rows=150,145 loops=1)

  • Hash Cond: ((t.written_off_nomenclature_id)::text = btrim(to_char((sonp.id_for_cards)::double precision, '9999999999'::text)))
13. 68.260 2,752.839 ↓ 1.7 150,145 1

Hash Left Join (cost=2,446.90..212,214.60 rows=87,915 width=255) (actual time=45.920..2,752.839 rows=150,145 loops=1)

  • Hash Cond: ((t.written_off_nomenclature_id)::text = btrim(to_char((sonw.id_for_cards)::double precision, '9999999999'::text)))
14. 355.074 2,682.374 ↓ 1.7 150,145 1

Hash Left Join (cost=2,445.38..210,674.57 rows=87,915 width=255) (actual time=43.702..2,682.374 rows=150,145 loops=1)

  • Hash Cond: ((t.client_id)::text = (c.id)::text)
15. 2,284.161 2,284.161 ↓ 1.7 150,145 1

Index Scan using transactions_new_transaction_date_idx on transactions_new t (cost=0.44..205,491.77 rows=87,915 width=317) (actual time=0.516..2,284.161 rows=150,145 loops=1)

  • Index Cond: ((transaction_date >= '2020-08-02'::date) AND (transaction_date <= '2020-09-02'::date) AND (transaction_date >= '2020-08-02'::date) AND (transaction_date <= '2020-09-02'::date))
16. 10.304 43.139 ↑ 1.0 27,182 1

Hash (cost=2,104.42..2,104.42 rows=27,242 width=62) (actual time=43.139..43.139 rows=27,182 loops=1)

  • Buckets: 4,096 Batches: 1 Memory Usage: 2,479kB
17. 32.835 32.835 ↑ 1.0 27,182 1

Seq Scan on customers c (cost=0.00..2,104.42 rows=27,242 width=62) (actual time=0.503..32.835 rows=27,182 loops=1)

18. 2.194 2.205 ↓ 1.1 26 1

Hash (cost=1.23..1.23 rows=23 width=2) (actual time=2.205..2.205 rows=26 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 1kB
19. 0.011 0.011 ↓ 1.1 26 1

Seq Scan on set_of_nomenclature sonw (cost=0.00..1.23 rows=23 width=2) (actual time=0.005..0.011 rows=26 loops=1)

20. 0.062 0.072 ↓ 1.1 26 1

Hash (cost=1.23..1.23 rows=23 width=2) (actual time=0.072..0.072 rows=26 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 1kB
21. 0.010 0.010 ↓ 1.1 26 1

Seq Scan on set_of_nomenclature sonp (cost=0.00..1.23 rows=23 width=2) (actual time=0.004..0.010 rows=26 loops=1)

22. 0.000 0.000 ↑ 1.0 2 150,145

Materialize (cost=0.00..1.03 rows=2 width=120) (actual time=0.000..0.000 rows=2 loops=150,145)

23. 5.785 5.785 ↑ 1.0 2 1

Seq Scan on set_of_transaction_types tt (cost=0.00..1.02 rows=2 width=120) (actual time=5.781..5.785 rows=2 loops=1)

24. 0.007 1.460 ↑ 3.0 4 1

Hash (cost=3.12..3.12 rows=12 width=282) (actual time=1.460..1.460 rows=4 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 1kB
25. 1.453 1.453 ↑ 3.0 4 1

Seq Scan on acl_companies ac (cost=0.00..3.12 rows=12 width=282) (actual time=0.005..1.453 rows=4 loops=1)

26. 0.108 3.972 ↑ 1.0 319 1

Hash (cost=38.19..38.19 rows=319 width=29) (actual time=3.972..3.972 rows=319 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 20kB
27. 3.864 3.864 ↑ 1.0 319 1

Seq Scan on acl_users auc (cost=0.00..38.19 rows=319 width=29) (actual time=0.007..3.864 rows=319 loops=1)

28. 0.098 0.226 ↑ 1.0 319 1

Hash (cost=38.19..38.19 rows=319 width=29) (actual time=0.226..0.226 rows=319 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 20kB
29. 0.128 0.128 ↑ 1.0 319 1

Seq Scan on acl_users aum (cost=0.00..38.19 rows=319 width=29) (actual time=0.004..0.128 rows=319 loops=1)

30. 69.234 1,595.894 ↓ 1.4 122,160 1

Hash (cost=57,094.26..57,094.26 rows=87,202 width=12) (actual time=1,595.894..1,595.894 rows=122,160 loops=1)

  • Buckets: 16,384 Batches: 2 (originally 1) Memory Usage: 4,097kB
31. 1,526.660 1,526.660 ↓ 1.4 122,160 1

Seq Scan on rn_rrn (cost=0.00..57,094.26 rows=87,202 width=12) (actual time=1,416.668..1,526.660 rows=122,160 loops=1)

  • Filter: (create_timestamp > '2020-08-01 00:00:00'::timestamp without time zone)
  • Rows Removed by Filter: 2,175,085
32. 30.499 113.204 ↓ 1.0 62,804 1

Hash (cost=4,062.68..4,062.68 rows=62,768 width=19) (actual time=113.204..113.204 rows=62,804 loops=1)

  • Buckets: 8,192 Batches: 1 Memory Usage: 3,152kB
33. 82.705 82.705 ↓ 1.0 62,804 1

Seq Scan on contracts con (cost=0.00..4,062.68 rows=62,768 width=19) (actual time=0.972..82.705 rows=62,804 loops=1)

34. 67.352 136.537 ↓ 1.0 141,958 1

Hash (cost=6,948.03..6,948.03 rows=141,603 width=33) (actual time=136.537..136.537 rows=141,958 loops=1)

  • Buckets: 8,192 Batches: 4 Memory Usage: 1,726kB
35. 69.185 69.185 ↓ 1.0 141,958 1

Seq Scan on cards (cost=0.00..6,948.03 rows=141,603 width=33) (actual time=0.011..69.185 rows=141,958 loops=1)

36. 1.397 8.884 ↑ 1.0 4,376 1

Hash (cost=250.76..250.76 rows=4,376 width=23) (actual time=8.884..8.884 rows=4,376 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 238kB
37. 7.487 7.487 ↑ 1.0 4,376 1

Seq Scan on pos (cost=0.00..250.76 rows=4,376 width=23) (actual time=1.267..7.487 rows=4,376 loops=1)

38. 1.273 2.421 ↑ 1.0 3,414 1

Hash (cost=125.91..125.91 rows=3,491 width=36) (actual time=2.421..2.421 rows=3,414 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 229kB
39. 1.148 1.148 ↑ 1.0 3,414 1

Seq Scan on cl_cards (cost=0.00..125.91 rows=3,491 width=36) (actual time=0.007..1.148 rows=3,414 loops=1)

Planning time : 24.329 ms