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

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

On Fri, 18 Feb 2005, Keith Worthington wrote:

> 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.

Explain analyze output for the query with and without the clause would
probably be useful for analysis.

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to majordomo@postgresql.org)

On Fri, 18 Feb 2005 21:55:29 -0800 (PST), Stephan Szabo wrote
> On Fri, 18 Feb 2005, Keith Worthington wrote:
>
> > 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.
> > Any suggestions on how to improve the performance would be
> > greatly appreciated.
>
> Explain analyze output for the query with and without the clause
> would probably be useful for analysis.

Here is the explain analyze output with the WHERE
clause commented out. This one actually ran slow. Usually it is only a few
hundred ms without the WHERE clause.

The larger picture is that I have several of these queries running as
subqueries of a larger statement. The whole statement takes upwords of 5
minutes to run on a single user machine that has minimal data. If I can't
find a way to improve this query I am going to have to go back the the drawing
board and figure out a different way of tackling the problem so that
performance is acceptable.

Thanks in advance for your help.

QUERY PLAN

-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
HashAggregate (cost=646.35..646.55 rows=80 width=36) (actual
time=1291.409..1291.633 rows=162 loops=1)
-> Hash Left Join (cost=142.94..645.95 rows=80 width=36) (actual
time=1253.595..1288.857 rows=884 loops=1)
Hash Cond: (("outer".item_id)::text = ("inner".item_id)::text)
-> Subquery Scan all_shipped_items (cost=42.06..544.66 rows=80
width=36) (actual time=3.579..34.674 rows=884 loops=1)
-> Append (cost=42.06..543.86 rows=80 width=40) (actual
time=3.574..33.078 rows=884 loops=1)
-> Subquery Scan "*SELECT* 1" (cost=42.06..80.49 rows=1
width=32) (actual time=3.572..13.620 rows=376 loops=1)
-> Nested Loop (cost=42.06..80.48 rows=1
width=32) (actual time=3.568..12.854 rows=376 loops=1)
-> Hash Join (cost=42.06..74.62 rows=1
width=32) (actual time=3.529..8.179 rows=376 loops=1)
Hash Cond: (("outer".item_id)::text =
("inner".id)::text)
-> Seq Scan on tbl_line_item
(cost=0.00..27.37 rows=1037 width=32) (actual time=0.014..1.486 rows=1087 loops=1)
-> Hash (cost=42.06..42.06 rows=1
width=24) (actual time=3.453..3.453 rows=0 loops=1)
-> Index Scan using
idx_tbl_item_item_type on tbl_item (cost=0.00..42.06 rows=1 width=24) (actual
time=0.035..2.789 rows=502 loops=1)
Index Cond:
((item_type)::text = 'DIR'::text)
Filter: ((NOT inactive) AND
(item_class = 1))
-> Index Scan using tbl_detail_pkey on
tbl_detail (cost=0.00..5.84 rows=1 width=8) (actual time=0.005..0.007 rows=1
loops=376)
Index Cond: ("outer".i_number =
tbl_detail.i_number)
-> Subquery Scan "*SELECT* 2" (cost=89.47..199.26
rows=56 width=36) (actual time=6.516..10.144 rows=460 loops=1)
-> Hash Join (cost=89.47..198.70 rows=56
width=36) (actual time=6.511..9.267 rows=460 loops=1)
Hash Cond: (("outer".id)::text =
("inner".item_id)::text)
-> Seq Scan on tbl_assembly
(cost=0.00..87.30 rows=4230 width=52) (actual time=0.809..1.470 rows=587 loops=1)
-> Hash (cost=89.46..89.46 rows=3 width=56)
(actual time=5.637..5.637 rows=0 loops=1)
-> Hash Join (cost=74.64..89.46
rows=3 width=56) (actual time=4.540..5.448 rows=137 loops=1)
Hash Cond: ("outer".i_number =
"inner".i_number)
-> Seq Scan on tbl_detail
(cost=0.00..13.53 rows=253 width=8) (actual time=0.003..0.341 rows=281 loops=1)
-> Hash (cost=74.63..74.63
rows=3 width=56) (actual time=4.483..4.483 rows=0 loops=1)
-> Hash Join
(cost=42.04..74.63 rows=3 width=56) (actual time=1.540..4.278 rows=137 loops=1)
Hash Cond:
(("outer".item_id)::text = ("inner".id)::text)
-> Seq Scan on
tbl_line_item (cost=0.00..27.37 rows=1037 width=32) (actual time=0.004..1.334
rows=1087 loops=1)
-> Hash
(cost=42.03..42.03 rows=5 width=24) (actual time=1.333..1.333 rows=0 loops=1)
-> Index Scan
using idx_tbl_item_item_type on tbl_item (cost=0.00..42.03 rows=5 width=24)
(actual time=0.035..1.078 rows=205 loops=1)
Index
Cond: ((item_type)::text = 'ASY'::text)
Filter:
(NOT inactive)
-> Subquery Scan "*SELECT* 3" (cost=89.66..116.32
rows=1 width=36) (actual time=5.076..6.629 rows=48 loops=1)
-> Nested Loop (cost=89.66..116.31 rows=1
width=36) (actual time=5.071..6.534 rows=48 loops=1)
-> Hash Join (cost=89.66..104.47 rows=2
width=38) (actual time=5.013..5.695 rows=48 loops=1)
Hash Cond: ("outer".so_number =
"inner".number)
-> Seq Scan on tbl_detail
(cost=0.00..13.53 rows=253 width=12) (actual time=0.011..0.341 rows=281 loops=1)
-> Hash (cost=89.66..89.66 rows=1
width=34) (actual time=4.964..4.964 rows=0 loops=1)
-> Hash Join (cost=84.17..89.66
rows=1 width=34) (actual time=4.395..4.882 rows=60 loops=1)
Hash Cond:
(("outer".item_id)::text = ("inner".id)::text)
-> Seq Scan on
tbl_item_bom (cost=0.00..4.32 rows=232 width=34) (actual time=0.014..0.097
rows=61 loops=1)
-> Hash
(cost=84.16..84.16 rows=1 width=24) (actual time=4.351..4.351 rows=0 loops=1)
-> Index Scan using
idx_tbl_item_item_type, idx_tbl_item_item_type on tbl_item (cost=0.00..84.16
rows=1 width=24) (actual time=0.035..3.586 rows=566 loops=1)
Index Cond:
(((item_type)::text = 'DIR'::text) OR ((item_type)::text = 'NET'::text))
Filter: ((NOT
inactive) AND (item_class = 1))
-> Index Scan using tbl_line_item_pkey on
tbl_line_item (cost=0.00..5.90 rows=1 width=10) (actual time=0.008..0.010
rows=1 loops=48)
Index Cond: (("outer".i_number =
tbl_line_item.i_number) AND ("outer".line = tbl_line_item.i_line))
-> Subquery Scan "*SELECT* 4" (cost=47.54..147.80
rows=22 width=40) (actual time=1.594..1.594 rows=0 loops=1)
-> Nested Loop (cost=47.54..147.58 rows=22
width=40) (actual time=1.591..1.591 rows=0 loops=1)
-> Nested Loop (cost=47.54..74.18 rows=1
width=60) (actual time=1.589..1.589 rows=0 loops=1)
-> Hash Join (cost=47.54..62.35
rows=2 width=62) (actual time=1.587..1.587 rows=0 loops=1)
Hash Cond: ("outer".so_number =
"inner".number)
-> Seq Scan on tbl_detail
(cost=0.00..13.53 rows=253 width=12) (never executed)
-> Hash (cost=47.53..47.53
rows=1 width=58) (actual time=1.565..1.565 rows=0 loops=1)
-> Hash Join
(cost=42.04..47.53 rows=1 width=58) (actual time=1.563..1.563 rows=0 loops=1)
Hash Cond:
(("outer".item_id)::text = ("inner".id)::text)
-> Seq Scan on
tbl_item_bom (cost=0.00..4.32 rows=232 width=34) (actual time=0.004..0.071
rows=61 loops=1)
-> Hash
(cost=42.03..42.03 rows=5 width=24) (actual time=1.408..1.408 rows=0 loops=1)
-> Index Scan
using idx_tbl_item_item_type on tbl_item (cost=0.00..42.03 rows=5 width=24)
(actual time=0.022..1.135 rows=205 loops=1)
Index
Cond: ((item_type)::text = 'ASY'::text)
Filter:
(NOT inactive)
-> Index Scan using tbl_line_item_pkey
on tbl_line_item (cost=0.00..5.90 rows=1 width=10) (never executed)
Index Cond: (("outer".i_number =
tbl_line_item.i_number) AND ("outer".line = tbl_line_item.i_line))
-> Index Scan using tbl_assembly_pkey on
tbl_assembly (cost=0.00..73.01 rows=22 width=52) (never executed)
Index Cond: (("outer".id)::text =
(tbl_assembly.id)::text)
-> Hash (cost=100.87..100.87 rows=1 width=24) (actual
time=1249.985..1249.985 rows=0 loops=1)
-> Subquery Scan all_items (cost=15.05..100.87 rows=1
width=24) (actual time=5.583..1248.912 rows=566 loops=1)
-> Nested Loop Left Join (cost=15.05..100.86 rows=1
width=28) (actual time=5.581..1247.873 rows=566 loops=1)
Join Filter: (("outer".id)::text =
("inner".item_id)::text)
-> Index Scan using idx_tbl_item_item_type,
idx_tbl_item_item_type on tbl_item items (cost=0.00..84.16 rows=1 width=24)
(actual time=0.093..5.702 rows=566 loops=1)
Index Cond: (((item_type)::text =
'DIR'::text) OR ((item_type)::text = 'NET'::text))
Filter: ((NOT inactive) AND (item_class = 1))
-> Subquery Scan last_inventory
(cost=15.05..15.95 rows=60 width=28) (actual time=0.010..1.784 rows=445 loops=566)
-> Unique (cost=15.05..15.35 rows=60
width=32) (actual time=0.008..1.043 rows=445 loops=566)
-> Sort (cost=15.05..15.20 rows=60
width=32) (actual time=0.006..0.289 rows=445 loops=566)
Sort Key: tbl_data.item_id,
tbl_data.inventory_id
-> Hash Join (cost=1.30..13.28
rows=60 width=32) (actual time=0.117..1.675 rows=445 loops=1)
Hash Cond:
("outer".inventory_id = "inner".inventory_id)
-> Seq Scan on tbl_data
(cost=0.00..8.92 rows=492 width=28) (actual time=0.009..0.474 rows=445 loops=1)
-> Hash (cost=1.24..1.24
rows=24 width=8) (actual time=0.031..0.031 rows=0 loops=1)
-> Seq Scan on
tbl_detail (cost=0.00..1.24 rows=24 width=8) (actual time=0.014..0.017 rows=2
loops=1)
Total runtime: 1292.896 ms
(82 rows)



