Expensive where clause - Mailing list pgsql-novice

Hi All,

I have been working on a SQL statement that contains a WHERE clause of the
form WHERE column1 > column2.  The query runs pretty quickly (285ms) without
the WHERE clause but slows to a relative crawl (5850ms) when it is included.
Anu suggestions on how to improve the performance would be greatly appreciated.

Kind Regards,
Keith

SELECT all_shipped_items.item_id,
       sum (all_shipped_items.quantity) AS quantity
--Get the inventory items and the last date they were counted.
  FROM (
         SELECT items.id AS item_id,
                COALESCE(last_inventory.inventory_date, CAST('0001-01-01' AS
date)) AS inventory_date
           FROM peachtree.tbl_item AS items
           LEFT OUTER JOIN ( SELECT DISTINCT ON ( inventory.tbl_data.item_id)
                                    inventory.tbl_data.item_id,
                                    inventory.tbl_detail.inventory_date
                               FROM inventory.tbl_data
                              INNER JOIN inventory.tbl_detail
                                 ON ( inventory.tbl_data.inventory_id =
inventory.tbl_detail.inventory_id )
                              ORDER BY inventory.tbl_data.item_id,
                                       inventory.tbl_data.inventory_id DESC
                            ) AS last_inventory
             ON ( items.id = last_inventory.item_id )
          WHERE ( NOT items.inactive )
            AND items.item_class = 1 -- stock item
            AND items.item_type IN ( 'DIR', 'NET' )
       ) AS all_items
--Get the inventory items and the date they were shipped from the invoices.
  RIGHT OUTER JOIN (
--            Get the direct items from tbl_line_item.
              SELECT invoice.tbl_line_item.quantity,
                     invoice.tbl_line_item.item_id,
                     invoice.tbl_detail.ship_date
                FROM invoice.tbl_line_item
                JOIN peachtree.tbl_item
                  ON ( invoice.tbl_line_item.item_id = peachtree.tbl_item.id )
                JOIN invoice.tbl_detail
                  ON ( invoice.tbl_line_item.i_number =
                       invoice.tbl_detail.i_number )
               WHERE ( NOT peachtree.tbl_item.inactive )
                 AND peachtree.tbl_item.item_class = 1 -- stock item
                 AND peachtree.tbl_item.item_type = 'DIR'
              UNION ALL
--            Get the assembly items from tbl_line_item.
              SELECT invoice.tbl_line_item.quantity *
                        peachtree.tbl_assembly.quantity
                     AS quantity,
                     peachtree.tbl_assembly.component_id AS item_id,
                     invoice.tbl_detail.ship_date
                FROM invoice.tbl_line_item
                JOIN peachtree.tbl_assembly
                  ON ( invoice.tbl_line_item.item_id =
                       peachtree.tbl_assembly.id )
                JOIN peachtree.tbl_item
                  ON ( invoice.tbl_line_item.item_id = peachtree.tbl_item.id )
                JOIN invoice.tbl_detail
                  ON ( invoice.tbl_line_item.i_number =
                       invoice.tbl_detail.i_number )
               WHERE ( NOT peachtree.tbl_item.inactive )
                 AND peachtree.tbl_item.item_type = 'ASY'
              UNION ALL
--            Get the direct items from tbl_item_bom.
              SELECT merged_invoice.quantity *
                        sales_order.tbl_item_bom.quantity
                     AS quantity,
                     sales_order.tbl_item_bom.item_id,
                     merged_invoice.ship_date
                FROM sales_order.tbl_item_bom
                JOIN ( SELECT invoice.tbl_detail.i_number,
                              invoice.tbl_detail.so_number,
                              invoice.tbl_detail.ship_date,
                              invoice.tbl_line_item.i_line,
                              invoice.tbl_line_item.quantity,
                              invoice.tbl_line_item.item_id
                         FROM invoice.tbl_detail
                         JOIN invoice.tbl_line_item
                           ON ( invoice.tbl_detail.i_number =
invoice.tbl_line_item.i_number )
                     ) AS merged_invoice
                  ON ( sales_order.tbl_item_bom.number =
                       merged_invoice.so_number AND
                       sales_order.tbl_item_bom.line =
                       merged_invoice.i_line )
                JOIN peachtree.tbl_item
                  ON ( sales_order.tbl_item_bom.item_id = peachtree.tbl_item.id )
               WHERE ( NOT peachtree.tbl_item.inactive )
                 AND peachtree.tbl_item.item_class = 1 -- stock item
                 AND peachtree.tbl_item.item_type IN ( 'DIR', 'NET' )
              UNION ALL
--            Get the assembly items from tbl_item_bom.
              SELECT merged_invoice.quantity *
                        sales_order.tbl_item_bom.quantity *
                        peachtree.tbl_assembly.quantity
                     AS quantity,
                     peachtree.tbl_assembly.component_id AS item_id,
                     merged_invoice.ship_date
                FROM sales_order.tbl_item_bom
                JOIN ( SELECT invoice.tbl_detail.i_number,
                              invoice.tbl_detail.so_number,
                              invoice.tbl_detail.ship_date,
                              invoice.tbl_line_item.i_line,
                              invoice.tbl_line_item.quantity,
                              invoice.tbl_line_item.item_id
                         FROM invoice.tbl_detail
                         JOIN invoice.tbl_line_item
                           ON ( invoice.tbl_detail.i_number =
invoice.tbl_line_item.i_number )
                     ) AS merged_invoice
                  ON ( sales_order.tbl_item_bom.number =
                       merged_invoice.so_number AND
                       sales_order.tbl_item_bom.line =
                       merged_invoice.i_line )
                JOIN peachtree.tbl_assembly
                  ON ( sales_order.tbl_item_bom.item_id =
peachtree.tbl_assembly.id )
                JOIN peachtree.tbl_item
                  ON ( sales_order.tbl_item_bom.item_id = peachtree.tbl_item.id )
               WHERE ( NOT peachtree.tbl_item.inactive )
                 AND peachtree.tbl_item.item_type = 'ASY'
            ) AS all_shipped_items
    ON ( all_items.item_id = all_shipped_items.item_id )
 WHERE all_shipped_items.ship_date > all_items.inventory_date
 GROUP BY all_shipped_items.item_id