explain.depesz.com

PostgreSQL's explain analyze made readable

Result: JlBG

Settings
# exclusive inclusive rows x rows loops node
1. 0.022 156.602 ↑ 1.0 93 1

Sort (cost=12,860.56..12,860.79 rows=93 width=68) (actual time=156.598..156.602 rows=93 loops=1)

  • Sort Key: date_series.date
  • Sort Method: quicksort Memory: 29kB
2. 0.049 156.580 ↑ 1.0 93 1

Hash Left Join (cost=12,762.02..12,857.52 rows=93 width=68) (actual time=156.366..156.580 rows=93 loops=1)

  • Hash Cond: (((date_series.month)::double precision = base.month) AND ((date_series.year)::double precision = base.year) AND ((date_series.date_of_month)::double precision = base.date) AND (entity.item_id = base.entity_id))
3. 0.031 19.670 ↑ 1.0 93 1

Nested Loop (cost=3,273.48..3,367.49 rows=93 width=21) (actual time=19.493..19.670 rows=93 loops=1)

4. 0.264 0.264 ↑ 1.0 31 1

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

  • Filter: ((date >= '2019-01-01'::date) AND (date <= '2019-01-31'::date))
  • Rows Removed by Filter: 3,623
5. 0.000 19.375 ↑ 1.0 3 31

Materialize (cost=3,273.48..3,273.53 rows=3 width=5) (actual time=0.625..0.625 rows=3 loops=31)

6. 0.002 19.381 ↑ 1.0 3 1

Subquery Scan on entity (cost=3,273.48..3,273.51 rows=3 width=5) (actual time=19.374..19.381 rows=3 loops=1)

7. 0.006 19.379 ↑ 1.0 3 1

Limit (cost=3,273.48..3,273.48 rows=3 width=37) (actual time=19.373..19.379 rows=3 loops=1)

8. 0.007 19.373 ↑ 40.7 3 1

Sort (cost=3,273.48..3,273.78 rows=122 width=37) (actual time=19.372..19.373 rows=3 loops=1)

  • Sort Key: (sum(c.home_amount))
  • Sort Method: top-N heapsort Memory: 25kB
9. 0.181 19.366 ↑ 9.4 13 1

GroupAggregate (cost=3,269.46..3,271.90 rows=122 width=37) (actual time=19.240..19.366 rows=13 loops=1)

  • Group Key: t.item_id
10. 0.260 19.185 ↓ 7.6 932 1

Sort (cost=3,269.46..3,269.77 rows=122 width=11) (actual time=19.145..19.185 rows=932 loops=1)

  • Sort Key: t.item_id
  • Sort Method: quicksort Memory: 68kB
11. 0.475 18.925 ↓ 7.6 932 1

Nested Loop (cost=237.50..3,265.23 rows=122 width=11) (actual time=0.561..18.925 rows=932 loops=1)

12. 1.216 10.932 ↓ 4.9 1,074 1

Hash Join (cost=236.80..2,621.92 rows=219 width=24) (actual time=0.541..10.932 rows=1,074 loops=1)

  • Hash Cond: (c.account_id = a.account_id)
13. 0.711 9.232 ↓ 5.0 10,396 1

Append (cost=0.70..2,380.24 rows=2,096 width=29) (actual time=0.052..9.232 rows=10,396 loops=1)

14. 8.521 8.521 ↓ 5.0 10,396 1

Index Scan using cashbasis_p6_company_id_cash_date_tx_id_sequence_idx on cashbasis_p6 c (cost=0.70..2,369.76 rows=2,096 width=29) (actual time=0.052..8.521 rows=10,396 loops=1)

  • Index Cond: ((company_id = 178,348,139.000000000) 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))
15. 0.022 0.484 ↓ 3.3 99 1

