explain.depesz.com

PostgreSQL's explain analyze made readable

Result: QoU4 : LoanByDate 4

Settings
# exclusive inclusive rows x rows loops node
1. 0.100 12,723.643 ↑ 12.5 16 1

GroupAggregate (cost=20,951.73..20,964.73 rows=200 width=328) (actual time=12,723.582..12,723.643 rows=16 loops=1)

  • Group Key: (date_trunc('day'::text, t.date))
2. 0.048 12,723.543 ↑ 12.5 16 1

Sort (cost=20,951.73..20,952.23 rows=200 width=184) (actual time=12,723.541..12,723.543 rows=16 loops=1)

  • Sort Key: (date_trunc('day'::text, t.date))
  • Sort Method: quicksort Memory: 27kB
3. 0.059 12,723.495 ↑ 12.5 16 1

Hash Full Join (cost=20,939.05..20,944.09 rows=200 width=184) (actual time=12,723.468..12,723.495 rows=16 loops=1)

  • Hash Cond: ((date_trunc('day'::text, t.date)) = pay.date)
4. 402.570 12,171.530 ↑ 12.5 16 1

HashAggregate (cost=20,327.64..20,330.14 rows=200 width=32) (actual time=12,171.522..12,171.530 rows=16 loops=1)

  • Group Key: date_trunc('day'::text, t.date)
5. 11,768.960 11,768.960 ↓ 36.1 756,554 1

Index Scan using tracks_date_index on tracks t (cost=0.43..20,013.56 rows=20,939 width=10) (actual time=1.827..11,768.960 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))
6. 0.101 551.906 ↓ 5.3 16 1

Hash (cost=611.37..611.37 rows=3 width=160) (actual time=551.906..551.906 rows=16 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 10kB
7. 0.035 551.805 ↓ 5.3 16 1

Subquery Scan on pay (cost=611.15..611.37 rows=3 width=160) (actual time=518.133..551.805 rows=16 loops=1)

8. 18.257 551.770 ↓ 5.3 16 1

GroupAggregate (cost=611.15..611.34 rows=3 width=160) (actual time=518.131..551.770 rows=16 loops=1)

  • Group Key: (date_trunc('day'::text, p.date))
9. 61.214 533.513 ↓ 13,941.0 41,823 1

Sort (cost=611.15..611.16 rows=3 width=260) (actual time=517.978..533.513 rows=41,823 loops=1)

  • Sort Key: (date_trunc('day'::text, p.date))
  • Sort Method: external merge Disk: 4,880kB
10. 151.851 472.299 ↓ 13,941.0 41,823 1

Nested Loop Left Join (cost=0.69..611.13 rows=3 width=260) (actual time=3.534..472.299 rows=41,823 loops=1)

  • -> Index Scan using actions_id_uindex on actions a (cost=0.27..2.29 rows=1 width=10) (actual time=0.002..0.002 rows=1 loops=41,823)" Index Cond: (id = p.action_id)
11. 320.448 320.448 ↓ 13,941.0 41,823 1

Index Scan using postbacks_date_index on postbacks p (cost=0.42..604.24 rows=3 width=274) (actual time=2.144..320.448 rows=41,823 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,482