explain.depesz.com

PostgreSQL's explain analyze made readable

Result: qxBy

Settings
# exclusive inclusive rows x rows loops node
1. 0.002 4,222.317 ↓ 0.0 0 1

Sort (cost=12,906,136.23..12,906,136.46 rows=93 width=68) (actual time=4,222.316..4,222.317 rows=0 loops=1)

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

Merge Right Join (cost=12,889,091.11..12,906,133.19 rows=93 width=68) (actual time=4,222.314..4,222.315 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'::tex
3. 0.000 3,891.118 ↓ 0.0 0 1

Finalize GroupAggregate (cost=9,911,996.77..9,926,609.33 rows=88,261 width=61) (actual time=3,891.118..3,891.118 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.525 3,891.187 ↓ 0.0 0 1

Gather Merge (cost=9,911,996.77..9,922,416.95 rows=73,550 width=61) (actual time=3,891.117..3,891.187 rows=0 loops=1)

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

Partial GroupAggregate (cost=9,910,996.74..9,912,927.43 rows=36,775 width=61) (actual time=3,886.662..3,886.662 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.024 3,886.661 ↓ 0.0 0 3 / 3

Sort (cost=9,910,996.74..9,911,088.68 rows=36,775 width=35) (actual time=3,886.661..3,886.661 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.001 3,886.637 ↓ 0.0 0 3 / 3

Nested Loop (cost=347.43..9,908,208.01 rows=36,775 width=35) (actual time=3,886.636..3,886.637 rows=0 loops=3)

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

Hash Join (cost=346.73..9,900,576.91 rows=2,320 width=32) (actual time=3,886.636..3,886.636 rows=0 loops=3)

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

Parallel Append (cost=0.70..9,900,180.46 rows=18,957 width=37) (actual time=3,886.635..3,886.635 rows=0 loops=3)

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

Parallel Index Scan using cashbasis_p0_pkey on cashbasis_p0 c (cost=0.70..9,900,085.68 rows=18,957 width=37) (actual time=3,886.633..3,886.633 rows=0 loops=3)

  • Index Cond: (company_id = '181054832'::numeric)
  • Filter: ((date(cash_date) >= '2019-01-01'::date) AND (date(cash_date) <= '2019-01-31'::date))
  • Rows Removed by Filter: 3024958
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 = '181054832'::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_p0_pkey on txdetails_p0 t (cost=0.70..2.92 rows=1 width=23) (never executed)

  • Index Cond: ((company_id = '181054832'::numeric) AND (tx_id = c.tx_id) AND (sequence = c.sequence))
  • Filter: (is_no_post IS NULL)
15. 0.010 331.195 ↓ 0.0 0 1

Sort (cost=2,977,094.34..2,977,094.57 rows=93 width=21) (actual time=331.195..331.195 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 331.185 ↓ 0.0 0 1

Nested Loop (cost=2,976,997.28..2,977,091.30 rows=93 width=21) (actual time=331.185..331.185 rows=0 loops=1)

17. 0.322 0.322 ↑ 1.0 31 1

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

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

Materialize (cost=2,976,997.28..2,976,997.33 rows=3 width=5) (actual time=10.673..10.673 rows=0 loops=31)

19. 0.001 330.853 ↓ 0.0 0 1

Subquery Scan on entity (cost=2,976,997.28..2,976,997.32 rows=3 width=5) (actual time=330.853..330.853 rows=0 loops=1)

20. 0.001 330.852 ↓ 0.0 0 1

Limit (cost=2,976,997.28..2,976,997.29 rows=3 width=37) (actual time=330.852..330.852 rows=0 loops=1)

21. 0.002 330.851 ↓ 0.0 0 1

Sort (cost=2,976,997.28..2,977,008.67 rows=4,556 width=37) (actual time=330.851..330.851 rows=0 loops=1)

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

Finalize GroupAggregate (cost=2,975,635.74..2,976,938.39 rows=4,556 width=37) (actual time=330.849..330.849 rows=0 loops=1)

  • Group Key: t_1.item_id
23. 6.043 332.335 ↓ 0.0 0 1

Gather Merge (cost=2,975,635.74..2,976,813.10 rows=9,112 width=37) (actual time=330.847..332.335 rows=0 loops=1)

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

Partial GroupAggregate (cost=2,974,635.72..2,974,761.33 rows=4,556 width=37) (actual time=326.292..326.292 rows=0 loops=3)

  • Group Key: t_1.item_id
25. 0.017 326.291 ↓ 0.0 0 3 / 3

Sort (cost=2,974,635.72..2,974,652.89 rows=6,866 width=11) (actual time=326.291..326.291 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 326.274 ↓ 0.0 0 3 / 3

Hash Join (cost=347.43..2,974,198.18 rows=6,866 width=11) (actual time=326.274..326.274 rows=0 loops=3)

  • Hash Cond: (c_1.account_id = a_1.account_id)
27. 0.006 326.273 ↓ 0.0 0 3 / 3

Nested Loop (cost=1.40..2,973,702.91 rows=56,117 width=24) (actual time=326.273..326.273 rows=0 loops=3)

28. 0.005 326.080 ↑ 50,489.9 13 3 / 3

Parallel Append (cost=0.70..1,080,575.23 rows=656,369 width=23) (actual time=104.656..326.080 rows=13 loops=3)

29. 326.075 326.075 ↑ 50,489.9 13 3 / 3

Parallel Index Only Scan using txdetails_p0_company_id_date_tx_date_is_no_post_tx_id_seque_idx on txdetails_p0 t_1 (cost=0.70..1,077,293.39 rows=656,369 width=23) (actual time=104.655..326.075 rows=13 loops=3)

  • Index Cond: ((company_id = '181054832'::numeric) AND (is_no_post IS NULL) AND (item_id IS NOT NULL))
  • Heap Fetches: 40
30. 0.027 0.187 ↓ 0.0 0 40 / 3

Append (cost=0.70..2.87 rows=1 width=29) (actual time=0.014..0.014 rows=0 loops=40)

31. 0.160 0.160 ↓ 0.0 0 40 / 3

Index Scan using cashbasis_p0_pkey on cashbasis_p0 c_1 (cost=0.70..2.87 rows=1 width=29) (actual time=0.012..0.012 rows=0 loops=40)

  • Index Cond: ((company_id = '181054832'::numeric) 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: 0
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 = '181054832'::numeric)
  • Filter: (account_type_id = '10'::numeric)