explain.depesz.com

PostgreSQL's explain analyze made readable

Result: QWp2

Settings
# exclusive inclusive rows x rows loops node
1. 0.018 2,532.923 ↑ 1.0 50 1

Limit (cost=438,399.02..438,399.14 rows=50 width=742) (actual time=2,532.909..2,532.923 rows=50 loops=1)

2. 160.716 2,532.905 ↑ 976.4 150 1

Sort (cost=438,398.77..438,764.92 rows=146,462 width=742) (actual time=2,532.891..2,532.905 rows=150 loops=1)

  • Sort Key: t.transaction_date, t.transaction_time
  • Sort Method: top-N heapsort Memory: 112kB
3. 138.240 2,372.189 ↑ 1.0 145,592 1

Hash Left Join (cost=20,499.68..432,372.72 rows=146,462 width=742) (actual time=266.763..2,372.189 rows=145,592 loops=1)

  • Hash Cond: ((t.entity_number)::text = (cl_cards.card_number)::text)
4. 116.000 2,231.271 ↑ 1.0 145,592 1

Hash Left Join (cost=20,333.87..429,732.39 rows=146,462 width=726) (actual time=264.049..2,231.271 rows=145,592 loops=1)

  • Hash Cond: (t.pos_id = pos.id)
5. 418.095 2,111.749 ↑ 1.0 145,592 1

Hash Left Join (cost=20,077.41..426,912.85 rows=146,462 width=707) (actual time=260.497..2,111.749 rows=145,592 loops=1)

  • Hash Cond: ((t.entity_number)::text = (cards.card_number)::text)
6. 189.752 1,595.664 ↑ 1.0 145,592 1

Hash Left Join (cost=12,808.70..390,512.06 rows=146,462 width=685) (actual time=162.295..1,595.664 rows=145,592 loops=1)

  • Hash Cond: ((t.contract_id)::text = (con.id)::text)
7. 320.205 1,361.379 ↑ 1.0 145,592 1

Hash Left Join (cost=9,554.97..384,726.19 rows=146,462 width=683) (actual time=117.691..1,361.379 rows=145,592 loops=1)

  • Hash Cond: (t.id = rn_rrn.transaction_id)
8. 82.294 950.048 ↑ 1.0 145,592 1

Hash Left Join (cost=2,393.60..350,612.88 rows=146,462 width=679) (actual time=26.517..950.048 rows=145,592 loops=1)

  • Hash Cond: (t.user_modify = aum.user_id)
9. 75.800 867.551 ↑ 1.0 145,592 1

Hash Left Join (cost=2,368.43..348,573.85 rows=146,462 width=662) (actual time=26.305..867.551 rows=145,592 loops=1)

  • Hash Cond: (t.user_create = auc.user_id)
10. 77.608 791.417 ↑ 1.0 145,592 1

Hash Left Join (cost=2,343.25..346,534.82 rows=146,462 width=645) (actual time=25.963..791.417 rows=145,592 loops=1)

  • Hash Cond: (t.emitent = ac.company_id)
11. 165.064 713.796 ↑ 1.0 145,592 1

Nested Loop Left Join (cost=2,342.16..345,008.08 rows=146,462 width=371) (actual time=25.941..713.796 rows=145,592 loops=1)

  • Join Filter: (t.transaction_type = tt.id)
  • Rows Removed by Join Filter: 145,592
12. 59.576 548.732 ↑ 1.0 145,592 1

Hash Left Join (cost=2,342.16..340,613.20 rows=146,462 width=255) (actual time=25.931..548.732 rows=145,592 loops=1)

  • Hash Cond: ((t.written_off_nomenclature_id)::text = btrim(to_char((sonp.id_for_cards)::double precision, '9999999999'::text)))
13. 57.364 489.097 ↑ 1.0 145,592 1

Hash Left Join (cost=2,340.57..338,048.53 rows=146,462 width=255) (actual time=25.865..489.097 rows=145,592 loops=1)

  • Hash Cond: ((t.written_off_nomenclature_id)::text = btrim(to_char((sonw.id_for_cards)::double precision, '9999999999'::text)))
14. 273.662 431.641 ↑ 1.0 145,592 1

Hash Left Join (cost=2,338.99..335,483.86 rows=146,462 width=255) (actual time=25.752..431.641 rows=145,592 loops=1)

  • Hash Cond: ((t.client_id)::text = (c.id)::text)
15. 132.320 132.320 ↑ 1.0 145,592 1

