explain.depesz.com

PostgreSQL's explain analyze made readable

Result: AeLP

Settings
# exclusive inclusive rows x rows loops node
1. 0.003 390.027 ↓ 0.0 0 1

Sort (cost=251,418.93..251,419.16 rows=93 width=68) (actual time=390.027..390.027 rows=0 loops=1)

  • Sort Key: date_series.date
  • Sort Method: quicksort Memory: 25kB
  • ((date_series.year)::double precision)))
2. 0.002 390.024 ↓ 0.0 0 1

Merge Right Join (cost=246,410.95..251,415.89 rows=93 width=68) (actual time=390.024..390.024 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)) =
3. 0.000 196.972 ↓ 0.0 0 1

Finalize GroupAggregate (cost=135,388.00..139,585.02 rows=29,294 width=61) (actual time=196.972..196.972 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.242 197.040 ↓ 0.0 0 1

Gather Merge (cost=135,388.00..138,632.96 rows=24,412 width=61) (actual time=196.971..197.040 rows=0 loops=1)

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

Partial GroupAggregate (cost=134,387.97..134,815.18 rows=12,206 width=61) (actual time=190.798..190.798 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.028 190.797 ↓ 0.0 0 3 / 3

Sort (cost=134,387.97..134,418.49 rows=12,206 width=35) (actual time=190.797..190.797 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 190.769 ↓ 0.0 0 3 / 3

Nested Loop (cost=347.43..133,559.47 rows=12,206 width=35) (actual time=190.769..190.769 rows=0 loops=3)

8. 0.001 190.768 ↓ 0.0 0 3 / 3

Hash Join (cost=346.73..128,984.42 rows=1,528 width=32) (actual time=190.768..190.768 rows=0 loops=3)

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

Parallel Append (cost=0.70..128,605.16 rows=12,492 width=37) (actual time=190.767..190.767 rows=0 loops=3)

10. 190.766 190.766 ↓ 0.0 0 3 / 3

Parallel Index Scan using cashbasis_p1_company_id_tx_id_sequence_cash_date_arap_link__idx on cashbasis_p1 c (cost=0.70..128,542.70 rows=12,492 width=37) (actual time=190.766..190.766 rows=0 loops=3)

  • Index Cond: ((company_id = '109287618'::numeric) 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))
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.005 193.050 ↓ 0.0 0 1

Sort (cost=111,022.95..111,023.18 rows=93 width=21) (actual time=193.050..193.050 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.004 193.045 ↓ 0.0 0 1

Nested Loop (cost=110,925.89..111,019.91 rows=93 width=21) (actual time=193.045..193.045 rows=0 loops=1)

17. 0.376 0.376 ↑ 1.0 31 1

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

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

Materialize (cost=110,925.89..110,925.95 rows=3 width=5) (actual time=6.215..6.215 rows=0 loops=31)

19. 0.001 192.662 ↓ 0.0 0 1

Subquery Scan on entity (cost=110,925.89..110,925.93 rows=3 width=5) (actual time=192.661..192.662 rows=0 loops=1)

20. 0.001 192.661 ↓ 0.0 0 1

Limit (cost=110,925.89..110,925.90 rows=3 width=37) (actual time=192.661..192.661 rows=0 loops=1)

21. 0.002 192.660 ↓ 0.0 0 1

Sort (cost=110,925.89..110,931.44 rows=2,219 width=37) (actual time=192.660..192.660 rows=0 loops=1)

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

Finalize GroupAggregate (cost=110,623.57..110,897.21 rows=2,219 width=37) (actual time=192.658..192.658 rows=0 loops=1)

  • Group Key: t_1.item_id
23. 9.931 196.022 ↓ 0.0 0 1

Gather Merge (cost=110,623.57..110,855.60 rows=1,850 width=37) (actual time=192.657..196.022 rows=0 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
24. 0.001 186.091 ↓ 0.0 0 3 / 3

Partial GroupAggregate (cost=109,623.54..109,642.04 rows=925 width=37) (actual time=186.091..186.091 rows=0 loops=3)

  • Group Key: t_1.item_id
25. 0.016 186.090 ↓ 0.0 0 3 / 3

Sort (cost=109,623.54..109,625.85 rows=925 width=11) (actual time=186.090..186.090 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.000 186.074 ↓ 0.0 0 3 / 3

Nested Loop (cost=347.43..109,577.97 rows=925 width=11) (actual time=186.074..186.074 rows=0 loops=3)

27. 0.001 186.074 ↓ 0.0 0 3 / 3

Hash Join (cost=346.73..107,939.46 rows=558 width=24) (actual time=186.074..186.074 rows=0 loops=3)

  • Hash Cond: (c_1.account_id = a_1.account_id)
28. 0.001 186.073 ↓ 0.0 0 3 / 3

Parallel Append (cost=0.70..107,581.30 rows=4,560 width=29) (actual time=186.073..186.073 rows=0 loops=3)

29. 186.072 186.072 ↓ 0.0 0 3 / 3

Parallel Index Scan using cashbasis_p1_company_id_tx_id_sequence_cash_date_arap_link__idx on cashbasis_p1 c_1 (cost=0.70..107,558.50 rows=4,560 width=29) (actual time=186.072..186.072 rows=0 loops=3)

  • Index Cond: ((company_id = '109287618'::numeric) 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))
30. 0.000 0.000 ↓ 0.0 0 / 3

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

31. 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)
32. 0.000 0.000 ↓ 0.0 0 / 3

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

33. 0.000 0.000 ↓ 0.0 0 / 3

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

  • Index Cond: ((company_id = '109287618'::numeric) AND (tx_id = c_1.tx_id) AND (sequence = c_1.sequence))
  • Filter: ((is_no_post IS NULL) AND (item_id IS NOT NULL))
Planning time : 4.735 ms
Execution time : 393.666 ms