explain.depesz.com

PostgreSQL's explain analyze made readable

Result: KoID

Settings
# exclusive inclusive rows x rows loops node
1. 0.041 56,853.804 ↑ 59,591.7 7 1

Sort (cost=319,753.53..320,796.38 rows=417,142 width=181) (actual time=56,853.802..56,853.804 rows=7 loops=1)

  • Sort Key: (date_part('epoch'::text, min(tr."timestamp")))
  • Sort Method: quicksort Memory: 26kB
2. 1,021.208 56,853.763 ↑ 59,591.7 7 1

GroupAggregate (cost=175,383.30..206,668.95 rows=417,142 width=181) (actual time=54,842.028..56,853.763 rows=7 loops=1)

  • Group Key: o.id, n.uuid, (floor(((date_part('epoch'::text, age(now(), (tr."timestamp")::timestamp with time zone)) / '86400'::double precision) / '30'::double precision)))
3. 8,909.865 55,832.555 ↑ 1.2 357,542 1

Sort (cost=175,383.30..176,426.16 rows=417,142 width=85) (actual time=54,447.638..55,832.555 rows=357,542 loops=1)

  • Sort Key: o.id, n.uuid, (floor(((date_part('epoch'::text, age(now(), (tr."timestamp")::timestamp with time zone)) / '86400'::double precision) / '30'::double precision)))
  • Sort Method: external merge Disk: 39744kB
4. 12,703.739 46,922.690 ↑ 1.2 357,542 1

Hash Join (cost=28.67..116,478.72 rows=417,142 width=85) (actual time=0.160..46,922.690 rows=357,542 loops=1)

  • Hash Cond: (tr.location_id = l.id)
5. 34,218.851 34,218.851 ↓ 1.6 1,308,657 1

Seq Scan on transactions tr (cost=0.00..101,890.89 rows=820,428 width=24) (actual time=0.029..34,218.851 rows=1,308,657 loops=1)

  • Filter: (age((('now'::cstring)::date)::timestamp without time zone, "timestamp") < '180 days'::interval)
  • Rows Removed by Filter: 1153659
6. 0.003 0.100 ↑ 3.7 7 1

Hash (cost=28.35..28.35 rows=26 width=57) (actual time=0.100..0.100 rows=7 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
7. 0.030 0.097 ↑ 3.7 7 1

Hash Join (cost=26.37..28.35 rows=26 width=57) (actual time=0.075..0.097 rows=7 loops=1)

  • Hash Cond: (l.organization_id = o.id)
8. 0.016 0.016 ↓ 1.1 55 1

Seq Scan on locations l (cost=0.00..1.51 rows=51 width=8) (actual time=0.004..0.016 rows=55 loops=1)

9. 0.004 0.051 ↑ 5.0 1 1

Hash (cost=26.30..26.30 rows=5 width=53) (actual time=0.051..0.051 rows=1 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
10. 0.022 0.047 ↑ 5.0 1 1

Hash Join (cost=1.20..26.30 rows=5 width=53) (actual time=0.041..0.047 rows=1 loops=1)

  • Hash Cond: (n.id = (o.id)::text)
  • Join Filter: (((o.id)::text = 'ee5b5e4a-67a1-0b08-79fa-1715b360a9ac'::text) OR (n.uuid = 'ee5b5e4a-67a1-0b08-79fa-1715b360a9ac'::text))
  • Rows Removed by Join Filter: 9
11. 0.007 0.007 ↑ 80.0 11 1

Seq Scan on org_uuid_map n (cost=0.00..18.80 rows=880 width=64) (actual time=0.005..0.007 rows=11 loops=1)

12. 0.011 0.018 ↓ 1.2 11 1

Hash (cost=1.09..1.09 rows=9 width=21) (actual time=0.018..0.018 rows=11 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
13. 0.007 0.007 ↓ 1.2 11 1

Seq Scan on organizations o (cost=0.00..1.09 rows=9 width=21) (actual time=0.002..0.007 rows=11 loops=1)