Re: Joining a series of dates - Mailing list pgsql-novice

On Tue, 13 Sep 2005 12:00:54 -0400, Keith Worthington wrote
> Hi All,
>
> I am trying to generate a dynamic date value as part of a much
> larger query. The date must be at least one day prior to the ship
> date and must not be a weekend or a holiday.
>
> I have created a table to hold the holidays and I am now trying to
> develop the query to give me the date.  In the code below I have
> hard coded the order date and the ship date but in the final query
> these are already extracted as part of the larger query.
>
> This is my first time using a series and I am not sure how to
> connect it to the holiday table.  There may be other mistakes in my
> query as well.  At this time the error that I am getting is that the
> dates column soen't exist.
>
> Any hints or pointers to relevant documenation will be appreciated.
>
> CREATE TABLE tbl_holidays
> (
>   holiday date NOT NULL,
>   CONSTRAINT tbl_holidays_pkey PRIMARY KEY (holiday)
> )
> WITHOUT OIDS;
>
> INSERT INTO tbl_holidays (holiday) VALUES ('2005-11-24'::date);
> INSERT INTO tbl_holidays (holiday) VALUES ('2005-11-25'::date);
> INSERT INTO tbl_holidays (holiday) VALUES ('2005-12-26'::date);
> INSERT INTO tbl_holidays (holiday) VALUES ('2005-12-30'::date);
>
> SELECT '2005-09-07'::date + s.a AS dates  --Generate a years worth of
>   FROM generate_series(0,365) AS s(a)     --possible ship dates starting
>                                           --with the date the order was
>                                           --placed.
>   JOIN tbl_holidays                       --Join to the holiday
> table to    ON ( dates = tbl_holidays.holiday )   --eliminate
> holidays from the                                          --series.
> WHERE dates <= ( '2005-09-12'::date -    --The date must be at least
>                  interval '1 day'        --one day prior to the ship
> date.               )::date   AND extract( dow FROM dates
> --The date must be during the              ) IN (1, 2, 3, 4, 5)
>   --work week.
>
> Kind Regards,
> Keith

Replying to myself.

Here is what I have figured out.  I can use two querys to get the data and
then EXCEPT them together to eliminate the holidays.  That part works fine.
So using 2005-09-07 as an order date and 2005-11-28 as a ship date this is
what I end up with.

SELECT max( dates ) AS completion_date
  FROM ( SELECT '2005-09-07'::date + s.a AS dates
           FROM generate_series(0,365) AS s(a)
         EXCEPT
         SELECT holiday
           FROM interface.tbl_holidays
       ) AS valid_dates
 WHERE dates <= ('2005-11-28'::date - interval '1 day')::date
   AND NOT extract(dow FROM dates) IN (0, 6);

Out drops 2005-11-23 which is one working day prior to the ship date. :-)

