4 way JOIN using aliases

Hi All,

Thanks to all on the NOVICE list that gave me help I now have a query running
that returns the results I am after. :-) Now of course I want it to run
faster. Currently it clocks in at ~160ms. I have checked over the indexes
and I belive that the tables are indexed properly. The largest table,
tbl_item, only has 2000 rows. Is it possible to reduce the time of this query
further? I have included the output of EXPLAIN ANALYZE below the query.
Unfortunately I am still struggling trying to learn how to interpret the
output. TIA

SELECT tbl_item.id AS item_id,
tbl_item.item_type,
tbl_item.inactive AS item_inactive,
tbl_item.description AS item_description,
CASE WHEN tbl_item.item_class=0 THEN 'Non-Stock'
WHEN tbl_item.item_class=1 THEN 'Stock'
WHEN tbl_item.item_class=2 THEN 'Description'
WHEN tbl_item.item_class=3 THEN 'Assembly'
WHEN tbl_item.item_class=4 THEN 'Service'
WHEN tbl_item.item_class=5 THEN 'Labor'
WHEN tbl_item.item_class=6 THEN 'Activity'
WHEN tbl_item.item_class=7 THEN 'Charge'
ELSE 'Unrecognized'
END AS item_class,
tbl_item.sales_gl_account AS acct_sales_gl_nmbr,
sales_desc.description AS acct_sales_gl_name,
tbl_item.inventory_gl_account AS acct_inv_gl_nmbr,
inv_desc.description AS acct_inv_gl_name,
tbl_item.cogs_gl_account AS acct_cogs_gl_nmbr,
cogs_desc.description AS acct_cogs_gl_name,
CASE WHEN tbl_item.costing_method=0 THEN 'Average'
WHEN tbl_item.costing_method=1 THEN 'FIFO'
WHEN tbl_item.costing_method=2 THEN 'LIFO'
ELSE 'Unrecognized'
END AS acct_cost_method,
tbl_mesh.mesh_size,
tbl_mesh.unit_of_measure AS mesh_uom,
tbl_mesh.mesh_type,
tbl_item.purchase_description,
tbl_item.last_unit_cost AS purchase_unit_cost,
tbl_item.purchase_uom AS purchase_uom,
tbl_item.reorder_point AS purchase_point,
tbl_item.reorder_quantity AS purchase_quantity,
tbl_item.sales_description,
tbl_item.last_unit_cost/peachtree.tbl_item.ptos_uom_factor AS
sales_unit_cost,
tbl_item.unit_of_measure AS sales_uom,
tbl_item.weight AS sales_weight,
tbl_current.last_count
+ tbl_current.received
- tbl_current.shipped AS inv_on_hand,
tbl_current.allocated AS inv_committed,
tbl_current.last_count
+ tbl_current.received
- tbl_current.shipped
- tbl_current.allocated AS inv_available,
tbl_current.on_order AS inv_on_order
FROM tbl_item
LEFT JOIN tbl_mesh
ON ( tbl_item.id = tbl_mesh.item_id )
JOIN tbl_gl_account AS sales_desc
ON ( tbl_item.sales_gl_account = sales_desc.account_id )
JOIN tbl_gl_account AS inv_desc
ON ( tbl_item.inventory_gl_account = inv_desc.account_id )
JOIN tbl_gl_account AS cogs_desc
ON ( tbl_item.cogs_gl_account = cogs_desc.account_id )
LEFT JOIN tbl_current
ON ( tbl_item.id = tbl_current.item_id )
ORDER BY tbl_item.id;

Sort (cost=5749.75..5758.98 rows=3691 width=333) (actual
time=154.923..156.070 rows=1906 loops=1)
Sort Key: tbl_item.id
-> Hash Left Join (cost=2542.56..5194.32 rows=3691 width=333) (actual
time=30.475..146.074 rows=1906 loops=1)
Hash Cond: (("outer".id)::text = ("inner".item_id)::text)
-> Hash Join (cost=15.85..366.14 rows=3691 width=313) (actual
time=2.292..82.281 rows=1906 loops=1)
Hash Cond: (("outer".sales_gl_account)::text =
("inner".account_id)::text)
-> Hash Join (cost=11.18..305.81 rows=3749 width=290) (actual
time=1.632..61.052 rows=1906 loops=1)
Hash Cond: (("outer".cogs_gl_account)::text =
("inner".account_id)::text)
-> Hash Join (cost=6.50..244.60 rows=3808 width=267)
(actual time=1.034..40.873 rows=1906 loops=1)
Hash Cond: (("outer".inventory_gl_account)::text =
("inner".account_id)::text)
-> Hash Left Join (cost=1.82..182.50 rows=3868
width=244) (actual time=0.407..20.878 rows=1936 loops=1)
Hash Cond: (("outer".id)::text =
("inner".item_id)::text)
-> Seq Scan on tbl_item (cost=0.00..160.68
rows=3868 width=224) (actual time=0.131..5.022 rows=1936 loops=1)
-> Hash (cost=1.66..1.66 rows=66 width=34)
(actual time=0.236..0.236 rows=0 loops=1)
-> Seq Scan on tbl_mesh
(cost=0.00..1.66 rows=66 width=34) (actual time=0.031..0.149 rows=66 loops=1)
-> Hash (cost=4.14..4.14 rows=214 width=32)
(actual time=0.573..0.573 rows=0 loops=1)
-> Seq Scan on tbl_gl_account inv_desc
(cost=0.00..4.14 rows=214 width=32) (actual time=0.005..0.317 rows=214 loops=1)
-> Hash (cost=4.14..4.14 rows=214 width=32) (actual
time=0.556..0.556 rows=0 loops=1)
-> Seq Scan on tbl_gl_account cogs_desc
(cost=0.00..4.14 rows=214 width=32) (actual time=0.005..0.294 rows=214 loops=1)
-> Hash (cost=4.14..4.14 rows=214 width=32) (actual
time=0.603..0.603 rows=0 loops=1)
-> Seq Scan on tbl_gl_account sales_desc
(cost=0.00..4.14 rows=214 width=32) (actual time=0.031..0.343 rows=214 loops=1)
-> Hash (cost=1775.57..1775.57 rows=76457 width=31) (actual
time=26.114..26.114 rows=0 loops=1)
-> Seq Scan on tbl_current (cost=0.00..1775.57 rows=76457
width=31) (actual time=22.870..25.024 rows=605 loops=1)
Total runtime: 158.053 ms

Kind Regards,
Keith