explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 9j2n

Settings
# exclusive inclusive rows x rows loops node
1. 0.031 98.824 ↑ 1.0 93 1

Sort (cost=12,903.34..12,903.58 rows=93 width=68) (actual time=98.820..98.824 rows=93 loops=1)

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

Merge Right Join (cost=12,584.43..12,900.30 rows=93 width=68) (actual time=98.240..98.793 rows=93 loops=1)

  • Merge Cond: ((t.item_id = entity.item_id) AND ((date_part('day'::text, c.cash_date)) = ((date_series.date_of_month)::double precision)) AND ((date_part('month'::text, c.cash_date)) = ((date_series.month)::double precision)) AND ((date_part('year'::text, c.cash_date)) = ((date_series.year)::double precision)))
3. 0.000 75.490 ↑ 22.4 82 1

Finalize GroupAggregate (cost=9,213.90..9,476.94 rows=1,836 width=61) (actual time=74.994..75.490 rows=82 loops=1)

  • Group Key: t.item_id, (date_part('day'::text, c.cash_date)), (date_part('month'::text, c.cash_date)), (date_part('year'::text, c.cash_date))
4. 4.998 75.597 ↑ 7.0 218 1

Gather Merge (cost=9,213.90..9,417.27 rows=1,530 width=61) (actual time=74.975..75.597 rows=218 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
5. 1.035 70.599 ↑ 6.6 116 3 / 3

Partial GroupAggregate (cost=8,213.87..8,240.65 rows=765 width=61) (actual time=69.403..70.599 rows=116 loops=3)

  • Group Key: t.item_id, (date_part('day'::text, c.cash_date)), (date_part('month'::text, c.cash_date)), (date_part('year'::text, c.cash_date))
6. 3.781 69.564 ↓ 4.1 3,126 3 / 3

Sort (cost=8,213.87..8,215.79 rows=765 width=35) (actual time=69.382..69.564 rows=3,126 loops=3)

  • Sort Key: t.item_id, (date_part('day'::text, c.cash_date)), (date_part('month'::text, c.cash_date)), (date_part('year'::text, c.cash_date))
  • Sort Method: quicksort Memory: 372kB
  • Worker 0: Sort Method: quicksort Memory: 387kB
  • Worker 1: Sort Method: quicksort Memory: 574kB
7. 4.451 65.783 ↓ 5.3 4,044 3 / 3

Nested Loop (cost=366.43..8,177.23 rows=765 width=35) (actual time=5.504..65.783 rows=4,044 loops=3)

8. 2.620 16.844 ↓ 14.7 4,044 3 / 3

Hash Join (cost=365.73..7,363.83 rows=275 width=32) (actual time=5.472..16.844 rows=4,044 loops=3)

  • Hash Cond: (c.account_id = a.account_id)
9. 0.817 13.471 ↓ 3.4 8,958 3 / 3

Parallel Append (cost=129.63..7,120.71 rows=2,632 width=37) (actual time=4.632..13.471 rows=8,958 loops=3)

10. 9.936 12.654 ↓ 3.4 8,958 3 / 3

Parallel Bitmap Heap Scan on cashbasis_p6 c (cost=129.63..7,107.55 rows=2,632 width=37) (actual time=4.631..12.654 rows=8,958 loops=3)

  • Recheck Cond: ((company_id = 178348139.000000000) AND (cash_date >= '2019-01-01 00:00:00'::timestamp without time zone) AND (cash_date <= '2019-01-31 00:00:00'::timestamp without time zone))
  • Heap Blocks: exact=2163
11. 2.718 2.718 ↓ 4.3 26,873 1 / 3

Bitmap Index Scan on cashbasis_p6_company_id_cash_date_tx_id_sequence_idx (cost=0.00..128.05 rows=6,316 width=0) (actual time=8.153..8.153 rows=26,873 loops=1)

  • Index Cond: ((company_id = 178348139.000000000) AND (cash_date >= '2019-01-01 00:00:00'::timestamp without time zone) AND (cash_date <= '2019-01-31 00:00:00'::timestamp without time zone))
12. 0.029 0.753 ↓ 3.3 99 3 / 3

Hash (cost=235.72..235.72 rows=30 width=13) (actual time=0.753..0.753 rows=99 loops=3)

  • Buckets: 1024 Batches: 1 Memory Usage: 13kB
13. 0.724 0.724 ↓ 3.3 99 3 / 3

Index Scan using accounts_pk on accounts a (cost=0.56..235.72 rows=30 width=13) (actual time=0.037..0.724 rows=99 loops=3)

  • Index Cond: (company_id = 178348139.000000000)
  • Filter: (account_type_id = '10'::numeric)
  • Rows Removed by Filter: 283
14. 4.044 44.488 ↑ 1.0 1 12,133 / 3

Append (cost=0.70..2.93 rows=1 width=23) (actual time=0.011..0.011 rows=1 loops=12,133)

15. 40.443 40.443 ↑ 1.0 1 12,133 / 3

Index Scan using txdetails_p6_pkey on txdetails_p6 t (cost=0.70..2.92 rows=1 width=23) (actual time=0.010..0.010 rows=1 loops=12,133)

  • Index Cond: ((company_id = 178348139.000000000) AND (tx_id = c.tx_id) AND (sequence = c.sequence))
  • Filter: (is_no_post IS NULL)
16. 0.087 23.243 ↑ 1.0 93 1

Sort (cost=3,370.54..3,370.77 rows=93 width=21) (actual time=23.239..23.243 rows=93 loops=1)

  • Sort Key: entity.item_id, ((date_series.date_of_month)::double precision), ((date_series.month)::double precision), ((date_series.year)::double precision)
  • Sort Method: quicksort Memory: 32kB
17. 0.009 23.156 ↑ 1.0 93 1

Nested Loop (cost=3,273.48..3,367.49 rows=93 width=21) (actual time=22.971..23.156 rows=93 loops=1)

18. 0.269 0.269 ↑ 1.0 31 1

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

  • Filter: ((date >= '2019-01-01'::date) AND (date <= '2019-01-31'::date))
  • Rows Removed by Filter: 3623
19. 0.019 22.878 ↑ 1.0 3 31

Materialize (cost=3,273.48..3,273.53 rows=3 width=5) (actual time=0.737..0.738 rows=3 loops=31)

20. 0.001 22.859 ↑ 1.0 3 1

Subquery Scan on entity (cost=3,273.48..3,273.51 rows=3 width=5) (actual time=22.855..22.859 rows=3 loops=1)

21. 0.005 22.858 ↑ 1.0 3 1

Limit (cost=3,273.48..3,273.48 rows=3 width=37) (actual time=22.854..22.858 rows=3 loops=1)

22. 0.008 22.853 ↑ 40.7 3 1

Sort (cost=3,273.48..3,273.78 rows=122 width=37) (actual time=22.852..22.853 rows=3 loops=1)

  • Sort Key: (sum(c_1.home_amount))
  • Sort Method: top-N heapsort Memory: 25kB
23. 0.294 22.845 ↑ 9.4 13 1

GroupAggregate (cost=3,269.46..3,271.90 rows=122 width=37) (actual time=22.647..22.845 rows=13 loops=1)

  • Group Key: t_1.item_id
24. 0.393 22.551 ↓ 7.6 932 1

Sort (cost=3,269.46..3,269.77 rows=122 width=11) (actual time=22.491..22.551 rows=932 loops=1)

  • Sort Key: t_1.item_id
  • Sort Method: quicksort Memory: 68kB
25. 0.604 22.158 ↓ 7.6 932 1

Nested Loop (cost=237.50..3,265.23 rows=122 width=11) (actual time=0.660..22.158 rows=932 loops=1)

26. 1.437 12.962 ↓ 4.9 1,074 1

Hash Join (cost=236.80..2,621.92 rows=219 width=24) (actual time=0.639..12.962 rows=1,074 loops=1)

  • Hash Cond: (c_1.account_id = a_1.account_id)
27. 0.794 10.948 ↓ 5.0 10,396 1

Append (cost=0.70..2,380.24 rows=2,096 width=29) (actual time=0.054..10.948 rows=10,396 loops=1)

28. 10.154 10.154 ↓ 5.0 10,396 1

Index Scan using cashbasis_p6_company_id_cash_date_tx_id_sequence_idx on cashbasis_p6 c_1 (cost=0.70..2,369.76 rows=2,096 width=29) (actual time=0.053..10.154 rows=10,396 loops=1)

  • Index Cond: ((company_id = 178348139.000000000) AND (cash_date >= '2020-01-01 00:00:00'::timestamp without time zone) AND (cash_date <= '2020-01-29 00:00:00'::timestamp without time zone))
29. 0.030 0.577 ↓ 3.3 99 1

Hash (cost=235.72..235.72 rows=30 width=13) (actual time=0.577..0.577 rows=99 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 13kB
30. 0.547 0.547 ↓ 3.3 99 1

Index Scan using accounts_pk on accounts a_1 (cost=0.56..235.72 rows=30 width=13) (actual time=0.037..0.547 rows=99 loops=1)

  • Index Cond: (company_id = 178348139.000000000)
  • Filter: (account_type_id = '10'::numeric)
  • Rows Removed by Filter: 283
31. 0.000 8.592 ↑ 1.0 1 1,074

Append (cost=0.70..2.93 rows=1 width=23) (actual time=0.008..0.008 rows=1 loops=1,074)

32. 8.592 8.592 ↑ 1.0 1 1,074

Index Scan using txdetails_p6_pkey on txdetails_p6 t_1 (cost=0.70..2.92 rows=1 width=23) (actual time=0.007..0.008 rows=1 loops=1,074)

  • Index Cond: ((company_id = 178348139.000000000) AND (tx_id = c_1.tx_id) AND (sequence = c_1.sequence))
  • Filter: ((is_no_post IS NULL) AND (item_id IS NOT NULL))
  • Rows Removed by Filter: 0
Planning time : 44.630 ms
Execution time : 99.209 ms