Index Scan using transactions_new_transaction_date_idx on transactions_new t (cost=0.44..328,583.36 rows=146,462 width=317) (actual time=0.063..132.320 rows=145,592 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. 8.969 25.659 ↑ 1.0 27,180 1

Hash (cost=1,998.80..1,998.80 rows=27,180 width=62) (actual time=25.659..25.659 rows=27,180 loops=1)

  • Buckets: 4,096 Batches: 1 Memory Usage: 2,478kB
17. 16.690 16.690 ↑ 1.0 27,180 1

Seq Scan on customers c (cost=0.00..1,998.80 rows=27,180 width=62) (actual time=0.013..16.690 rows=27,180 loops=1)

18. 0.076 0.092 ↑ 1.0 26 1

Hash (cost=1.26..1.26 rows=26 width=2) (actual time=0.092..0.092 rows=26 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 1kB
19. 0.016 0.016 ↑ 1.0 26 1

Seq Scan on set_of_nomenclature sonw (cost=0.00..1.26 rows=26 width=2) (actual time=0.012..0.016 rows=26 loops=1)

20. 0.052 0.059 ↑ 1.0 26 1

Hash (cost=1.26..1.26 rows=26 width=2) (actual time=0.059..0.059 rows=26 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 1kB
21. 0.007 0.007 ↑ 1.0 26 1

Seq Scan on set_of_nomenclature sonp (cost=0.00..1.26 rows=26 width=2) (actual time=0.003..0.007 rows=26 loops=1)

22. 0.000 0.000 ↑ 1.0 2 145,592

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

23. 0.006 0.006 ↑ 1.0 2 1

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

24. 0.003 0.013 ↑ 1.0 4 1

Hash (cost=1.04..1.04 rows=4 width=282) (actual time=0.013..0.013 rows=4 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 1kB
25. 0.010 0.010 ↑ 1.0 4 1

Seq Scan on acl_companies ac (cost=0.00..1.04 rows=4 width=282) (actual time=0.009..0.010 rows=4 loops=1)

26. 0.080 0.334 ↑ 1.0 319 1

Hash (cost=21.19..21.19 rows=319 width=29) (actual time=0.334..0.334 rows=319 loops=1)

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

Seq Scan on acl_users auc (cost=0.00..21.19 rows=319 width=29) (actual time=0.011..0.254 rows=319 loops=1)

28. 0.079 0.203 ↑ 1.0 319 1

Hash (cost=21.19..21.19 rows=319 width=29) (actual time=0.203..0.203 rows=319 loops=1)

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

Seq Scan on acl_users aum (cost=0.00..21.19 rows=319 width=29) (actual time=0.003..0.124 rows=319 loops=1)

30. 37.793 91.126 ↑ 1.0 118,481 1

Hash (cost=5,024.15..5,024.15 rows=122,898 width=12) (actual time=91.126..91.126 rows=118,481 loops=1)

  • Buckets: 16,384 Batches: 2 Memory Usage: 2,780kB
31. 53.333 53.333 ↑ 1.0 118,481 1

Index Scan using rn_rrn_create_timestamp_idx on rn_rrn (cost=0.43..5,024.15 rows=122,898 width=12) (actual time=0.060..53.333 rows=118,481 loops=1)

  • Index Cond: (create_timestamp > '2020-08-01 00:00:00'::timestamp without time zone)
32. 20.638 44.533 ↑ 1.0 62,788 1

Hash (cost=2,468.88..2,468.88 rows=62,788 width=19) (actual time=44.533..44.533 rows=62,788 loops=1)

  • Buckets: 8,192 Batches: 1 Memory Usage: 3,151kB
33. 23.895 23.895 ↑ 1.0 62,788 1

Seq Scan on contracts con (cost=0.00..2,468.88 rows=62,788 width=19) (actual time=0.011..23.895 rows=62,788 loops=1)

34. 44.334 97.990 ↑ 1.0 141,898 1

Hash (cost=4,385.98..4,385.98 rows=141,898 width=33) (actual time=97.990..97.990 rows=141,898 loops=1)

  • Buckets: 8,192 Batches: 4 Memory Usage: 1,725kB
35. 53.656 53.656 ↑ 1.0 141,898 1

Seq Scan on cards (cost=0.00..4,385.98 rows=141,898 width=33) (actual time=0.011..53.656 rows=141,898 loops=1)

36. 1.320 3.522 ↑ 1.0 4,376 1

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

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

Seq Scan on pos (cost=0.00..201.76 rows=4,376 width=23) (actual time=0.012..2.202 rows=4,376 loops=1)

38. 1.188 2.678 ↑ 1.0 3,414 1

Hash (cost=123.14..123.14 rows=3,414 width=36) (actual time=2.678..2.678 rows=3,414 loops=1)

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

Seq Scan on cl_cards (cost=0.00..123.14 rows=3,414 width=36) (actual time=0.012..1.490 rows=3,414 loops=1)

Planning time : 10.939 ms