explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 9ITI

Settings
# exclusive inclusive rows x rows loops node
1. 0.020 105.332 ↑ 1.0 93 1

Sort (cost=12,444.40..12,444.63 rows=93 width=68) (actual time=105.328..105.332 rows=93 loops=1)

  • Sort Key: date_series.date
  • Sort Method: quicksort Memory: 29kB
2. 0.061 105.312 ↑ 1.0 93 1

Hash Left Join (cost=12,345.87..12,441.36 rows=93 width=68) (actual time=105.062..105.312 rows=93 loops=1)

  • Hash Cond: (((date_series.month)::double precision = base.month) AND ((date_series.year)::double precision = base.year) AND ((date_series.date_of_month)::double precision = base.date) AND (entity.item_id = base.entity_id))
3. 0.017 50.874 ↑ 1.0 93 1

Nested Loop (cost=3,497.80..3,591.82 rows=93 width=21) (actual time=50.666..50.874 rows=93 loops=1)

4. 0.296 0.296 ↑ 1.0 31 1

Seq Scan on date_series (cost=0.00..92.81 rows=31 width=16) (actual time=0.119..0.296 rows=31 loops=1)

  • Filter: ((date >= '2019-01-01'::date) AND (date <= '2019-01-31'::date))
  • Rows Removed by Filter: 3623
5. 0.014 50.561 ↑ 1.0 3 31

Materialize (cost=3,497.80..3,497.85 rows=3 width=5) (actual time=1.631..1.631 rows=3 loops=31)

6. 0.001 50.547 ↑ 1.0 3 1

Subquery Scan on entity (cost=3,497.80..3,497.84 rows=3 width=5) (actual time=50.541..50.547 rows=3 loops=1)

7. 0.006 50.546 ↑ 1.0 3 1

Limit (cost=3,497.80..3,497.81 rows=3 width=37) (actual time=50.540..50.546 rows=3 loops=1)

8. 0.022 50.540 ↑ 27.0 3 1

Sort (cost=3,497.80..3,498.00 rows=81 width=37) (actual time=50.539..50.540 rows=3 loops=1)

  • Sort Key: (sum(c.home_amount))
  • Sort Method: top-N heapsort Memory: 25kB
9. 0.892 50.518 ↓ 1.1 90 1

GroupAggregate (cost=3,495.13..3,496.75 rows=81 width=37) (actual time=49.417..50.518 rows=90 loops=1)

  • Group Key: t.item_id
10. 1.695 49.626 ↓ 62.8 5,089 1

Sort (cost=3,495.13..3,495.34 rows=81 width=11) (actual time=49.403..49.626 rows=5,089 loops=1)

  • Sort Key: t.item_id
  • Sort Method: quicksort Memory: 431kB
11. 3.039 47.931 ↓ 62.8 5,089 1

Nested Loop (cost=347.43..3,492.56 rows=81 width=11) (actual time=1.282..47.931 rows=5,089 loops=1)

12. 1.519 9.059 ↓ 19.9 5,119 1

Hash Join (cost=346.73..2,737.63 rows=257 width=24) (actual time=0.828..9.059 rows=5,119 loops=1)

  • Hash Cond: (c.account_id = a.account_id)
13. 6.778 6.778 ↓ 3.3 6,979 1