Now, unfortunately, it gets ugly.  I place this into the larger query and my
execution time skyrockets from 668ms to 53804ms. :-(

Here is the complete query.
EXPLAIN ANALYZE SELECT merged_line_items.so_number,
        merged_line_items.so_line,
        merged_line_items.quantity AS line_item_quantity,
        merged_line_items.item_id AS line_item_id,
        merged_line_items.item_type AS line_item_type,
        merged_line_items.description AS line_item_description,
        merged_sales_orders.customer_name,
        merged_sales_orders.ship_to_name,
        merged_sales_orders.ship_to_state,
        merged_sales_orders.so_date AS order_date,
        merged_sales_orders.ship_by_date,

(
SELECT max( dates )
  FROM ( SELECT merged_sales_orders.so_date + s.a AS dates
           FROM generate_series(0,730) AS s(a)
         EXCEPT
         SELECT holiday
           FROM interface.tbl_holidays
       ) AS valid_dates
 WHERE dates <= (merged_sales_orders.ship_by_date - interval '1 day')::date
   AND NOT extract(dow FROM dates) IN (0, 6)
) AS completion_date,

        merged_sales_orders.so_note,
        production_notes.description AS line_item_production_note,
        merged_boms.so_subline,
        merged_boms.quantity AS bom_quantity,
        merged_boms.item_id AS bom_item_id,
        merged_boms.item_type AS bom_item_type,
        merged_boms.description AS bom_item_description,
        trunc((merged_boms.length_in / 12::real)::double precision)::integer
AS length_ft,
        merged_boms.length_in - 12::real * trunc((merged_boms.length_in /
12::real)::double precision) AS length_in,
        trunc((merged_boms.width_in / 12::real)::double precision)::integer AS
width_ft,
        merged_boms.width_in - 12::real * trunc((merged_boms.width_in /
12::real)::double precision) AS width_in,
        round((merged_boms.length_in / merged_boms.mesh_size)::double
precision)::integer AS length_bars,
        round((merged_boms.width_in / merged_boms.mesh_size)::double
precision)::integer AS width_bars,
        merged_boms.tension
   FROM ( SELECT tbl_line_item.so_number, tbl_line_item.so_line,
tbl_line_item.quantity, tbl_line_item.item_id, tbl_item.item_type,
tbl_item_description.description
           FROM sales_order.tbl_line_item
      LEFT JOIN sales_order.tbl_item_description ON tbl_line_item.so_number =
tbl_item_description.so_number AND tbl_line_item.so_line =
tbl_item_description.so_line
   LEFT JOIN peachtree.tbl_item ON tbl_line_item.item_id::text = tbl_item.id::text
  WHERE tbl_item.item_type::text = 'BAY'::text OR tbl_item.item_type::text =
'NET'::text OR tbl_item.item_type::text = 'VAS'::text) merged_line_items
   LEFT JOIN ( SELECT tbl_customer.name AS customer_name,
tbl_detail.so_number, tbl_detail.ship_to_name, tbl_detail.ship_to_state,
tbl_detail.so_date, tbl_detail.ship_by_date, tbl_note.description AS so_note
           FROM sales_order.tbl_detail
      LEFT JOIN sales_order.tbl_note ON tbl_detail.so_number = tbl_note.so_number
   LEFT JOIN peachtree.tbl_customer ON tbl_detail.customer_id::text =
tbl_customer.id::text) merged_sales_orders ON merged_line_items.so_number =
merged_sales_orders.so_number
   LEFT JOIN ( SELECT tbl_line_item.so_number, tbl_line_item.so_line - 1 AS
so_line, tbl_line_item.item_id, tbl_item.item_type, tbl_item.description
      FROM sales_order.tbl_line_item
   LEFT JOIN peachtree.tbl_item ON tbl_line_item.item_id::text = tbl_item.id::text
  WHERE tbl_item.item_type::text = 'PRO'::text AND tbl_item.id::text <> 'PN'::text
UNION ALL
    SELECT tbl_line_item.so_number, tbl_line_item.so_line - 1 AS so_line,
tbl_line_item.item_id, tbl_item.item_type, tbl_item_description.description
      FROM sales_order.tbl_line_item
   LEFT JOIN sales_order.tbl_item_description ON tbl_line_item.so_number =
tbl_item_description.so_number AND tbl_line_item.so_line =
tbl_item_description.so_line
   LEFT JOIN peachtree.tbl_item ON tbl_line_item.item_id::text = tbl_item.id::text
  WHERE tbl_item.id::text = 'PN'::text) production_notes ON
merged_line_items.so_number = production_notes.so_number AND
merged_line_items.so_line = production_notes.so_line
   LEFT JOIN ( SELECT tbl_item_bom.so_number, tbl_item_bom.so_line,
tbl_item_bom.so_subline, tbl_item_bom.quantity, tbl_item_bom.item_id,
tbl_item.item_type, tbl_item.description, tbl_mesh.mesh_size,
tbl_net_production.length_in, tbl_net_production.width_in,
tbl_net_production.tension
   FROM sales_order.tbl_item_bom
   LEFT JOIN peachtree.tbl_item ON tbl_item_bom.item_id::text = tbl_item.id::text
   LEFT JOIN peachtree.tbl_mesh ON tbl_item_bom.item_id::text =
tbl_mesh.item_id::text
   LEFT JOIN sales_order.tbl_net_production ON tbl_item_bom.so_number =
tbl_net_production.so_number AND tbl_item_bom.so_line =
tbl_net_production.so_line AND tbl_item_bom.so_subline =
tbl_net_production.so_subline) merged_boms ON merged_line_items.so_number =
merged_boms.so_number AND merged_line_items.so_line = merged_boms.so_line
  ORDER BY merged_line_items.so_number, merged_line_items.so_line,
merged_boms.so_subline;

Here is the EXPLAIN ANALYZE output with the new code.
"Sort  (cost=880645.11..880670.94 rows=10334 width=317) (actual
time=53785.664..53790.550 rows=7885 loops=1)"
"  Sort Key: tbl_line_item.so_number, tbl_line_item.so_line,
tbl_item_bom.so_subline"
"  ->  Merge Left Join  (cost=5732.80..879956.08 rows=10334 width=317) (actual
time=508.497..53762.260 rows=7885 loops=1)"
"        Merge Cond: (("outer".so_line = "inner".so_line) AND
("outer".so_number = "inner".so_number))"
"        ->  Merge Left Join  (cost=4497.19..4578.95 rows=10334 width=238)
(actual time=296.662..329.011 rows=3165 loops=1)"
"              Merge Cond: (("outer".so_line = "inner".so_line) AND
("outer".so_number = "inner".so_number))"
"              ->  Sort  (cost=3683.31..3709.14 rows=10334 width=206) (actual
time=247.378..249.776 rows=3165 loops=1)"
"                    Sort Key: tbl_line_item.so_line, tbl_line_item.so_number"
"                    ->  Hash Left Join  (cost=1526.72..2994.28 rows=10334
width=206) (actual time=73.057..226.797 rows=3165 loops=1)"
"                          Hash Cond: ("outer".so_number = "inner".so_number)"
"                          ->  Hash Left Join  (cost=196.80..1103.20
rows=10334 width=94) (actual time=9.012..146.714 rows=3165 loops=1)"
"                                Hash Cond: (("outer".item_id)::text =
("inner".id)::text)"
"                                Filter: ((("inner".item_type)::text =
'BAY'::text) OR (("inner".item_type)::text = 'NET'::text) OR
(("inner".item_type)::text = 'VAS'::text))"
"                                ->  Merge Left Join  (cost=0.00..673.88
rows=10334 width=86) (actual time=0.211..83.295 rows=10351 loops=1)"
"                                      Merge Cond: (("outer".so_number =
"inner".so_number) AND ("outer".so_line = "inner".so_line))"
"                                      ->  Index Scan using tbl_line_item_pkey
on tbl_line_item  (cost=0.00..368.45 rows=10334 width=20) (actual
time=0.111..21.762 rows=10351 loops=1)"
"                                      ->  Index Scan using
tbl_item_description_pkey on tbl_item_description  (cost=0.00..182.75
rows=5319 width=72) (actual time=0.077..11.356 rows=5329 loops=1)"
"                                ->  Hash  (cost=185.64..185.64 rows=4464
width=19) (actual time=7.814..7.814 rows=0 loops=1)"
"                                      ->  Seq Scan on tbl_item
(cost=0.00..185.64 rows=4464 width=19) (actual time=0.062..4.763 rows=2248
loops=1)"
"                          ->  Hash  (cost=1287.39..1287.39 rows=17012
width=116) (actual time=63.693..63.693 rows=0 loops=1)"
"                                ->  Hash Left Join  (cost=271.35..1287.39
rows=17012 width=116) (actual time=24.193..59.081 rows=2447 loops=1)"
"                                      Hash Cond: (("outer".customer_id)::text
= ("inner".id)::text)"
"                                      ->  Hash Left Join  (cost=9.60..770.46
rows=17012 width=102) (actual time=2.389..20.564 rows=2447 loops=1)"
"                                            Hash Cond: ("outer".so_number =
"inner".so_number)"
"                                            ->  Seq Scan on tbl_detail
(cost=0.00..672.12 rows=17012 width=51) (actual time=0.467..7.620 rows=2447
loops=1)"
"                                            ->  Hash  (cost=8.68..8.68
rows=368 width=55) (actual time=1.260..1.260 rows=0 loops=1)"
"                                                  ->  Seq Scan on tbl_note
(cost=0.00..8.68 rows=368 width=55) (actual time=0.007..0.707 rows=369 loops=1)"
"                                      ->  Hash  (cost=244.60..244.60
rows=6860 width=34) (actual time=21.030..21.030 rows=0 loops=1)"
"                                            ->  Seq Scan on tbl_customer
(cost=0.00..244.60 rows=6860 width=34) (actual time=0.529..12.765 rows=3470
loops=1)"
"              ->  Sort  (cost=813.89..815.25 rows=545 width=40) (actual
time=49.231..49.644 rows=493 loops=1)"
"                    Sort Key: production_notes.so_line,
production_notes.so_number"
"                    ->  Subquery Scan production_notes  (cost=12.49..789.12
rows=545 width=40) (actual time=28.682..48.044 rows=493 loops=1)"
"                          ->  Append  (cost=12.49..783.67 rows=545 width=90)
(actual time=28.677..46.974 rows=493 loops=1)"
"                                ->  Subquery Scan "*SELECT* 1"
(cost=12.49..283.82 rows=14 width=52) (actual time=19.815..19.815 rows=0 loops=1)"
"                                      ->  Hash Join  (cost=12.49..283.68
rows=14 width=52) (actual time=19.813..19.813 rows=0 loops=1)"
"                                            Hash Cond:
(("outer".item_id)::text = ("inner".id)::text)"
"                                            ->  Seq Scan on tbl_line_item
(cost=0.00..219.34 rows=10334 width=16) (actual time=0.004..10.130 rows=10351
loops=1)"
"                                            ->  Hash  (cost=12.48..12.48
rows=6 width=47) (actual time=0.145..0.145 rows=0 loops=1)"
"                                                  ->  Index Scan using
idx_tbl_item_item_type on tbl_item  (cost=0.00..12.48 rows=6 width=47) (actual
time=0.110..0.123 rows=2 loops=1)"
"                                                        Index Cond:
((item_type)::text = 'PRO'::text)"
"                                                        Filter: ((id)::text
<> 'PN'::text)"
"                                ->  Subquery Scan "*SELECT* 2"
(cost=269.21..499.85 rows=531 width=90) (actual time=8.857..26.555 rows=493
loops=1)"
"                                      ->  Nested Loop  (cost=269.21..494.54
rows=531 width=90) (actual time=8.851..25.480 rows=493 loops=1)"
"                                            ->  Index Scan using
tbl_part_pkey on tbl_item  (cost=0.00..3.07 rows=1 width=19) (actual
time=0.078..0.083 rows=1 loops=1)"
"                                                  Index Cond: ((id)::text =
'PN'::text)"
"                                            ->  Merge Right Join
(cost=269.21..484.83 rows=531 width=82) (actual time=8.757..23.975 rows=493
loops=1)"
"                                                  Merge Cond:
(("outer".so_number = "inner".so_number) AND ("outer".so_line = "inner".so_line))"
"                                                  ->  Index Scan using
tbl_item_description_pkey on tbl_item_description  (cost=0.00..182.75
rows=5319 width=72) (actual time=0.009..8.025 rows=5329 loops=1)"
"                                                  ->  Sort
(cost=269.21..270.54 rows=531 width=16) (actual time=8.675..9.020 rows=493
loops=1)"
"                                                        Sort Key:
tbl_line_item.so_number, tbl_line_item.so_line"
"                                                        ->  Seq Scan on
tbl_line_item  (cost=0.00..245.18 rows=531 width=16) (actual time=0.043..7.946
rows=493 loops=1)"
"                                                              Filter:
('PN'::text = (item_id)::text)"
"        ->  Sort  (cost=1235.61..1255.06 rows=7780 width=85) (actual
time=203.564..209.484 rows=7787 loops=1)"
"              Sort Key: tbl_item_bom.so_line, tbl_item_bom.so_number"
"              ->  Hash Left Join  (cost=278.85..732.81 rows=7780 width=85)
(actual time=20.843..149.866 rows=7787 loops=1)"
"                    Hash Cond: (("outer".so_number = "inner".so_number) AND
("outer".so_line = "inner".so_line) AND ("outer".so_subline =
"inner".so_subline))"
"                    ->  Hash Left Join  (cost=198.79..536.03 rows=7780
width=66) (actual time=10.046..98.495 rows=7787 loops=1)"
"                          Hash Cond: (("outer".item_id)::text =
("inner".item_id)::text)"
"                          ->  Hash Left Join  (cost=196.80..465.30 rows=7780
width=62) (actual time=8.814..64.220 rows=7787 loops=1)"
"                                Hash Cond: (("outer".item_id)::text =
("inner".id)::text)"
"                                ->  Seq Scan on tbl_item_bom
(cost=0.00..151.80 rows=7780 width=26) (actual time=0.004..9.964 rows=7787
loops=1)"
"                                ->  Hash  (cost=185.64..185.64 rows=4464
width=47) (actual time=8.130..8.130 rows=0 loops=1)"
"                                      ->  Seq Scan on tbl_item
(cost=0.00..185.64 rows=4464 width=47) (actual time=0.061..5.080 rows=2248
loops=1)"
"                          ->  Hash  (cost=1.79..1.79 rows=79 width=18)
(actual time=0.242..0.242 rows=0 loops=1)"
"                                ->  Seq Scan on tbl_mesh  (cost=0.00..1.79
rows=79 width=18) (actual time=0.009..0.122 rows=79 loops=1)"
"                    ->  Hash  (cost=58.61..58.61 rows=2861 width=29) (actual
time=10.062..10.062 rows=0 loops=1)"
"                          ->  Seq Scan on tbl_net_production
(cost=0.00..58.61 rows=2861 width=29) (actual time=0.013..5.636 rows=2865
loops=1)"
"        SubPlan"
"          ->  Aggregate  (cost=84.51..84.51 rows=1 width=4) (actual
time=6.728..6.729 rows=1 loops=7885)"
"                ->  Subquery Scan valid_dates  (cost=76.14..84.42 rows=33
width=4) (actual time=4.001..6.718 rows=9 loops=7885)"
"                      Filter: ((dates <= (($0 - '1 day'::interval))::date)
AND (date_part('dow'::text, (dates)::timestamp without time zone) <> 0::double
precision) AND (date_part('dow'::text, (dates)::timestamp without time zone)
<> 6::double precision))"
"                      ->  SetOp Except  (cost=76.14..81.16 rows=100 width=4)
(actual time=3.922..5.445 rows=719 loops=7885)"
"                            ->  Sort  (cost=76.14..78.65 rows=1004 width=4)
(actual time=3.902..4.376 rows=744 loops=7885)"
"                                  Sort Key: dates"
"                                  ->  Append  (cost=0.00..26.08 rows=1004
width=4) (actual time=0.240..3.079 rows=744 loops=7885)"
"                                        ->  Subquery Scan "*SELECT* 1"
(cost=0.00..25.00 rows=1000 width=4) (actual time=0.238..2.113 rows=731
loops=7885)"
"                                              ->  Function Scan on
generate_series s  (cost=0.00..15.00 rows=1000 width=4) (actual
time=0.219..0.964 rows=731 loops=7885)"
"                                        ->  Subquery Scan "*SELECT* 2"
(cost=0.00..1.08 rows=4 width=4) (actual time=0.005..0.038 rows=13 loops=7885)"
"                                              ->  Seq Scan on tbl_holidays
(cost=0.00..1.04 rows=4 width=4) (actual time=0.004..0.017 rows=13 loops=7885)"
"Total runtime: 53804.044 ms"



