explain.depesz.com

PostgreSQL's explain analyze made readable

Result: BI6q

Settings
# exclusive inclusive rows x rows loops node
1. 0.024 78.656 ↑ 1.0 93 1

Sort (cost=14,532.05..14,532.28 rows=93 width=68) (actual time=78.652..78.656 rows=93 loops=1)

  • Sort Key: date_series.date
  • Sort Method: quicksort Memory: 29kB
2.          

CTE base

3. 0.000 58.699 ↑ 12.1 171 1

Finalize GroupAggregate (cost=10,226.26..10,522.68 rows=2,070 width=61) (actual time=57.161..58.699 rows=171 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))
4. 5.832 59.653 ↑ 4.2 412 1

Gather Merge (cost=10,226.26..10,455.42 rows=1,724 width=61) (actual time=57.141..59.653 rows=412 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
5. 0.988 53.821 ↑ 6.3 137 3 / 3

Partial GroupAggregate (cost=9,226.23..9,256.40 rows=862 width=61) (actual time=52.666..53.821 rows=137 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))
6. 2.676 52.833 ↓ 4.7 4,044 3 / 3

Sort (cost=9,226.23..9,228.39 rows=862 width=35) (actual time=52.648..52.833 rows=4,044 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: 408kB
  • Worker 0: Sort Method: quicksort Memory: 414kB
  • Worker 1: Sort Method: quicksort Memory: 416kB
7. 1.122 50.157 ↓ 4.7 4,044 3 / 3

Nested Loop (cost=382.16..9,184.20 rows=862 width=35) (actual time=4.053..50.157 rows=4,044 loops=3)

8. 1.746 12.636 ↓ 13.0 4,044 3 / 3

Hash Join (cost=381.46..8,267.30 rows=310 width=32) (actual time=4.023..12.636 rows=4,044 loops=3)

  • Hash Cond: (c_1.account_id = a_1.account_id)
9. 0.626 10.020 ↓ 3.0 8,958 3 / 3

Parallel Append (cost=145.37..8,023.30 rows=2,966 width=37) (actual time=2.930..10.020 rows=8,958 loops=3)

10. 7.541 9.394 ↓ 3.0 8,958 3 / 3

Parallel Bitmap Heap Scan on cashbasis_p6 c_1 (cost=145.37..8,008.47 rows=2,966 width=37) (actual time=2.930..9.394 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=2382
11. 1.853 1.853 ↓ 3.8 26,873 1 / 3

Bitmap Index Scan on cashbasis_p6_company_id_cash_date_tx_id_sequence_idx (cost=0.00..143.59 rows=7,119 width=0) (actual time=5.559..5.559 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.022 0.870 ↓ 3.3 99 3 / 3

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

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

Index Scan using accounts_pk on accounts a_1 (cost=0.56..235.72 rows=30 width=13) (actual time=0.033..0.848 rows=99 loops=3)

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

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

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

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

  • Index Cond: ((company_id = 178348139.000000000) AND (tx_id = c_1.tx_id) AND (sequence = c_1.sequence))
  • Filter: (is_no_post IS NULL)
16. 0.076 78.632 ↑ 1.0 93 1

Hash Right Join (cost=3,780.74..4,006.33 rows=93 width=68) (actual time=76.977..78.632 rows=93 loops=1)

  • Hash Cond: ((base.month = (date_series.month)::double precision) AND (base.year = (date_series.year)::double precision) AND (base.date = (date_series.date_of_month)::double precision) AND (base.entity_id = entity.item_id))
17. 58.753 58.753 ↑ 12.1 171 1

CTE Scan on base (cost=0.00..41.40 rows=2,070 width=86) (actual time=57.162..58.753 rows=171 loops=1)

18. 0.029 19.803 ↑ 1.0 93 1

Hash (cost=3,778.88..3,778.88 rows=93 width=21) (actual time=19.803..19.803 rows=93 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 14kB
19. 0.011 19.774 ↑ 1.0 93 1

Nested Loop (cost=3,684.87..3,778.88 rows=93 width=21) (actual time=19.598..19.774 rows=93 loops=1)

20. 0.264 0.264 ↑ 1.0 31 1

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

  • Filter: ((date >= '2019-01-01'::date) AND (date <= '2019-01-31'::date))
  • Rows Removed by Filter: 3623
21. 0.012 19.499 ↑ 1.0 3 31

Materialize (cost=3,684.87..3,684.92 rows=3 width=5) (actual time=0.629..0.629 rows=3 loops=31)

22. 0.002 19.487 ↑ 1.0 3 1

Subquery Scan on entity (cost=3,684.87..3,684.90 rows=3 width=5) (actual time=19.483..19.487 rows=3 loops=1)

23. 0.003 19.485 ↑ 1.0 3 1

Limit (cost=3,684.87..3,684.87 rows=3 width=37) (actual time=19.483..19.485 rows=3 loops=1)

24. 0.005 19.482 ↑ 46.3 3 1

Sort (cost=3,684.87..3,685.21 rows=139 width=37) (actual time=19.482..19.482 rows=3 loops=1)

  • Sort Key: (sum(c.home_amount))
  • Sort Method: top-N heapsort Memory: 25kB
25. 0.162 19.477 ↑ 10.7 13 1

GroupAggregate (cost=3,680.29..3,683.07 rows=139 width=37) (actual time=19.360..19.477 rows=13 loops=1)

  • Group Key: t.item_id
26. 0.250 19.315 ↓ 6.7 932 1

Sort (cost=3,680.29..3,680.64 rows=139 width=11) (actual time=19.276..19.315 rows=932 loops=1)

  • Sort Key: t.item_id
  • Sort Method: quicksort Memory: 68kB
27. 0.597 19.065 ↓ 6.7 932 1

Nested Loop (cost=237.50..3,675.34 rows=139 width=11) (actual time=0.576..19.065 rows=932 loops=1)

28. 1.200 10.950 ↓ 4.3 1,074 1

Hash Join (cost=236.80..2,943.90 rows=249 width=24) (actual time=0.542..10.950 rows=1,074 loops=1)

  • Hash Cond: (c.account_id = a.account_id)
29. 0.779 9.261 ↓ 4.4 10,396 1

Append (cost=0.70..2,701.46 rows=2,379 width=29) (actual time=0.047..9.261 rows=10,396 loops=1)

30. 8.482 8.482 ↓ 4.4 10,396 1

Index Scan using cashbasis_p6_company_id_cash_date_tx_id_sequence_idx on cashbasis_p6 c (cost=0.70..2,689.57 rows=2,379 width=29) (actual time=0.047..8.482 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))
31. 0.021 0.489 ↓ 3.3 99 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 13kB
32. 0.468 0.468 ↓ 3.3 99 1

Index Scan using accounts_pk on accounts a (cost=0.56..235.72 rows=30 width=13) (actual time=0.028..0.468 rows=99 loops=1)

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

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

34. 7.518 7.518 ↑ 1.0 1 1,074

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

  • Index Cond: ((company_id = 178348139.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
Planning time : 43.630 ms
Execution time : 80.061 ms