explain.depesz.com

PostgreSQL's explain analyze made readable

Result: t4yD : LoanByDate 2

Settings
# exclusive inclusive rows x rows loops node
1. 0.048 1,314.037 ↑ 2,500.0 16 1

GroupAggregate (cost=54,874,661.08..60,485,580.48 rows=40,000 width=328) (actual time=1,314.006..1,314.037 rows=16 loops=1)

  • Group Key: (COALESCE((date_trunc('day'::text, t.date)), pay.date))
2. 0.040 1,313.989 ↑ 11,686,498.8 16 1

Sort (cost=54,874,661.08..55,342,121.03 rows=186,983,980 width=184) (actual time=1,313.987..1,313.989 rows=16 loops=1)

  • Sort Key: (COALESCE((date_trunc('day'::text, t.date)), pay.date)) DESC
  • Sort Method: quicksort Memory: 27kB
3. 1.051 1,313.949 ↑ 11,686,498.8 16 1

Hash Full Join (cost=651,322.51..698,768.73 rows=186,983,980 width=184) (actual time=1,139.694..1,313.949 rows=16 loops=1)

  • Hash Cond: ((date_trunc('day'::text, t.date)) = pay.date)
4. 102.785 1,215.938 ↑ 51,490.3 16 1

GroupAggregate (cost=640,752.46..665,467.81 rows=823,845 width=32) (actual time=1,026.370..1,215.938 rows=16 loops=1)

  • Group Key: (date_trunc('day'::text, t.date))
5. 273.943 1,113.153 ↑ 1.1 756,554 1

Sort (cost=640,752.46..642,812.07 rows=823,845 width=10) (actual time=1,025.601..1,113.153 rows=756,554 loops=1)

  • Sort Key: (date_trunc('day'::text, t.date))
  • Sort Method: external merge Disk: 14,824kB
6. 839.210 839.210 ↑ 1.1 756,554 1

Index Scan using tracks_date_index on tracks t (cost=0.44..551,755.39 rows=823,845 width=10) (actual time=0.072..839.210 rows=756,554 loops=1)

  • Index Cond: ((date >= '2020-02-05 21:00:00+03'::timestamp with time zone) AND (date <= '2020-02-20 20:59:59+03'::timestamp with time zone))
7. 0.100 96.960 ↑ 2,837.1 16 1

Hash (cost=8,982.64..8,982.64 rows=45,393 width=160) (actual time=96.960..96.960 rows=16 loops=1)

  • Buckets: 65,536 Batches: 2 Memory Usage: 514kB
8. 0.032 96.860 ↑ 2,837.1 16 1

Subquery Scan on pay (cost=5,691.65..8,982.64 rows=45,393 width=160) (actual time=78.847..96.860 rows=16 loops=1)

9. 12.782 96.828 ↑ 2,837.1 16 1

GroupAggregate (cost=5,691.65..8,528.71 rows=45,393 width=160) (actual time=78.846..96.828 rows=16 loops=1)

  • Group Key: (date_trunc('day'::text, p.date))
10. 34.661 84.046 ↑ 1.1 41,534 1

Sort (cost=5,691.65..5,805.13 rows=45,393 width=110) (actual time=78.763..84.046 rows=41,534 loops=1)

  • Sort Key: (date_trunc('day'::text, p.date))
  • Sort Method: external merge Disk: 4,840kB
11. 16.887 49.385 ↑ 1.1 41,534 1

Hash Left Join (cost=142.02..2,180.46 rows=45,393 width=110) (actual time=1.627..49.385 rows=41,534 loops=1)

  • Hash Cond: (p.action_id = a.id)
12. 30.962 30.962 ↑ 1.1 41,534 1

Index Scan using postbacks_date_index on postbacks p (cost=0.43..1,806.10 rows=45,393 width=124) (actual time=0.072..30.962 rows=41,534 loops=1)

  • Index Cond: ((date >= '2020-02-05 21:00:00+03'::timestamp with time zone) AND (date <= '2020-02-20 20:59:59+03'::timestamp with time zone))
  • Filter: (updated_by IS NULL)
  • Rows Removed by Filter: 2,771
13. 0.676 1.536 ↓ 1.0 3,811 1

Hash (cost=94.04..94.04 rows=3,804 width=10) (actual time=1.536..1.536 rows=3,811 loops=1)

  • Buckets: 4,096 Batches: 1 Memory Usage: 211kB
14. 0.860 0.860 ↓ 1.0 3,811 1

Seq Scan on actions a (cost=0.00..94.04 rows=3,804 width=10) (actual time=0.008..0.860 rows=3,811 loops=1)