explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 8nPs

Settings
# exclusive inclusive rows x rows loops node
1. 0.002 8,360.823 ↓ 0.0 0 1

Sort (cost=5,504,814.21..5,504,814.44 rows=93 width=68) (actual time=8,360.823..8,360.823 rows=0 loops=1)

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

Merge Right Join (cost=5,498,464.70..5,504,811.17 rows=93 width=68) (actual time=8,360.821..8,360.821 rows=0 loops=1)

  • Merge Cond: ((t.item_id = entity.item_id) AND ((date_part('day'::text, (date(c.cash_date))::timestamp without time zone)) = ((date_series.date_of_month)::double precision)) AND ((date_part('month'::text, (date(c.cash_date))::timestamp without time zone)) = ((date_series.month)::double precision)) AND ((date_part('year'::text, (date(c.cash_date))::timestamp without time zone)) = ((date_series.year)::double
3. 0.000 2,386.112 ↓ 0.0 0 1

Finalize GroupAggregate (cost=3,612,542.35..3,617,982.83 rows=32,860 width=61) (actual time=2,386.112..2,386.112 rows=0 loops=1)

  • Group Key: t.item_id, (date_part('day'::text, (date(c.cash_date))::timestamp without time zone)), (date_part('month'::text, (date(c.cash_date))::timestamp without time zone)), (date_part('year'::text, (date(c.cash_date))::timestamp without time zone))
4. 4.581 2,386.187 ↓ 0.0 0 1

Gather Merge (cost=3,612,542.35..3,616,421.97 rows=27,384 width=61) (actual time=2,386.111..2,386.187 rows=0 loops=1)

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

Partial GroupAggregate (cost=3,611,542.33..3,612,261.16 rows=13,692 width=61) (actual time=2,381.606..2,381.606 rows=0 loops=3)

  • Group Key: t.item_id, (date_part('day'::text, (date(c.cash_date))::timestamp without time zone)), (date_part('month'::text, (date(c.cash_date))::timestamp without time zone)), (date_part('year'::text, (date(c.cash_date))::timestamp without time zone))
6. 0.025 2,381.605 ↓ 0.0 0 3 / 3

Sort (cost=3,611,542.33..3,611,576.56 rows=13,692 width=35) (actual time=2,381.605..2,381.605 rows=0 loops=3)

  • Sort Key: t.item_id, (date_part('day'::text, (date(c.cash_date))::timestamp without time zone)), (date_part('month'::text, (date(c.cash_date))::timestamp without time zone)), (date_part('year'::text, (date(c.cash_date))::timestamp without time zone))
  • Sort Method: quicksort Memory: 25kB
  • Worker 0: Sort Method: quicksort Memory: 25kB
  • Worker 1: Sort Method: quicksort Memory: 25kB
7. 0.000 2,381.580 ↓ 0.0 0 3 / 3

Nested Loop (cost=347.43..3,610,601.61 rows=13,692 width=35) (actual time=2,381.580..2,381.580 rows=0 loops=3)

8. 0.001 2,381.580 ↓ 0.0 0 3 / 3

Hash Join (cost=346.73..3,607,850.94 rows=832 width=32) (actual time=2,381.580..2,381.580 rows=0 loops=3)

  • Hash Cond: (c.account_id = a.account_id)
9. 0.010 2,381.579 ↓ 0.0 0 3 / 3

Parallel Append (cost=0.70..3,607,486.82 rows=6,805 width=37) (actual time=2,381.579..2,381.579 rows=0 loops=3)

10. 2,381.569 2,381.569 ↓ 0.0 0 3 / 3

Parallel Index Scan using cashbasis_p1_company_id_tx_id_sequence_idx on cashbasis_p1 c (cost=0.70..3,607,452.79 rows=6,805 width=37) (actual time=2,381.569..2,381.569 rows=0 loops=3)

  • Index Cond: (company_id = 109287618.000000000)
  • Filter: ((date(cash_date) >= '2019-01-01'::date) AND (date(cash_date) <= '2019-01-31'::date))
  • Rows Removed by Filter: 1529103
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.000000000)
  • Filter: (account_type_id = '10'::numeric)
13. 0.000 0.000 ↓ 0.0 0 / 3

Append (cost=0.70..2.93 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.000000000) AND (tx_id = c.tx_id) AND (sequence = c.sequence))
  • Filter: (is_no_post IS NULL)
15. 0.011 5,974.707 ↓ 0.0 0 1

Sort (cost=1,885,922.35..1,885,922.58 rows=93 width=21) (actual time=5,974.707..5,974.707 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.000 5,974.696 ↓ 0.0 0 1

Nested Loop (cost=1,885,825.29..1,885,919.31 rows=93 width=21) (actual time=5,974.696..5,974.696 rows=0 loops=1)

17. 0.350 0.350 ↑ 1.0 31 1

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

  • Filter: ((date >= '2019-01-01'::date) AND (date <= '2019-01-31'::date))
  • Rows Removed by Filter: 3623
18. 0.014 5,974.351 ↓ 0.0 0 31

Materialize (cost=1,885,825.29..1,885,825.34 rows=3 width=5) (actual time=192.721..192.721 rows=0 loops=31)

19. 0.002 5,974.337 ↓ 0.0 0 1

Subquery Scan on entity (cost=1,885,825.29..1,885,825.33 rows=3 width=5) (actual time=5,974.336..5,974.337 rows=0 loops=1)

20. 0.001 5,974.335 ↓ 0.0 0 1

Limit (cost=1,885,825.29..1,885,825.30 rows=3 width=37) (actual time=5,974.335..5,974.335 rows=0 loops=1)

21. 0.002 5,974.334 ↓ 0.0 0 1

Sort (cost=1,885,825.29..1,885,828.86 rows=1,429 width=37) (actual time=5,974.334..5,974.334 rows=0 loops=1)

  • Sort Key: (sum((c_1.home_amount / '10000000'::numeric)))
  • Sort Method: quicksort Memory: 25kB
22. 0.000 5,974.332 ↓ 0.0 0 1

Finalize GroupAggregate (cost=1,885,390.88..1,885,806.82 rows=1,429 width=37) (actual time=5,974.331..5,974.332 rows=0 loops=1)

  • Group Key: t_1.item_id
23. 7.270 5,975.965 ↓ 0.0 0 1

Gather Merge (cost=1,885,390.88..1,885,767.52 rows=2,858 width=37) (actual time=5,974.330..5,975.965 rows=0 loops=1)

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

Partial GroupAggregate (cost=1,884,390.85..1,884,437.62 rows=1,429 width=37) (actual time=5,968.695..5,968.695 rows=0 loops=3)

  • Group Key: t_1.item_id
25. 0.024 5,968.694 ↓ 0.0 0 3 / 3

Sort (cost=1,884,390.85..1,884,398.08 rows=2,890 width=11) (actual time=5,968.694..5,968.694 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,968.670 ↓ 0.0 0 3 / 3

Hash Join (cost=347.43..1,884,224.72 rows=2,890 width=11) (actual time=5,968.670..5,968.670 rows=0 loops=3)

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

Nested Loop (cost=1.40..1,883,815.88 rows=23,619 width=24) (actual time=5,968.669..5,968.669 rows=0 loops=3)

28. 46.751 625.843 ↓ 1.4 596,764 3 / 3

Parallel Append (cost=0.70..669,356.23 rows=416,645 width=23) (actual time=0.063..625.843 rows=596,764 loops=3)

29. 579.092 579.092 ↓ 1.4 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..667,273.00 rows=416,645 width=23) (actual time=0.057..579.092 rows=596,764 loops=3)

  • Index Cond: ((company_id = 109287618.000000000) AND (is_no_post IS NULL) AND (item_id IS NOT NULL))
  • Heap Fetches: 18
30. 596.764 5,370.876 ↓ 0.0 0 1,790,292 / 3

Append (cost=0.70..2.90 rows=1 width=29) (actual time=0.009..0.009 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_company_id_tx_id_sequence_idx on cashbasis_p1 c_1 (cost=0.70..2.90 rows=1 width=29) (actual time=0.008..0.008 rows=0 loops=1,790,292)

  • Index Cond: ((company_id = 109287618.000000000) AND (tx_id = t_1.tx_id) AND (sequence = t_1.sequence))
  • Filter: ((date(cash_date) >= '2020-01-01'::date) AND (date(cash_date) <= '2020-01-29'::date))
  • 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.000000000)
  • Filter: (account_type_id = '10'::numeric)
Planning time : 2.931 ms
Execution time : 8,362.730 ms