explain.depesz.com

PostgreSQL's explain analyze made readable

Result: mLEee

Settings
# exclusive inclusive rows x rows loops node
1. 0.000 2,548.088 ↑ 1.0 1 1

Finalize Aggregate (cost=63,194.95..63,194.96 rows=1 width=32) (actual time=2,548.088..2,548.088 rows=1 loops=1)

  • Buffers: shared hit=4039965
2. 6.705 2,549.738 ↓ 1.5 3 1

Gather (cost=63,194.73..63,194.94 rows=2 width=32) (actual time=2,548.070..2,549.738 rows=3 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
  • Buffers: shared hit=4039965
3. 271.332 2,543.033 ↑ 1.0 1 3 / 3

Partial Aggregate (cost=62,194.73..62,194.74 rows=1 width=32) (actual time=2,543.033..2,543.033 rows=1 loops=3)

  • Buffers: shared hit=4039965
4. 163.506 2,271.701 ↓ 43.5 268,178 3 / 3

Hash Join (cost=352.17..62,148.52 rows=6,160 width=9) (actual time=36.522..2,271.701 rows=268,178 loops=3)

  • Hash Cond: (stockitemtransaction.storeid = userstorepermission.storeid)
  • Buffers: shared hit=4039965
5. 377.308 2,108.161 ↓ 45.0 268,178 3 / 3

Nested Loop (cost=349.24..62,126.82 rows=5,965 width=41) (actual time=36.440..2,108.161 rows=268,178 loops=3)

  • Buffers: shared hit=4039894
6. 109.122 121.785 ↓ 29.2 268,178 3 / 3

Parallel Bitmap Heap Scan on stockitemtransaction (cost=348.68..29,958.64 rows=9,197 width=44) (actual time=36.409..121.785 rows=268,178 loops=3)

  • Recheck Cond: (((transactiontime)::date >= '2020-01-01'::date) AND ((transactiontime)::date <= '2020-01-31'::date) AND (transactiontype = 2))
  • Heap Blocks: exact=4465
  • Buffers: shared hit=17222
7. 12.663 12.663 ↓ 36.4 804,534 1 / 3

Bitmap Index Scan on ix_stockitemtransaction_transactiontime_cast (cost=0.00..343.16 rows=22,073 width=0) (actual time=37.989..37.990 rows=804,534 loops=1)

  • Index Cond: (((transactiontime)::date >= '2020-01-01'::date) AND ((transactiontime)::date <= '2020-01-31'::date))
  • Buffers: shared hit=2201
8. 1,609.068 1,609.068 ↑ 1.0 1 804,534 / 3

Index Scan using dailytotals_storeid_stockitemid_calculationdate_uq on dailytotals dt (cost=0.56..3.50 rows=1 width=41) (actual time=0.006..0.006 rows=1 loops=804,534)

  • Index Cond: ((stockitemid = stockitemtransaction.stockitemid) AND (storeid = stockitemtransaction.storeid) AND (calculationdate = (stockitemtransaction.transactiontime)::date))
  • Buffers: shared hit=4022672
9. 0.009 0.034 ↑ 1.0 22 3 / 3

Hash (cost=2.66..2.66 rows=22 width=16) (actual time=0.033..0.034 rows=22 loops=3)

  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
  • Buffers: shared hit=11
10. 0.025 0.025 ↑ 1.0 22 3 / 3

Index Only Scan using ix_test on userstorepermission (cost=0.27..2.66 rows=22 width=16) (actual time=0.021..0.025 rows=22 loops=3)

  • Index Cond: (tenantuserid = 'e8dd09bb-cf41-4601-84de-78ff81618612'::uuid)
  • Heap Fetches: 0
  • Buffers: shared hit=11