Here is the EXPLAIN ANALYZE output without the new code.
"Sort  (cost=7368.90..7394.74 rows=10334 width=317) (actual
time=650.959..655.829 rows=7885 loops=1)"
"  Sort Key: tbl_line_item.so_number, tbl_line_item.so_line,
tbl_item_bom.so_subline"
"  ->  Merge Left Join  (cost=5732.80..6679.88 rows=10334 width=317) (actual
time=496.260..628.888 rows=7885 loops=1)"
"        Merge Cond: (("outer".so_line = "inner".so_line) AND
("outer".so_number = "inner".so_number))"
"        ->  Merge Left Join  (cost=4497.19..4578.95 rows=10334 width=238)
(actual time=293.152..318.099 rows=3165 loops=1)"
"              Merge Cond: (("outer".so_line = "inner".so_line) AND
("outer".so_number = "inner".so_number))"
"              ->  Sort  (cost=3683.31..3709.14 rows=10334 width=206) (actual
time=244.063..246.058 rows=3165 loops=1)"
"                    Sort Key: tbl_line_item.so_line, tbl_line_item.so_number"
"                    ->  Hash Left Join  (cost=1526.72..2994.28 rows=10334
width=206) (actual time=70.484..224.614 rows=3165 loops=1)"
"                          Hash Cond: ("outer".so_number = "inner".so_number)"
"                          ->  Hash Left Join  (cost=196.80..1103.20
rows=10334 width=94) (actual time=8.959..147.018 rows=3165 loops=1)"
"                                Hash Cond: (("outer".item_id)::text =
("inner".id)::text)"
"                                Filter: ((("inner".item_type)::text =
'BAY'::text) OR (("inner".item_type)::text = 'NET'::text) OR
(("inner".item_type)::text = 'VAS'::text))"
"                                ->  Merge Left Join  (cost=0.00..673.88
rows=10334 width=86) (actual time=0.192..82.894 rows=10351 loops=1)"
"                                      Merge Cond: (("outer".so_number =
"inner".so_number) AND ("outer".so_line = "inner".so_line))"
"                                      ->  Index Scan using tbl_line_item_pkey
on tbl_line_item  (cost=0.00..368.45 rows=10334 width=20) (actual
time=0.092..20.062 rows=10351 loops=1)"
"                                      ->  Index Scan using
tbl_item_description_pkey on tbl_item_description  (cost=0.00..182.75
rows=5319 width=72) (actual time=0.077..12.190 rows=5329 loops=1)"
"                                ->  Hash  (cost=185.64..185.64 rows=4464
width=19) (actual time=7.787..7.787 rows=0 loops=1)"
"                                      ->  Seq Scan on tbl_item
(cost=0.00..185.64 rows=4464 width=19) (actual time=0.063..4.680 rows=2248
loops=1)"
"                          ->  Hash  (cost=1287.39..1287.39 rows=17012
width=116) (actual time=61.143..61.143 rows=0 loops=1)"
"                                ->  Hash Left Join  (cost=271.35..1287.39
rows=17012 width=116) (actual time=26.560..57.356 rows=2447 loops=1)"
"                                      Hash Cond: (("outer".customer_id)::text
= ("inner".id)::text)"
"                                      ->  Hash Left Join  (cost=9.60..770.46
rows=17012 width=102) (actual time=2.907..21.550 rows=2447 loops=1)"
"                                            Hash Cond: ("outer".so_number =
"inner".so_number)"
"                                            ->  Seq Scan on tbl_detail
(cost=0.00..672.12 rows=17012 width=51) (actual time=0.882..11.461 rows=2447
loops=1)"
"                                            ->  Hash  (cost=8.68..8.68
rows=368 width=55) (actual time=1.346..1.346 rows=0 loops=1)"
"                                                  ->  Seq Scan on tbl_note
(cost=0.00..8.68 rows=368 width=55) (actual time=0.066..0.784 rows=369 loops=1)"
"                                      ->  Hash  (cost=244.60..244.60
rows=6860 width=34) (actual time=22.899..22.899 rows=0 loops=1)"
"                                            ->  Seq Scan on tbl_customer
(cost=0.00..244.60 rows=6860 width=34) (actual time=0.729..14.626 rows=3470
loops=1)"
"              ->  Sort  (cost=813.89..815.25 rows=545 width=40) (actual
time=49.035..49.368 rows=493 loops=1)"
"                    Sort Key: production_notes.so_line,
production_notes.so_number"
"                    ->  Subquery Scan production_notes  (cost=12.49..789.12
rows=545 width=40) (actual time=28.696..47.889 rows=493 loops=1)"
"                          ->  Append  (cost=12.49..783.67 rows=545 width=90)
(actual time=28.691..46.793 rows=493 loops=1)"
"                                ->  Subquery Scan "*SELECT* 1"
(cost=12.49..283.82 rows=14 width=52) (actual time=19.932..19.932 rows=0 loops=1)"
"                                      ->  Hash Join  (cost=12.49..283.68
rows=14 width=52) (actual time=19.929..19.929 rows=0 loops=1)"
"                                            Hash Cond:
(("outer".item_id)::text = ("inner".id)::text)"
"                                            ->  Seq Scan on tbl_line_item
(cost=0.00..219.34 rows=10334 width=16) (actual time=0.004..10.194 rows=10351
loops=1)"
"                                            ->  Hash  (cost=12.48..12.48
rows=6 width=47) (actual time=0.148..0.148 rows=0 loops=1)"
"                                                  ->  Index Scan using
idx_tbl_item_item_type on tbl_item  (cost=0.00..12.48 rows=6 width=47) (actual
time=0.113..0.126 rows=2 loops=1)"
"                                                        Index Cond:
((item_type)::text = 'PRO'::text)"
"                                                        Filter: ((id)::text
<> 'PN'::text)"
"                                ->  Subquery Scan "*SELECT* 2"
(cost=269.21..499.85 rows=531 width=90) (actual time=8.756..26.255 rows=493
loops=1)"
"                                      ->  Nested Loop  (cost=269.21..494.54
rows=531 width=90) (actual time=8.750..25.156 rows=493 loops=1)"
"                                            ->  Index Scan using
tbl_part_pkey on tbl_item  (cost=0.00..3.07 rows=1 width=19) (actual
time=0.061..0.066 rows=1 loops=1)"
"                                                  Index Cond: ((id)::text =
'PN'::text)"
"                                            ->  Merge Right Join
(cost=269.21..484.83 rows=531 width=82) (actual time=8.673..23.692 rows=493
loops=1)"
"                                                  Merge Cond:
(("outer".so_number = "inner".so_number) AND ("outer".so_line = "inner".so_line))"
"                                                  ->  Index Scan using
tbl_item_description_pkey on tbl_item_description  (cost=0.00..182.75
rows=5319 width=72) (actual time=0.009..7.869 rows=5329 loops=1)"
"                                                  ->  Sort
(cost=269.21..270.54 rows=531 width=16) (actual time=8.595..8.925 rows=493
loops=1)"
"                                                        Sort Key:
tbl_line_item.so_number, tbl_line_item.so_line"
"                                                        ->  Seq Scan on
tbl_line_item  (cost=0.00..245.18 rows=531 width=16) (actual time=0.043..7.898
rows=493 loops=1)"
"                                                              Filter:
('PN'::text = (item_id)::text)"
"        ->  Sort  (cost=1235.61..1255.06 rows=7780 width=85) (actual
time=203.038..208.112 rows=7787 loops=1)"
"              Sort Key: tbl_item_bom.so_line, tbl_item_bom.so_number"
"              ->  Hash Left Join  (cost=278.85..732.81 rows=7780 width=85)
(actual time=20.913..150.811 rows=7787 loops=1)"
"                    Hash Cond: (("outer".so_number = "inner".so_number) AND
("outer".so_line = "inner".so_line) AND ("outer".so_subline =
"inner".so_subline))"
"                    ->  Hash Left Join  (cost=198.79..536.03 rows=7780
width=66) (actual time=9.832..98.817 rows=7787 loops=1)"
"                          Hash Cond: (("outer".item_id)::text =
("inner".item_id)::text)"
"                          ->  Hash Left Join  (cost=196.80..465.30 rows=7780
width=62) (actual time=8.608..64.237 rows=7787 loops=1)"
"                                Hash Cond: (("outer".item_id)::text =
("inner".id)::text)"
"                                ->  Seq Scan on tbl_item_bom
(cost=0.00..151.80 rows=7780 width=26) (actual time=0.004..10.151 rows=7787
loops=1)"
"                                ->  Hash  (cost=185.64..185.64 rows=4464
width=47) (actual time=7.953..7.953 rows=0 loops=1)"
"                                      ->  Seq Scan on tbl_item
(cost=0.00..185.64 rows=4464 width=47) (actual time=0.061..4.939 rows=2248
loops=1)"
"                          ->  Hash  (cost=1.79..1.79 rows=79 width=18)
(actual time=0.237..0.237 rows=0 loops=1)"
"                                ->  Seq Scan on tbl_mesh  (cost=0.00..1.79
rows=79 width=18) (actual time=0.010..0.122 rows=79 loops=1)"
"                    ->  Hash  (cost=58.61..58.61 rows=2861 width=29) (actual
time=10.329..10.329 rows=0 loops=1)"
"                          ->  Seq Scan on tbl_net_production
(cost=0.00..58.61 rows=2861 width=29) (actual time=0.051..5.965 rows=2865
loops=1)"
"Total runtime: 667.997 ms"

Kind Regards,
Keith