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.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.unit_of_measure AS mesh_uom,
        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.last_unit_cost/peachtree.tbl_item.ptos_uom_factor AS
        tbl_item.unit_of_measure AS sales_uom,
        tbl_item.weight AS sales_weight,
           + tbl_current.received
           - tbl_current.shipped AS inv_on_hand,
        tbl_current.allocated AS inv_committed,
           + 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 =
              ->  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 =
                    ->  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 =
                          ->  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 =
                                ->  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,

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

Reply via email to