explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Mv3w : Optimization for: plan #1U1V

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 0.016 401.921 ↑ 1.0 1 1

Aggregate (cost=64,739.47..64,739.48 rows=1 width=96) (actual time=401.921..401.921 rows=1 loops=1)

2. 0.015 401.905 ↑ 1.2 4 1

HashAggregate (cost=64,739.33..64,739.38 rows=5 width=96) (actual time=401.902..401.905 rows=4 loops=1)

  • Group Key: "*SELECT* 1".target_amount, ((0)::numeric), (((0))::numeric)
3. 0.003 401.890 ↑ 1.2 4 1

Append (cost=21.55..64,739.29 rows=5 width=96) (actual time=0.074..401.890 rows=4 loops=1)

4. 0.012 0.077 ↑ 1.3 3 1

HashAggregate (cost=21.55..21.60 rows=4 width=96) (actual time=0.074..0.077 rows=3 loops=1)

  • Group Key: "*SELECT* 1".target_amount, ((0)::numeric), (0)
5. 0.002 0.065 ↑ 1.3 3 1

Append (cost=4.30..21.52 rows=4 width=68) (actual time=0.048..0.065 rows=3 loops=1)

6. 0.004 0.054 ↑ 1.5 2 1

Subquery Scan on *SELECT* 1 (cost=4.30..13.32 rows=3 width=39) (actual time=0.048..0.054 rows=2 loops=1)

7. 0.021 0.050 ↑ 1.5 2 1

Bitmap Heap Scan on salesman_sales_target sst (cost=4.30..13.28 rows=3 width=11) (actual time=0.045..0.050 rows=2 loops=1)

  • Recheck Cond: (salesman_id = 620)
  • Heap Blocks: exact=2
8. 0.029 0.029 ↑ 1.5 2 1

Bitmap Index Scan on salesman_idx_salesman_sales_target (cost=0.00..4.30 rows=3 width=0) (actual time=0.029..0.029 rows=2 loops=1)

  • Index Cond: (salesman_id = 620)
9. 0.002 0.009 ↑ 1.0 1 1

Subquery Scan on *SELECT* 2 (cost=8.18..8.21 rows=1 width=68) (actual time=0.008..0.009 rows=1 loops=1)

10. 0.002 0.007 ↑ 1.0 1 1

Aggregate (cost=8.18..8.19 rows=1 width=40) (actual time=0.007..0.007 rows=1 loops=1)

11. 0.005 0.005 ↓ 0.0 0 1

Index Scan using salesman_idx_invoice on invoice i (cost=0.14..8.17 rows=1 width=138) (actual time=0.005..0.005 rows=0 loops=1)

  • Index Cond: (salesman_id = 620)
  • Filter: (enabled AND (NOT is_deleted) AND ((billing_type)::text = ANY ('{SELLING,SELLING_RETURN}'::text[])) AND (date_part('month'::text, date) = date_part('month'::text, (now() - '5 days'::interval))))
12. 0.004 401.810 ↑ 1.0 1 1

Subquery Scan on *SELECT* 3 (cost=64,717.66..64,717.69 rows=1 width=96) (actual time=401.809..401.810 rows=1 loops=1)

13. 0.032 401.806 ↑ 1.0 1 1

Aggregate (cost=64,717.66..64,717.67 rows=1 width=40) (actual time=401.806..401.806 rows=1 loops=1)

14. 51.541 401.774 ↑ 1.8 4 1

Hash Join (cost=3,602.52..64,717.61 rows=7 width=60) (actual time=377.532..401.774 rows=4 loops=1)

  • Hash Cond: (od.orders_id = o.id)
15. 328.212 350.174 ↑ 4.3 121,396 1

Bitmap Heap Scan on order_detail od (cost=2,085.58..61,832.79 rows=521,081 width=68) (actual time=31.304..350.174 rows=121,396 loops=1)

  • Recheck Cond: ((quantity - shipped_amount) > '0'::numeric)
  • Heap Blocks: exact=16370
16. 21.962 21.962 ↑ 4.3 121,396 1

Bitmap Index Scan on order_detail_idx_where (cost=0.00..1,955.31 rows=521,081 width=0) (actual time=21.961..21.962 rows=121,396 loops=1)

17. 0.003 0.059 ↑ 1.0 2 1

Hash (cost=1,516.92..1,516.92 rows=2 width=8) (actual time=0.058..0.059 rows=2 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
18. 0.024 0.056 ↑ 1.0 2 1

Bitmap Heap Scan on orders o (cost=11.76..1,516.92 rows=2 width=8) (actual time=0.049..0.056 rows=2 loops=1)

  • Recheck Cond: (salesman_id = 620)
  • Filter: (enabled AND (NOT is_deleted) AND (NOT cancelled) AND (date_part('month'::text, (order_date)::timestamp without time zone) = date_part('month'::text, now())))
  • Heap Blocks: exact=2
19. 0.032 0.032 ↑ 222.5 2 1

Bitmap Index Scan on salesman_idx (cost=0.00..11.76 rows=445 width=0) (actual time=0.032..0.032 rows=2 loops=1)

  • Index Cond: (salesman_id = 620)
Planning time : 1.383 ms
Execution time : 402.221 ms