Hello,
i have a statement which ends with
ERROR: out of memory
DETAIL: Failed on request of size 639.
i tried it several times, the number after size changes but not the outcome.
could someone tell me what information you need to tell me whats wrong?
what i have so fare is:
PostgreSQL version: 8.1.4
Linux version 2.6.16.20-0.12-bigsmp ([EMAIL PROTECTED]) (gcc version
4.1.0 (SUSE Linux)) #1 SMP Mon Jun 19 22:25:45 UTC 2006
the statement:
SELECT catalog_prefix, supplier_aid
, stage_1.article_data_md5(
COALESCE(src.description_short ,'')
,
COALESCE(src.description_long ,'')
, COALESCE(src.ean
,'')
,
COALESCE(m2.manufacturer_name , src.manufacturer_name)
,
COALESCE(m1.manufacturer_name_old, src.manufacturer_name, '')
,
COALESCE(src.manufacturer_aid ,'')
) AS md5
, COALESCE(m2.manufacturer_name, src.manufacturer_name)
AS manufacturer_name
, COALESCE(m1.manufacturer_name_old,
src.manufacturer_name) AS manufacturer_name_old
, description_short, description_long, ean, manufacturer_aid
FROM quellen.article
src
LEFT JOIN ( SELECT SUBSTRING(file FROM
'^([a-zA-Z0-9]*)[.].*') AS catalog_prefix
, sku AS supplier_aid
, manufacturer_name_old
FROM quellen.manufacturer_names_old
) m1 USING (catalog_prefix, supplier_aid)
LEFT JOIN data.manufacturer_mapping
m2 ON (
UPPER(m2.manufacturer_name_old)=UPPER(COALESCE(m1.manufacturer_name_old,src.manufacturer_name)))
WHERE EXISTS ( SELECT 1
FROM stage_1.article_category_a
WHERE isactive IS TRUE
AND root_code=(SELECT
col_root_category_id FROM quellen.cataloginfo WHERE catalog_prefix =
'root')
AND catalog_prefix=src.catalog_prefix
AND supplier_aid=src.supplier_aid
)
and the explain plan:
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------
Merge Right Join (cost=17894565.16..18024911.70 rows=5792735 width=496)
Merge Cond: ("outer"."?column3?" = "inner"."?column9?")
-> Sort (cost=62.33..64.83 rows=1000 width=64)
Sort Key: upper(version.manufacturer_name_old)
-> Function Scan on dblink version (cost=0.00..12.50
rows=1000 width=64)
-> Sort (cost=17894502.83..17897399.20 rows=1158547 width=464)
Sort Key:
upper(COALESCE(manufacturer_names_old.manufacturer_name_old,
src.manufacturer_name))
-> Merge Left Join (cost=17752829.62..17777814.64
rows=1158547 width=464)
Merge Cond: (("outer".supplier_aid = "inner".sku) AND
("outer".catalog_prefix = "inner"."?column4?"))
-> Sort (cost=17587009.86..17589906.23 rows=1158547 width=448)
Sort Key: src.supplier_aid, src.catalog_prefix
-> Seq Scan on article src
(cost=0.00..17470321.68 rows=1158547 width=448)
Filter: (subplan)
SubPlan
-> Index Scan using
article_category_a_pkey on article_category_a (cost=3.44..7.46 rows=1
width=0)
Index Cond: ((catalog_prefix = $1)
AND (supplier_aid = $2) AND (root_code = $0))
Filter: (isactive IS TRUE)
InitPlan
-> Index Scan using
cataloginfo_pkey on cataloginfo (cost=0.00..3.44 rows=1 width=5)
Index Cond: (catalog_prefix
= 'root'::text)
-> Sort (cost=165819.76..169160.88 rows=1336447 width=63)
Sort Key: manufacturer_names_old.sku,
"substring"(manufacturer_names_old.file, '^([a-zA-Z0-9]*)[.].*'::text)
-> Seq Scan on manufacturer_names_old
(cost=0.00..29836.47 rows=1336447 width=63)
greetings, anita
---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster
