Skip site navigation (1) Skip section navigation (2)

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

From: webmaster(at)postgreSQL(dot)org
To: pgsql-bugs(at)postgreSQL(dot)org
Subject: [Keystone Slip # 5] Query consumes all RAM on machine (OR + LIKE problem)
Date: 1999-07-23 16:50:40
Message-ID: 199907231650.MAA15663@hub.org (view raw or flat)
Thread:
Lists: pgsql-bugs
---------------------------------------------------------------------------
Slip number -----: 5
Problem ---------: Query consumes all RAM on machine (OR + LIKE problem)
Opened by -------: mascarim(at)yahoo(dot)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


pgsql-bugs by date

Next:From: webmasterDate: 1999-07-23 16:52:24
Subject: [Keystone Slip # 4] ECPG declare cursor parses, but no data struct in Postgres.
Previous:From: webmasterDate: 1999-07-23 16:43:17
Subject: [Keystone Slip # 3]

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group