Here is the explain analyze output with the WHERE clause active.

QUERY PLAN

-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
HashAggregate (cost=646.74..646.74 rows=1 width=36) (actual
time=11422.145..11422.334 rows=147 loops=1)
-> Nested Loop (cost=57.11..646.74 rows=1 width=36) (actual
time=17.359..11420.069 rows=504 loops=1)
Join Filter: ((("outer".item_id)::text = ("inner".item_id)::text) AND
("inner".ship_date > "outer".inventory_date))
-> Subquery Scan all_items (cost=15.05..100.87 rows=1 width=28)
(actual time=5.494..1269.377 rows=566 loops=1)
-> Nested Loop Left Join (cost=15.05..100.86 rows=1 width=28)
(actual time=5.490..1267.843 rows=566 loops=1)
Join Filter: (("outer".id)::text = ("inner".item_id)::text)
-> Index Scan using idx_tbl_item_item_type,
idx_tbl_item_item_type on tbl_item items (cost=0.00..84.16 rows=1 width=24)
(actual time=0.094..6.970 rows=566 loops=1)
Index Cond: (((item_type)::text = 'DIR'::text) OR
((item_type)::text = 'NET'::text))
Filter: ((NOT inactive) AND (item_class = 1))
-> Subquery Scan last_inventory (cost=15.05..15.95
rows=60 width=28) (actual time=0.011..1.811 rows=445 loops=566)
-> Unique (cost=15.05..15.35 rows=60 width=32)
(actual time=0.008..1.052 rows=445 loops=566)
-> Sort (cost=15.05..15.20 rows=60
width=32) (actual time=0.006..0.289 rows=445 loops=566)
Sort Key: tbl_data.item_id,
tbl_data.inventory_id
-> Hash Join (cost=1.30..13.28
rows=60 width=32) (actual time=0.124..1.661 rows=445 loops=1)
Hash Cond: ("outer".inventory_id
= "inner".inventory_id)
-> Seq Scan on tbl_data
(cost=0.00..8.92 rows=492 width=28) (actual time=0.008..0.479 rows=445 loops=1)
-> Hash (cost=1.24..1.24
rows=24 width=8) (actual time=0.032..0.032 rows=0 loops=1)
-> Seq Scan on tbl_detail
(cost=0.00..1.24 rows=24 width=8) (actual time=0.015..0.018 rows=2 loops=1)
-> Subquery Scan all_shipped_items (cost=42.06..544.66 rows=80
width=40) (actual time=0.056..17.002 rows=884 loops=566)
-> Append (cost=42.06..543.86 rows=80 width=40) (actual
time=0.054..15.355 rows=884 loops=566)
-> Subquery Scan "*SELECT* 1" (cost=42.06..80.49 rows=1
width=32) (actual time=0.053..8.856 rows=376 loops=566)
-> Nested Loop (cost=42.06..80.48 rows=1
width=32) (actual time=0.050..8.131 rows=376 loops=566)
-> Hash Join (cost=42.06..74.62 rows=1
width=32) (actual time=0.034..4.011 rows=376 loops=566)
Hash Cond: (("outer".item_id)::text =
("inner".id)::text)
-> Seq Scan on tbl_line_item
(cost=0.00..27.37 rows=1037 width=32) (actual time=0.003..1.220 rows=1087
loops=566)
-> Hash (cost=42.06..42.06 rows=1
width=24) (actual time=4.029..4.029 rows=0 loops=1)
-> Index Scan using
idx_tbl_item_item_type on tbl_item (cost=0.00..42.06 rows=1 width=24) (actual
time=0.032..3.350 rows=502 loops=1)
Index Cond:
((item_type)::text = 'DIR'::text)
Filter: ((NOT inactive) AND
(item_class = 1))
-> Index Scan using tbl_detail_pkey on
tbl_detail (cost=0.00..5.84 rows=1 width=8) (actual time=0.005..0.006 rows=1
loops=212816)
Index Cond: ("outer".i_number =
tbl_detail.i_number)
-> Subquery Scan "*SELECT* 2" (cost=89.47..199.26
rows=56 width=36) (actual time=0.365..3.797 rows=460 loops=566)
-> Hash Join (cost=89.47..198.70 rows=56
width=36) (actual time=0.361..2.979 rows=460 loops=566)
Hash Cond: (("outer".id)::text =
("inner".item_id)::text)
-> Seq Scan on tbl_assembly
(cost=0.00..87.30 rows=4230 width=52) (actual time=0.335..0.931 rows=587
loops=566)
-> Hash (cost=89.46..89.46 rows=3 width=56)
(actual time=5.369..5.369 rows=0 loops=1)
-> Hash Join (cost=74.64..89.46
rows=3 width=56) (actual time=4.359..5.177 rows=137 loops=1)
Hash Cond: ("outer".i_number =
"inner".i_number)
-> Seq Scan on tbl_detail
(cost=0.00..13.53 rows=253 width=8) (actual time=0.004..0.353 rows=281 loops=1)
-> Hash (cost=74.63..74.63
rows=3 width=56) (actual time=4.307..4.307 rows=0 loops=1)
-> Hash Join
(cost=42.04..74.63 rows=3 width=56) (actual time=1.558..4.117 rows=137 loops=1)
Hash Cond:
(("outer".item_id)::text = ("inner".id)::text)
-> Seq Scan on
tbl_line_item (cost=0.00..27.37 rows=1037 width=32) (actual time=0.004..1.138
rows=1087 loops=1)
-> Hash
(cost=42.03..42.03 rows=5 width=24) (actual time=1.356..1.356 rows=0 loops=1)
-> Index Scan
using idx_tbl_item_item_type on tbl_item (cost=0.00..42.03 rows=5 width=24)
(actual time=0.034..1.089 rows=205 loops=1)
Index
Cond: ((item_type)::text = 'ASY'::text)
Filter:
(NOT inactive)
-> Subquery Scan "*SELECT* 3" (cost=89.66..116.32
rows=1 width=36) (actual time=0.052..1.441 rows=48 loops=566)
-> Nested Loop (cost=89.66..116.31 rows=1
width=36) (actual time=0.048..1.343 rows=48 loops=566)
-> Hash Join (cost=89.66..104.47 rows=2
width=38) (actual time=0.023..0.698 rows=48 loops=566)
Hash Cond: ("outer".so_number =
"inner".number)
-> Seq Scan on tbl_detail
(cost=0.00..13.53 rows=253 width=12) (actual time=0.003..0.333 rows=281 loops=566)
-> Hash (cost=89.66..89.66 rows=1
width=34) (actual time=4.781..4.781 rows=0 loops=1)
-> Hash Join (cost=84.17..89.66
rows=1 width=34) (actual time=4.281..4.695 rows=60 loops=1)
Hash Cond:
(("outer".item_id)::text = ("inner".id)::text)
-> Seq Scan on
tbl_item_bom (cost=0.00..4.32 rows=232 width=34) (actual time=0.008..0.096
rows=61 loops=1)
-> Hash
(cost=84.16..84.16 rows=1 width=24) (actual time=4.237..4.237 rows=0 loops=1)
-> Index Scan using
idx_tbl_item_item_type, idx_tbl_item_item_type on tbl_item (cost=0.00..84.16
rows=1 width=24) (actual time=0.033..3.470 rows=566 loops=1)
Index Cond:
(((item_type)::text = 'DIR'::text) OR ((item_type)::text = 'NET'::text))
Filter: ((NOT
inactive) AND (item_class = 1))
-> Index Scan using tbl_line_item_pkey on
tbl_line_item (cost=0.00..5.90 rows=1 width=10) (actual time=0.007..0.008
rows=1 loops=27168)
Index Cond: (("outer".i_number =
tbl_line_item.i_number) AND ("outer".line = tbl_line_item.i_line))
-> Subquery Scan "*SELECT* 4" (cost=47.54..147.80
rows=22 width=40) (actual time=0.166..0.166 rows=0 loops=566)
-> Nested Loop (cost=47.54..147.58 rows=22
width=40) (actual time=0.164..0.164 rows=0 loops=566)
-> Nested Loop (cost=47.54..74.18 rows=1
width=60) (actual time=0.162..0.162 rows=0 loops=566)
-> Hash Join (cost=47.54..62.35
rows=2 width=62) (actual time=0.161..0.161 rows=0 loops=566)
Hash Cond: ("outer".so_number =
"inner".number)
-> Seq Scan on tbl_detail
(cost=0.00..13.53 rows=253 width=12) (never executed)
-> Hash (cost=47.53..47.53
rows=1 width=58) (actual time=1.432..81.382 rows=0 loops=1)
-> Hash Join
(cost=42.04..47.53 rows=1 width=58) (actual time=1.430..80.405 rows=0 loops=1)
Hash Cond:
(("outer".item_id)::text = ("inner".id)::text)
-> Seq Scan on
tbl_item_bom (cost=0.00..4.32 rows=232 width=34) (actual time=0.004..39.690
rows=34526 loops=1)
-> Hash
(cost=42.03..42.03 rows=5 width=24) (actual time=1.278..1.278 rows=0 loops=1)
-> Index Scan
using idx_tbl_item_item_type on tbl_item (cost=0.00..42.03 rows=5 width=24)
(actual time=0.024..1.024 rows=205 loops=1)
Index
Cond: ((item_type)::text = 'ASY'::text)
Filter:
(NOT inactive)
-> Index Scan using tbl_line_item_pkey
on tbl_line_item (cost=0.00..5.90 rows=1 width=10) (never executed)
Index Cond: (("outer".i_number =
tbl_line_item.i_number) AND ("outer".line = tbl_line_item.i_line))
-> Index Scan using tbl_assembly_pkey on
tbl_assembly (cost=0.00..73.01 rows=22 width=52) (never executed)
Index Cond: (("outer".id)::text =
(tbl_assembly.id)::text)
Total runtime: 11423.492 ms
(81 rows)

