explain.depesz.com

PostgreSQL's explain analyze made readable

Result: vvuN

Settings
# exclusive inclusive rows x rows loops node
1. 0.000 1,047.249 ↓ 51.0 51 1

Limit (cost=184,852.14..184,852.15 rows=1 width=256) (actual time=1,047.220..1,047.249 rows=51 loops=1)

2.          

Initplan (for Limit)

3. 0.018 0.018 ↑ 1.0 1 1

Index Scan using idx_employee_empid on employee (cost=0.42..8.44 rows=1 width=4) (actual time=0.017..0.018 rows=1 loops=1)

  • Index Cond: ((employeeid)::text = 'CGI9998'::text)
4. 2.350 1,047.231 ↓ 51.0 51 1

Sort (cost=184,843.70..184,843.71 rows=1 width=256) (actual time=1,047.218..1,047.231 rows=51 loops=1)

  • Sort Key: pbh.total, pbh.billid
  • Sort Method: top-N heapsort Memory: 51kB
5. 2.085 1,044.881 ↓ 1,673.0 1,673 1

Nested Loop Left Join (cost=3,766.38..184,843.69 rows=1 width=256) (actual time=218.485..1,044.881 rows=1,673 loops=1)

6. 2.175 1,031.085 ↓ 1,673.0 1,673 1

Nested Loop (cost=3,765.96..184,835.25 rows=1 width=256) (actual time=218.438..1,031.085 rows=1,673 loops=1)

7. 29.648 1,022.218 ↓ 1,673.0 1,673 1

Hash Right Join (cost=3,765.96..184,833.77 rows=1 width=256) (actual time=218.414..1,022.218 rows=1,673 loops=1)

  • Hash Cond: (i.contractid = pbh.infoid)
8. 774.943 774.943 ↑ 1.0 229,828 1

Seq Scan on inventories i (cost=0.00..177,907.67 rows=229,828 width=2,486) (actual time=0.109..774.943 rows=229,828 loops=1)

9. 1.444 217.627 ↓ 1,673.0 1,673 1

Hash (cost=3,765.95..3,765.95 rows=1 width=256) (actual time=217.627..217.627 rows=1,673 loops=1)

  • Buckets: 2048 (originally 1024) Batches: 1 (originally 1) Memory Usage: 522kB
10. 0.491 216.183 ↓ 1,673.0 1,673 1

Nested Loop (cost=11.34..3,765.95 rows=1 width=256) (actual time=73.946..216.183 rows=1,673 loops=1)

11. 0.099 0.422 ↓ 2.5 38 1

Nested Loop (cost=10.91..66.91 rows=15 width=8) (actual time=0.070..0.422 rows=38 loops=1)

12. 0.050 0.095 ↓ 2.9 38 1

HashAggregate (cost=10.62..10.75 rows=13 width=4) (actual time=0.059..0.095 rows=38 loops=1)

  • Group Key: ecc.costcenterid
13. 0.010 0.045 ↓ 2.5 38 1

Bitmap Heap Scan on employeecostcenter ecc (cost=4.39..10.58 rows=15 width=4) (actual time=0.042..0.045 rows=38 loops=1)

  • Recheck Cond: (employeeid = $0)
  • Heap Blocks: exact=2
14. 0.035 0.035 ↓ 2.5 38 1

Bitmap Index Scan on empcc_emp_idx (cost=0.00..4.39 rows=15 width=0) (actual time=0.035..0.035 rows=38 loops=1)

  • Index Cond: (employeeid = $0)
15. 0.228 0.228 ↑ 1.0 1 38

Index Only Scan using costcenter_pkey on costcenter cc (cost=0.29..4.31 rows=1 width=4) (actual time=0.005..0.006 rows=1 loops=38)

  • Index Cond: (costcenterid = ecc.costcenterid)
  • Heap Fetches: 0
16. 215.270 215.270 ↓ 44.0 44 38

Index Scan using pbh_costcenterid on paidbillshistory pbh (cost=0.43..246.59 rows=1 width=256) (actual time=4.257..5.665 rows=44 loops=38)

  • Index Cond: (costcenterid = cc.costcenterid)
  • Filter: (((costcenterno)::text = ANY ('{CA002-1001-10012-140,""}'::text[])) AND (invoicedate >= '2019-11-01'::date) AND (invoicedate < '2019-12-02'::date) AND (vendorid > '-1'::integer) AND ((costcenterno)::text (...)
  • Rows Removed by Filter: 8470
17. 6.692 6.692 ↑ 1.0 1 1,673

Seq Scan on servicetypes st (cost=0.00..1.46 rows=1 width=4) (actual time=0.002..0.004 rows=1 loops=1,673)

  • Filter: (typeid = 3)
  • Rows Removed by Filter: 36
18. 11.711 11.711 ↑ 1.0 1 1,673

Index Only Scan using idx_employee_empid on employee e (cost=0.42..8.44 rows=1 width=9) (actual time=0.007..0.007 rows=1 loops=1,673)

  • Index Cond: (employeeid = (pbh.username)::text)
  • Heap Fetches: 585
Planning time : 4.462 ms
Execution time : 1,047.559 ms