Append (cost=0.70..2,386.01 rows=2,101 width=29) (actual time=0.053..6.778 rows=6,979 loops=1)

  • -> Index Scan using cashbasis_p5_company_id_cash_date_tx_id_sequence_idx on cashbasis_p5 c (cost=0.70..2375.50 rows=2101 width=29) (actual time=0.053..6.296 rows=6979
  • Index Cond: ((company_id = 490808020.000000000) AND (cash_date >= '2020-01-01 00:00:00'::timestamp without time zone) AND (cash_date <= '2020-01-29 00:00:00'::time
14. 0.009 0.762 ↑ 1.6 27 1

Hash (cost=345.48..345.48 rows=44 width=13) (actual time=0.762..0.762 rows=27 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
15. 0.753 0.753 ↑ 1.6 27 1

Index Scan using accounts_pk on accounts a (cost=0.56..345.48 rows=44 width=13) (actual time=0.026..0.753 rows=27 loops=1)

  • Index Cond: (company_id = 490808020.000000000)
  • Filter: (account_type_id = '10'::numeric)
  • Rows Removed by Filter: 501
16. 0.000 35.833 ↑ 1.0 1 5,119

Append (cost=0.70..2.93 rows=1 width=23) (actual time=0.007..0.007 rows=1 loops=5,119)

17. 35.833 35.833 ↑ 1.0 1 5,119

Index Scan using txdetails_p5_pkey on txdetails_p5 t (cost=0.70..2.92 rows=1 width=23) (actual time=0.006..0.007 rows=1 loops=5,119)

  • Index Cond: ((company_id = 490808020.000000000) AND (tx_id = c.tx_id) AND (sequence = c.sequence))
  • Filter: ((is_no_post IS NULL) AND (item_id IS NOT NULL))
  • Rows Removed by Filter: 0
18. 0.196 54.377 ↑ 1.9 603 1

Hash (cost=8,825.71..8,825.71 rows=1,118 width=61) (actual time=54.377..54.377 rows=603 loops=1)

  • Buckets: 2048 Batches: 1 Memory Usage: 57kB
19. 0.070 54.181 ↑ 1.8 618 1

Subquery Scan on base (cost=8,654.30..8,825.71 rows=1,118 width=61) (actual time=51.222..54.181 rows=618 loops=1)

20. 0.765 54.111 ↑ 1.8 618 1

Finalize GroupAggregate (cost=8,654.30..8,814.53 rows=1,118 width=61) (actual time=51.220..54.111 rows=618 loops=1)

  • Group Key: t_1.item_id, (date_part('day'::text, c_1.cash_date)), (date_part('month'::text, c_1.cash_date)), (date_part('year'::text, c_1.cash_date))
21. 5.677 53.346 ↓ 1.7 1,585 1

Gather Merge (cost=8,654.30..8,778.19 rows=932 width=61) (actual time=51.208..53.346 rows=1,585 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
22. 1.165 47.669 ↓ 1.1 528 3 / 3

Partial GroupAggregate (cost=7,654.28..7,670.59 rows=466 width=61) (actual time=46.345..47.669 rows=528 loops=3)

  • Group Key: t_1.item_id, (date_part('day'::text, c_1.cash_date)), (date_part('month'::text, c_1.cash_date)), (date_part('year'::text, c_1.cash_date))
23. 2.946 46.504 ↓ 8.1 3,770 3 / 3

Sort (cost=7,654.28..7,655.44 rows=466 width=35) (actual time=46.329..46.504 rows=3,770 loops=3)

  • Sort Key: t_1.item_id, (date_part('day'::text, c_1.cash_date)), (date_part('month'::text, c_1.cash_date)), (date_part('year'::text, c_1.cash_date))
  • Sort Method: quicksort Memory: 400kB
  • Worker 0: Sort Method: quicksort Memory: 386kB
  • Worker 1: Sort Method: quicksort Memory: 388kB
24. 1.808 43.558 ↓ 8.1 3,770 3 / 3

Nested Loop (cost=463.00..7,633.62 rows=466 width=35) (actual time=2.262..43.558 rows=3,770 loops=3)

25. 1.197 7.820 ↓ 13.0 3,770 3 / 3

Hash Join (cost=462.30..6,778.37 rows=290 width=32) (actual time=2.233..7.820 rows=3,770 loops=3)

  • Hash Cond: (c_1.account_id = a_1.account_id)
26. 0.364 5.672 ↓ 2.0 4,719 3 / 3

Parallel Append (cost=116.28..6,426.02 rows=2,375 width=37) (actual time=1.249..5.672 rows=4,719 loops=3)

27. 4.299 5.308 ↓ 2.0 4,719 3 / 3

Parallel Bitmap Heap Scan on cashbasis_p5 c_1 (cost=116.28..6,414.14 rows=2,375 width=37) (actual time=1.248..5.308 rows=4,719 loops=3)

  • Recheck Cond: ((company_id = 490808020.000000000) AND (cash_date >= '2019-01-01 00:00:00'::timestamp without time zone) AND (cash_date <= '2019-01-31 00:00:00'::timestam
  • Heap Blocks: exact=1600
28. 1.009 1.009 ↓ 2.5 14,157 1 / 3

Bitmap Index Scan on cashbasis_p5_company_id_cash_date_tx_id_sequence_idx (cost=0.00..114.85 rows=5,700 width=0) (actual time=3.028..3.028 rows=14,157 loops=1)

  • Index Cond: ((company_id = 490808020.000000000) AND (cash_date >= '2019-01-01 00:00:00'::timestamp without time zone) AND (cash_date <= '2019-01-31 00:00:00'::time
29. 0.009 0.951 ↑ 1.6 27 3 / 3

Hash (cost=345.48..345.48 rows=44 width=13) (actual time=0.951..0.951 rows=27 loops=3)

  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
30. 0.942 0.942 ↑ 1.6 27 3 / 3

Index Scan using accounts_pk on accounts a_1 (cost=0.56..345.48 rows=44 width=13) (actual time=0.034..0.942 rows=27 loops=3)

  • Index Cond: (company_id = 490808020.000000000)
  • Filter: (account_type_id = '10'::numeric)
  • Rows Removed by Filter: 501
31. 3.770 33.930 ↑ 1.0 1 11,310 / 3

Append (cost=0.70..2.93 rows=1 width=23) (actual time=0.009..0.009 rows=1 loops=11,310)

32. 30.160 30.160 ↑ 1.0 1 11,310 / 3

Index Scan using txdetails_p5_pkey on txdetails_p5 t_1 (cost=0.70..2.92 rows=1 width=23) (actual time=0.008..0.008 rows=1 loops=11,310)

  • Index Cond: ((company_id = 490808020.000000000) AND (tx_id = c_1.tx_id) AND (sequence = c_1.sequence))
  • Filter: (is_no_post IS NULL)
Planning time : 4.778 ms
Execution time : 105.796 ms