Hash (cost=235.72..235.72 rows=30 width=13) (actual time=0.483..0.484 rows=99 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 13kB
16. 0.462 0.462 ↓ 3.3 99 1

Index Scan using accounts_pk on accounts a (cost=0.56..235.72 rows=30 width=13) (actual time=0.030..0.462 rows=99 loops=1)

  • Index Cond: (company_id = 178,348,139.000000000)
  • Filter: (account_type_id = '10'::numeric)
  • Rows Removed by Filter: 283
17. 0.000 7.518 ↑ 1.0 1 1,074

Append (cost=0.70..2.93 rows=1 width=23) (actual time=0.007..0.007 rows=1 loops=1,074)

18. 7.518 7.518 ↑ 1.0 1 1,074

Index Scan using txdetails_p6_pkey on txdetails_p6 t (cost=0.70..2.92 rows=1 width=23) (actual time=0.006..0.007 rows=1 loops=1,074)

  • Index Cond: ((company_id = 178,348,139.000000000) AND (tx_id = c.tx_id) AND (sequence = c.sequence))
  • Filter: ((is_no_post IS NULL) AND (item_id IS NOT NULL))
  • Rows Removed by Filter: 0
19. 0.056 136.861 ↑ 11.5 159 1

Hash (cost=9,451.83..9,451.83 rows=1,836 width=61) (actual time=136.861..136.861 rows=159 loops=1)

  • Buckets: 2,048 Batches: 1 Memory Usage: 27kB
20. 0.016 136.805 ↑ 10.7 171 1

Subquery Scan on base (cost=9,396.75..9,451.83 rows=1,836 width=61) (actual time=136.729..136.805 rows=171 loops=1)

21. 5.295 136.789 ↑ 10.7 171 1

HashAggregate (cost=9,396.75..9,433.47 rows=1,836 width=61) (actual time=136.728..136.789 rows=171 loops=1)

  • Group Key: t_1.item_id, date_part('day'::text, c_1.cash_date), date_part('month'::text, c_1.cash_date), date_part('year'::text, c_1.cash_date)
22. 10.206 131.494 ↓ 6.6 12,133 1

Nested Loop (cost=237.50..9,373.80 rows=1,836 width=35) (actual time=0.514..131.494 rows=12,133 loops=1)

23. 4.694 36.357 ↓ 18.4 12,133 1

Hash Join (cost=236.80..7,421.62 rows=660 width=32) (actual time=0.491..36.357 rows=12,133 loops=1)

  • Hash Cond: (c_1.account_id = a_1.account_id)
24. 1.897 31.224 ↓ 4.3 26,873 1

Append (cost=0.70..7,168.67 rows=6,316 width=37) (actual time=0.047..31.224 rows=26,873 loops=1)

25. 29.327 29.327 ↓ 4.3 26,873 1

Index Scan using cashbasis_p6_company_id_cash_date_tx_id_sequence_idx on cashbasis_p6 c_1 (cost=0.70..7,137.09 rows=6,316 width=37) (actual time=0.047..29.327 rows=26,873 loops=1)

  • Index Cond: ((company_id = 178,348,139.000000000) 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))
26. 0.018 0.439 ↓ 3.3 99 1

Hash (cost=235.72..235.72 rows=30 width=13) (actual time=0.439..0.439 rows=99 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 13kB
27. 0.421 0.421 ↓ 3.3 99 1

Index Scan using accounts_pk on accounts a_1 (cost=0.56..235.72 rows=30 width=13) (actual time=0.029..0.421 rows=99 loops=1)

  • Index Cond: (company_id = 178,348,139.000000000)
  • Filter: (account_type_id = '10'::numeric)
  • Rows Removed by Filter: 283
28. 0.000 84.931 ↑ 1.0 1 12,133

Append (cost=0.70..2.93 rows=1 width=23) (actual time=0.007..0.007 rows=1 loops=12,133)

29. 84.931 84.931 ↑ 1.0 1 12,133

Index Scan using txdetails_p6_pkey on txdetails_p6 t_1 (cost=0.70..2.92 rows=1 width=23) (actual time=0.007..0.007 rows=1 loops=12,133)

  • Index Cond: ((company_id = 178,348,139.000000000) AND (tx_id = c_1.tx_id) AND (sequence = c_1.sequence))
  • Filter: (is_no_post IS NULL)
Planning time : 44.284 ms
Execution time : 156.787 ms