explain.depesz.com

PostgreSQL's explain analyze made readable

Result: nbqL

Settings
# exclusive inclusive rows x rows loops node
1. 239.653 75,991.318 ↑ 39,851.5 34 1

GroupAggregate (cost=600,550.92..664,911.14 rows=1,354,952 width=115) (actual time=75,207.835..75,991.318 rows=34 loops=1)

  • Group Key: o.id, lm.item_category_name, (floor(((date_part('epoch'::text, age(now(), (tr."timestamp")::timestamp with time zone)) / '86400'::double precision) / '30'::double precision)))
2. 1,304.238 75,751.665 ↑ 6.7 202,348 1

Sort (cost=600,550.92..603,938.30 rows=1,354,952 width=43) (actual time=75,156.054..75,751.665 rows=202,348 loops=1)

  • Sort Key: o.id, lm.item_category_name, (floor(((date_part('epoch'::text, age(now(), (tr."timestamp")::timestamp with time zone)) / '86400'::double precision) / '30'::double precision)))
  • Sort Method: external merge Disk: 13624kB
3. 4,086.020 74,447.427 ↑ 6.7 202,348 1

Hash Join (cost=132,612.91..379,187.34 rows=1,354,952 width=43) (actual time=48,716.524..74,447.427 rows=202,348 loops=1)

  • Hash Cond: (ti.item_id = i.id)
4. 19,404.847 54,243.713 ↑ 1.1 729,037 1

Hash Join (cost=118,105.61..308,257.30 rows=813,112 width=45) (actual time=32,274.978..54,243.713 rows=729,037 loops=1)

  • Hash Cond: (ti.transaction_id = tr.id)
5. 2,564.342 2,564.342 ↓ 1.0 4,798,494 1

Seq Scan on transaction_items ti (cost=0.00..113,508.42 rows=4,797,642 width=16) (actual time=0.029..2,564.342 rows=4,798,494 loops=1)

6. 2,971.501 32,274.524 ↑ 1.2 357,668 1

Hash (cost=109,219.45..109,219.45 rows=417,373 width=45) (actual time=32,274.524..32,274.524 rows=357,668 loops=1)

  • Buckets: 65536 Batches: 16 Memory Usage: 2227kB
7. 8,278.530 29,303.023 ↑ 1.2 357,668 1

Hash Join (cost=28.67..109,219.45 rows=417,373 width=45) (actual time=2,750.445..29,303.023 rows=357,668 loops=1)

  • Hash Cond: (tr.location_id = l.id)
8. 21,024.375 21,024.375 ↓ 1.6 1,309,024 1

Seq Scan on transactions tr (cost=0.00..101,927.58 rows=820,883 width=28) (actual time=2,750.298..21,024.375 rows=1,309,024 loops=1)

  • Filter: (age((('now'::cstring)::date)::timestamp without time zone, "timestamp") < '6 mons'::interval)
  • Rows Removed by Filter: 1153659
9. 0.010 0.118 ↑ 3.7 7 1

Hash (cost=28.35..28.35 rows=26 width=25) (actual time=0.118..0.118 rows=7 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
10. 0.023 0.108 ↑ 3.7 7 1

Hash Join (cost=26.37..28.35 rows=26 width=25) (actual time=0.093..0.108 rows=7 loops=1)

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

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

12. 0.008 0.062 ↑ 5.0 1 1

Hash (cost=26.30..26.30 rows=5 width=21) (actual time=0.062..0.062 rows=1 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
13. 0.027 0.054 ↑ 5.0 1 1

Hash Join (cost=1.20..26.30 rows=5 width=21) (actual time=0.047..0.054 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
14. 0.006 0.006 ↑ 80.0 11 1

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

15. 0.013 0.021 ↓ 1.2 11 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
16. 0.008 0.008 ↓ 1.2 11 1

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

17. 547.725 16,117.694 ↑ 3.5 47,245 1

Hash (cost=11,654.54..11,654.54 rows=164,060 width=14) (actual time=16,117.693..16,117.694 rows=47,245 loops=1)

  • Buckets: 131072 Batches: 4 Memory Usage: 1578kB
18. 13,613.981 15,569.969 ↑ 3.5 47,245 1

Hash Join (cost=4,271.19..11,654.54 rows=164,060 width=14) (actual time=1,576.818..15,569.969 rows=47,245 loops=1)

  • Hash Cond: ("substring"(lm.product_sku_no, '(([0-9]+.*)*[0-9]+)'::text) = (i.location_sku)::text)
19. 380.028 380.028 ↑ 1.0 23,703 1

Seq Scan on liquor_mapping lm (cost=0.00..789.76 rows=23,876 width=12) (actual time=0.015..380.028 rows=23,703 loops=1)

20. 1,107.112 1,575.960 ↑ 1.2 82,445 1

Hash (cost=2,559.53..2,559.53 rows=98,453 width=14) (actual time=1,575.960..1,575.960 rows=82,445 loops=1)

  • Buckets: 131072 Batches: 2 Memory Usage: 2895kB
21. 468.848 468.848 ↑ 1.0 97,593 1

Seq Scan on items i (cost=0.00..2,559.53 rows=98,453 width=14) (actual time=0.011..468.848 rows=97,593 loops=1)