Kind Regards,
Keith

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

On Sat, 19 Feb 2005, Keith Worthington wrote:

> On Fri, 18 Feb 2005 21:55:29 -0800 (PST), Stephan Szabo wrote
> > On Fri, 18 Feb 2005, Keith Worthington wrote:
> >
> > > 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.
> > > Any suggestions on how to improve the performance would be
> > > greatly appreciated.
> >
> > Explain analyze output for the query with and without the clause
> > would probably be useful for analysis.
>
> Here is the explain analyze output with the WHERE
> clause commented out. This one actually ran slow. Usually it is only a few
> hundred ms without the WHERE clause.

That's probably just the instrumentation.

I'm not 100% sure why it's changing plans although I wonder if the costs
are just close enough that small changes are causing the plan change, but
I think it wouldn't pick a nested loop if it knew that it was grossly
underestimating the number of loops. It might be interesting to see how
the second query runs in explain analyze with enable_nestloop=off although
that'll likely make lower portions of the query more expensive.

One thing that jumps out at me is scans like this:
-> Index Scan using idx_tbl_item_item_type,
idx_tbl_item_item_type on tbl_item items (cost=0.00..84.16 rows=1
width=24)
(actual time=0.093..5.702 rows=566 loops=1)
Index Cond: (((item_type)::text =
'DIR'::text) OR ((item_type)::text = 'NET'::text))
Filter: ((NOT inactive) AND (item_class = 1))

This misestimation may be playing a part in why it thinks a nested loop is
a good plan. Is there a strong correlation between some item_types and
item_class or inactive? What does explain analyze on the following
queries show?

select * from tbl_item where (item_type='DIR OR item_type='NET');
select * from tbl_item where (item_type='DIR OR item_type='NET') AND NOT
inactive;
select * from tbl_item where (item_type='DIR OR item_type='NET') AND
item_class=1;
select * from tbl_item where (item_type='DIR OR item_type='NET') AND NOT
inactive AND item_class=1;

I'm not sure if you'd get any win from a partial index with WHERE NOT
inactive (or possibly both NOT inactive AND item_class=1 if you're almost
always limiting item_class to 1), but that might also be something to
check.


---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly