Thread
-
[Keystone Slip # 5] Query consumes all RAM on machine (OR + LIKE problem)
webmaster@postgresql.org — 1999-07-23T16:50:40Z
--------------------------------------------------------------------------- Slip number -----: 5 Problem ---------: Query consumes all RAM on machine (OR + LIKE problem) Opened by -------: mascarim@yahoo.com on 07/23/99 11:48 Assigned To -----: momjian --------------------------------------------------------------------------- Summary: PostgreSQL 6.5.0 on i686-pc-linux-gnu, compiled by gcc 2.7.2.3 The following simple, 5-way join consumes all RAM on the machine as it processess the query, taking minutes to execute until all RAM is consumed: SELECT DISTINCT supplies.supply,supplies.supplyunit, supplies.purchaseunit,supplies.vendor, supplies.vendorgroup,supplies.vendoritem, supplies.vendorname,supplies.description, supplies.conversion,supplies.price, supplies.inventory,supplies.commodity, supplies.adddate FROM supplies,permitbuy,locations,supplychains,reserves WHERE permitbuy.webuser = 'mascarj' AND (locations.company,locations.costcntr) = (permitbuy.company, permitbuy.costcntr) AND supplychains.target = locations.target AND reserves.target = supplychains.supplysource AND supplies.supply = reserves.supply AND supplies.inventory = '1' AND ((upper(supplies.supply) LIKE '%SEQ%') OR (upper(supplies.vendoritem) LIKE '%SEQ%') OR (upper(supplies.vendorname) LIKE '%SEQ%') OR (upper(supplies.description) LIKE '%SEQ%')) OR ((upper(supplies.supply) LIKE '%SCD%') OR (upper(supplies.vendoritem) LIKE '%SCD%') OR (upper(supplies.vendorname) LIKE '%SCD%') OR (upper(supplies.description) LIKE '%SCD%')) AND ((upper(supplies.supply) LIKE '%SLEE%') OR (upper(supplies.vendoritem) LIKE '%SLEE%') OR (upper(supplies.vendorname) LIKE '%SLEE%') OR (upper(supplies.description) LIKE '%SLEE%')) ORDER BY supplies.description Here's the plan. Obviously something is seriously wrong as NO indexes are used: NOTICE: QUERY PLAN: Unique (cost=61491617792.00 rows=1073741849 width=232) -> Sort (cost=61491617792.00 rows=1073741849 width=232) -> Nested Loop (cost=61491617792.00 rows=1073741849 width=232) -> Nested Loop (cost=50662932480.00 rows=1073741850 width=204) -> Nested Loop (cost=26479044608.00 rows=1073741850 width=168) -> Nested Loop (cost=27997336.00 rows=536717461 width=160) -> Seq Scan on supplies (cost=1675.03 rows=29832 width=144) -> Seq Scan on reserves (cost=938.44 rows=20468 width=16) -> Seq Scan on supplychains (cost=49.28 rows=1251 width=8) -> Seq Scan on permitbuy (cost=22.52 rows=531 width=36) -> Seq Scan on locations (cost=10.09 rows=245 width=28) EXPLAIN Table Rows -------------------------- supplies 29926 permitbuy 531 locations 245 supplychains 1251 reserves 20476 Index Columns -------------------------- k_supplies1 supply k_permitbuy1 webuser,company,costcntr k_locations1 target k_locations2 company, costcntr k_supplychains1 target,supplysource,priority k_supplychains2 target,supplysource k_supplychains3 target,priority k_reserves1 target,supply k_reserves2 supply Any help would be appreciated. I would give you a complete description of the tables and indices except that there is the 8K limit on this form! ;-) --------------------------------------------------------------------------- History: 07/23/99 12:50 by scrappy: Dependency changed to 0 from Current tech changed to momjian from Contact changed to from ANONYMOUS Scheduled Close Date changed to 0 from Scheduled Open Date changed to 0 from Public setting changed to 1 from 0 Policy changed to 0 --------------------------------------------------------------------------- Full information on this slip is available at: http://www.postgresql.org/bugs/visitor.php3?sid=5&v_func=zoom --------------------------------------------------------------------------- This message was generated automatically by Keystone at http://www.postgresql.org