[Keystone Slip # 5] Query consumes all RAM on machine (OR + LIKE problem)

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