explain.depesz.com

PostgreSQL's explain analyze made readable

Result: e3PQ

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

Sort (cost=5,090,818.87..5,090,819.11 rows=93 width=68) (actual time=8,545.962..8,545.962 rows=0 loops=1)

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

Merge Right Join (cost=5,083,716.12..5,090,815.83 rows=93 width=68) (actual time=8,545.960..8,545.960 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,138.420 ↓ 0.0 0 1

Finalize GroupAggregate (cost=3,243,882.43..3,249,836.88 rows=41,561 width=61) (actual time=3,138.420..3,138.420 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. 5.266 3,138.489 ↓ 0.0 0 1

Gather Merge (cost=3,243,882.43..3,248,486.15 rows=34,634 width=61) (actual time=3,138.420..3,138.489 rows=0 loops=1)

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

Partial GroupAggregate (cost=3,242,882.40..3,243,488.50 rows=17,317 width=61) (actual time=3,133.223..3,133.223 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.026 3,133.222 ↓ 0.0 0 3 / 3

Sort (cost=3,242,882.40..3,242,925.70 rows=17,317 width=35) (actual time=3,133.222..3,133.222 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,133.196 ↓ 0.0 0 3 / 3

Nested Loop (cost=347.43..3,241,663.30 rows=17,317 width=35) (actual time=3,133.196..3,133.196 rows=0 loops=3)

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

Hash Join (cost=346.73..3,238,442.59 rows=1,053 width=32) (actual time=3,133.195..3,133.195 rows=0 loops=3)

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

Parallel Append (cost=0.70..3,238,073.66 rows=8,608 width=37) (actual time=3,133.194..3,133.194 rows=0 loops=3)

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

Parallel Index Scan using cashbasis_p1_pkey on cashbasis_p1 c (cost=0.70..3,238,030.62 rows=8,608 width=37) (actual time=3,133.192..3,133.192 rows=0 loops=3)

  • Index Cond: (company_id = 109287618.000000000)
  • 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: 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.015 5,407.537 ↓ 0.0 0 1

Sort (cost=1,839,833.70..1,839,833.93 rows=93 width=21) (actual time=5,407.537..5,407.537 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.020 5,407.522 ↓ 0.0 0 1

Nested Loop (cost=1,839,736.64..1,839,830.65 rows=93 width=21) (actual time=5,407.522..5,407.522 rows=0 loops=1)

17. 0.327 0.327 ↑ 1.0 31 1

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

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

Materialize (cost=1,839,736.64..1,839,736.69 rows=3 width=5) (actual time=174.425..174.425 rows=0 loops=31)

19. 0.001 5,407.185 ↓ 0.0 0 1

Subquery Scan on entity (cost=1,839,736.64..1,839,736.67 rows=3 width=5) (actual time=5,407.185..5,407.185 rows=0 loops=1)

20. 0.002 5,407.184 ↓ 0.0 0 1

Limit (cost=1,839,736.64..1,839,736.64 rows=3 width=37) (actual time=5,407.184..5,407.184 rows=0 loops=1)

21. 0.002 5,407.182 ↓ 0.0 0 1

Sort (cost=1,839,736.64..1,839,740.18 rows=1,416 width=37) (actual time=5,407.182..5,407.182 rows=0 loops=1)

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

Finalize GroupAggregate (cost=1,839,543.78..1,839,718.34 rows=1,416 width=37) (actual time=5,407.180..5,407.180 rows=0 loops=1)

  • Group Key: t_1.item_id
23. 9.630 5,410.669 ↓ 0.0 0 1

Gather Merge (cost=1,839,543.78..1,839,691.79 rows=1,180 width=37) (actual time=5,407.179..5,410.669 rows=0 loops=1)

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

Partial GroupAggregate (cost=1,838,543.76..1,838,555.56 rows=590 width=37) (actual time=5,401.039..5,401.039 rows=0 loops=3)

  • Group Key: t_1.item_id
25. 0.015 5,401.038 ↓ 0.0 0 3 / 3

Sort (cost=1,838,543.76..1,838,545.24 rows=590 width=11) (actual time=5,401.038..5,401.038 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,401.023 ↓ 0.0 0 3 / 3

Hash Join (cost=347.43..1,838,516.61 rows=590 width=11) (actual time=5,401.023..5,401.023 rows=0 loops=3)

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

Nested Loop (cost=1.40..1,838,157.76 rows=4,823 width=24) (actual time=5,401.022..5,401.022 rows=0 loops=3)

28. 42.988 579.074 ↓ 1.4 596,764 3 / 3

Parallel Append (cost=0.70..664,418.33 rows=416,645 width=23) (actual time=0.041..579.074 rows=596,764 loops=3)

29. 536.086 536.086 ↓ 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..662,335.11 rows=416,645 width=23) (actual time=0.040..536.086 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. 596.764 4,774.112 ↓ 0.0 0 1,790,292 / 3

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

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

Index Scan using cashbasis_p1_pkey on cashbasis_p1 c_1 (cost=0.70..2.80 rows=1 width=29) (actual time=0.007..0.007 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 : 2.461 ms
Execution time : 8,549.663 ms