explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 3Ny

Settings
# exclusive inclusive rows x rows loops node
1. 0.003 8,906.564 ↓ 0.0 0 1

Sort (cost=6,102,163.40..6,102,163.63 rows=93 width=68) (actual time=8,906.564..8,906.564 rows=0 loops=1)

  • Sort Key: date_series.date
  • Sort Method: quicksort Memory: 25kB
2. 0.002 8,906.561 ↓ 0.0 0 1

Merge Right Join (cost=6,097,155.85..6,102,160.35 rows=93 width=68) (actual time=8,906.561..8,906.561 rows=0 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 3,442.017 ↓ 0.0 0 1

Finalize GroupAggregate (cost=4,466,163.83..4,470,360.49 rows=29,291 width=61) (actual time=3,442.017..3,442.017 rows=0 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. 6.253 3,442.085 ↓ 0.0 0 1

Gather Merge (cost=4,466,163.83..4,469,408.52 rows=24,410 width=61) (actual time=3,442.016..3,442.085 rows=0 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
5. 0.001 3,435.832 ↓ 0.0 0 3 / 3

Partial GroupAggregate (cost=4,465,163.80..4,465,590.98 rows=12,205 width=61) (actual time=3,435.832..3,435.832 rows=0 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. 0.031 3,435.831 ↓ 0.0 0 3 / 3

Sort (cost=4,465,163.80..4,465,194.31 rows=12,205 width=35) (actual time=3,435.831..3,435.831 rows=0 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: 25kB
  • Worker 0: Sort Method: quicksort Memory: 25kB
  • Worker 1: Sort Method: quicksort Memory: 25kB
7. 0.001 3,435.800 ↓ 0.0 0 3 / 3

Nested Loop (cost=347.43..4,464,335.38 rows=12,205 width=35) (actual time=3,435.800..3,435.800 rows=0 loops=3)

8. 0.001 3,435.799 ↓ 0.0 0 3 / 3

Hash Join (cost=346.73..4,459,760.33 rows=1,528 width=32) (actual time=3,435.799..3,435.799 rows=0 loops=3)

  • Hash Cond: (c.account_id = a.account_id)
9. 0.001 3,435.798 ↓ 0.0 0 3 / 3

Parallel Append (cost=0.70..4,459,381.08 rows=12,492 width=37) (actual time=3,435.798..3,435.798 rows=0 loops=3)

10. 3,435.797 3,435.797 ↓ 0.0 0 3 / 3

Parallel Index Scan using cashbasis_p1_pkey on cashbasis_p1 c (cost=0.70..4,459,318.62 rows=12,492 width=37) (actual time=3,435.796..3,435.797 rows=0 loops=3)

  • Index Cond: (company_id = '109287618'::numeric)
  • Filter: ((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))
  • Rows Removed by Filter: 1,529,103
11. 0.000 0.000 ↓ 0.0 0 / 3

Hash (cost=345.48..345.48 rows=44 width=13) (never executed)

12. 0.000 0.000 ↓ 0.0 0 / 3

Index Scan using accounts_pk on accounts a (cost=0.56..345.48 rows=44 width=13) (never executed)

  • Index Cond: (company_id = '109287618'::numeric)
  • Filter: (account_type_id = '10'::numeric)
13. 0.000 0.000 ↓ 0.0 0 / 3

Append (cost=0.70..2.92 rows=1 width=23) (never executed)

14. 0.000 0.000 ↓ 0.0 0 / 3

Index Scan using txdetails_p1_pkey on txdetails_p1 t (cost=0.70..2.92 rows=1 width=23) (never executed)

  • Index Cond: ((company_id = '109287618'::numeric) AND (tx_id = c.tx_id) AND (sequence = c.sequence))
  • Filter: (is_no_post IS NULL)
15. 0.013 5,464.542 ↓ 0.0 0 1

Sort (cost=1,630,992.03..1,630,992.26 rows=93 width=21) (actual time=5,464.542..5,464.542 rows=0 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: 25kB
16. 0.006 5,464.529 ↓ 0.0 0 1

Nested Loop (cost=1,630,894.97..1,630,988.99 rows=93 width=21) (actual time=5,464.529..5,464.529 rows=0 loops=1)

17. 0.339 0.339 ↑ 1.0 31 1

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

  • Filter: ((date >= '2019-01-01'::date) AND (date <= '2019-01-31'::date))
  • Rows Removed by Filter: 3,623
18. 0.013 5,464.184 ↓ 0.0 0 31

Materialize (cost=1,630,894.97..1,630,895.02 rows=3 width=5) (actual time=176.264..176.264 rows=0 loops=31)

19. 0.001 5,464.171 ↓ 0.0 0 1

Subquery Scan on entity (cost=1,630,894.97..1,630,895.01 rows=3 width=5) (actual time=5,464.171..5,464.171 rows=0 loops=1)

20. 0.002 5,464.170 ↓ 0.0 0 1

Limit (cost=1,630,894.97..1,630,894.98 rows=3 width=37) (actual time=5,464.170..5,464.170 rows=0 loops=1)

21. 0.003 5,464.168 ↓ 0.0 0 1

Sort (cost=1,630,894.97..1,630,900.52 rows=2,219 width=37) (actual time=5,464.168..5,464.168 rows=0 loops=1)

  • Sort Key: (sum(c_1.home_amount))
  • Sort Method: quicksort Memory: 25kB
22. 0.000 5,464.165 ↓ 0.0 0 1

Finalize GroupAggregate (cost=1,630,592.64..1,630,866.29 rows=2,219 width=37) (actual time=5,464.165..5,464.165 rows=0 loops=1)

  • Group Key: t_1.item_id
23. 10.561 5,467.716 ↓ 0.0 0 1

Gather Merge (cost=1,630,592.64..1,630,824.68 rows=1,850 width=37) (actual time=5,464.164..5,467.716 rows=0 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
24. 0.001 5,457.155 ↓ 0.0 0 3 / 3

Partial GroupAggregate (cost=1,629,592.62..1,629,611.12 rows=925 width=37) (actual time=5,457.155..5,457.155 rows=0 loops=3)

  • Group Key: t_1.item_id
25. 0.017 5,457.154 ↓ 0.0 0 3 / 3

Sort (cost=1,629,592.62..1,629,594.93 rows=925 width=11) (actual time=5,457.154..5,457.154 rows=0 loops=3)

  • Sort Key: t_1.item_id
  • Sort Method: quicksort Memory: 25kB
  • Worker 0: Sort Method: quicksort Memory: 25kB
  • Worker 1: Sort Method: quicksort Memory: 25kB
26. 0.001 5,457.137 ↓ 0.0 0 3 / 3

Hash Join (cost=347.43..1,629,547.04 rows=925 width=11) (actual time=5,457.137..5,457.137 rows=0 loops=3)

  • Hash Cond: (c_1.account_id = a_1.account_id)
27. 106.316 5,457.136 ↓ 0.0 0 3 / 3

Nested Loop (cost=1.40..1,629,180.92 rows=7,556 width=24) (actual time=5,457.136..5,457.136 rows=0 loops=3)

28. 45.369 576.708 ↓ 1.6 596,764 3 / 3

Parallel Append (cost=0.70..588,868.82 rows=367,457 width=23) (actual time=0.035..576.708 rows=596,764 loops=3)

29. 531.339 531.339 ↓ 1.6 596,764 3 / 3

Parallel Index Only Scan using txdetails_p1_company_id_date_tx_date_is_no_post_tx_id_seque_idx on txdetails_p1 t_1 (cost=0.70..587,031.53 rows=367,457 width=23) (actual time=0.034..531.339 rows=596,764 loops=3)

  • Index Cond: ((company_id = '109287618'::numeric) AND (is_no_post IS NULL) AND (item_id IS NOT NULL))
  • Heap Fetches: 18
30. 0.000 4,774.112 ↓ 0.0 0 1,790,292 / 3

Append (cost=0.70..2.82 rows=1 width=29) (actual time=0.008..0.008 rows=0 loops=1,790,292)

31. 4,774.112 4,774.112 ↓ 0.0 0 1,790,292 / 3

Index Scan using cashbasis_p1_pkey on cashbasis_p1 c_1 (cost=0.70..2.82 rows=1 width=29) (actual time=0.008..0.008 rows=0 loops=1,790,292)

  • Index Cond: ((company_id = '109287618'::numeric) AND (tx_id = t_1.tx_id) AND (sequence = t_1.sequence))
  • Filter: ((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))
  • Rows Removed by Filter: 1
32. 0.000 0.000 ↓ 0.0 0 / 3

Hash (cost=345.48..345.48 rows=44 width=13) (never executed)

33. 0.000 0.000 ↓ 0.0 0 / 3

Index Scan using accounts_pk on accounts a_1 (cost=0.56..345.48 rows=44 width=13) (never executed)

  • Index Cond: (company_id = '109287618'::numeric)
  • Filter: (account_type_id = '10'::numeric)
Planning time : 4.946 ms
Execution time : 8,910.389 ms