Out of memory error in 8.1.0 Win32

From: "Relyea, Mike" <Mike(dot)Relyea(at)xerox(dot)com>
To: "Postgres General" <pgsql-general(at)postgresql(dot)org>
Subject: Out of memory error in 8.1.0 Win32
Date: 2006-06-14 17:59:56
Message-ID: 1806D1F73FCB7F439F2C842EE0627B180415C060@usa0300ms01.na.xerox.net
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

I've just started receiving an out of memory error with my most complex
query. It has been running fine for the past 9 months. It's a snapshot
materialized view that I update every night using the functions from
http://jonathangardner.net/PostgreSQL/materialized_views/matviews.html

The error I'm receiving when I try to execute SELECT
refresh_matview('"tblSummary"'); is
ERROR: out of memory
DETAIL: Failed on request of size 344.
CONTEXT: SQL statement "INSERT INTO "tblSummary" SELECT * FROM
"qrySummary""
PL/pgSQL function "refresh_matview" line 13 at execute statement

I'd post the results from EXPLAIN INSERT INTO "tblSummary" SELECT * FROM
"qrySummary"; but it's just over 700 lines

qrySummary is a view based on a number of other views and a few tables.
It is defined as
========================================================================
=
SELECT "qryPQSetPages"."MachineID", "qryPQSetPages"."PrintCopyID",
"tblColors"."Color", "tblSuppliers"."Supplier", "tblZones"."Zone",
"tblPrinters"."Name" AS "Printer", "qryYield"."Yield",
"qryPrintCopyComments"."PrintCopyComments",
avg("qryPhotopicDensity"."PhotopicDensity") AS "AvgPhotopicDensity",
avg("qryCRMS"."CRMS_Value") AS "AvgCRMS", avg("qryLStar"."AvgOfLstar")
AS "AvgLstar", avg("qryAStar"."AvgOfAstar") AS "AvgAstar",
avg("qryBStar"."AvgOfBstar") AS "AvgBstar", avg("qryABRatio"."ABRatio")
AS "AvgABRatio", max("qryGhosting"."MaxGhosting") AS "MaxGhost",
max("qryBackground"."Background_DeltaE") AS "MaxBkgdDeltaE",
avg("qryMottle_NMF"."Mottle_NMF") AS "AvgMottle_NMF",
max("qryVBS_Horizontal"."VBS_Horizontal") AS "MaxVBS_H",
max("qryVBS_Vertical"."VBS_Vertical") AS "MaxVBS_V",
max("qryReload"."DeltaE") AS "MaxReloadDeltaE"
FROM "tblColors"
JOIN ("qryPQSetPages"
LEFT JOIN "qryCRMS" ON "qryPQSetPages"."ColorID" =
"qryCRMS"."ColorID" AND "qryPQSetPages"."PrintCopyID" =
"qryCRMS"."PrintCopyID" AND "qryPQSetPages"."MachineID" =
"qryCRMS"."MachineID"
LEFT JOIN "qryGhosting" ON "qryPQSetPages"."ColorID" =
"qryGhosting"."ColorID" AND "qryPQSetPages"."PrintCopyID" =
"qryGhosting"."PrintCopyID" AND "qryPQSetPages"."MachineID" =
"qryGhosting"."MachineID"
LEFT JOIN "qryVBS_Horizontal" ON "qryPQSetPages"."ColorID" =
"qryVBS_Horizontal"."ColorID" AND "qryPQSetPages"."PrintCopyID" =
"qryVBS_Horizontal"."PrintCopyID" AND "qryPQSetPages"."MachineID" =
"qryVBS_Horizontal"."MachineID"
LEFT JOIN "qryBStar" ON "qryPQSetPages"."ColorID" =
"qryBStar"."ColorID" AND "qryPQSetPages"."PrintCopyID" =
"qryBStar"."PrintCopyID" AND "qryPQSetPages"."MachineID" =
"qryBStar"."MachineID"
LEFT JOIN "qryBackground" ON "qryPQSetPages"."ColorID" =
"qryBackground"."ColorID" AND "qryPQSetPages"."PrintCopyID" =
"qryBackground"."PrintCopyID" AND "qryPQSetPages"."MachineID" =
"qryBackground"."MachineID"
LEFT JOIN "qryReload" ON "qryPQSetPages"."ColorID" =
"qryReload"."ColorID" AND "qryPQSetPages"."PrintCopyID" =
"qryReload"."PrintCopyID" AND "qryPQSetPages"."MachineID" =
"qryReload"."MachineID"
LEFT JOIN "qryMottle_NMF" ON "qryPQSetPages"."ColorID" =
"qryMottle_NMF"."ColorID" AND "qryPQSetPages"."PrintCopyID" =
"qryMottle_NMF"."PrintCopyID" AND "qryPQSetPages"."MachineID" =
"qryMottle_NMF"."MachineID"
LEFT JOIN "qryAStar" ON "qryPQSetPages"."ColorID" =
"qryAStar"."ColorID" AND "qryPQSetPages"."PrintCopyID" =
"qryAStar"."PrintCopyID" AND "qryPQSetPages"."MachineID" =
"qryAStar"."MachineID"
LEFT JOIN "qryABRatio" ON "qryPQSetPages"."ColorID" =
"qryABRatio"."ColorID" AND "qryPQSetPages"."PrintCopyID" =
"qryABRatio"."PrintCopyID" AND "qryPQSetPages"."MachineID" =
"qryABRatio"."MachineID"
LEFT JOIN "qryLStar" ON "qryPQSetPages"."ColorID" =
"qryLStar"."ColorID" AND "qryPQSetPages"."PrintCopyID" =
"qryLStar"."PrintCopyID" AND "qryPQSetPages"."MachineID" =
"qryLStar"."MachineID"
LEFT JOIN "qryYield" ON "qryPQSetPages"."ColorID" =
"qryYield"."ColorID" AND "qryPQSetPages"."MachineID" =
"qryYield"."MachineID"
LEFT JOIN "qryPrintCopyComments" ON "qryPQSetPages"."ColorID" =
"qryPrintCopyComments"."ColorID" AND "qryPQSetPages"."PrintCopyID" =
"qryPrintCopyComments"."PrintCopyID" AND "qryPQSetPages"."MachineID" =
"qryPrintCopyComments"."MachineID"
LEFT JOIN "qryVBS_Vertical" ON "qryPQSetPages"."ColorID" =
"qryVBS_Vertical"."ColorID" AND "qryPQSetPages"."PrintCopyID" =
"qryVBS_Vertical"."PrintCopyID" AND "qryPQSetPages"."MachineID" =
"qryVBS_Vertical"."MachineID"
LEFT JOIN "qryPhotopicDensity" ON "qryPQSetPages"."ColorID" =
"qryPhotopicDensity"."ColorID" AND "qryPQSetPages"."PrintCopyID" =
"qryPhotopicDensity"."PrintCopyID" AND "qryPQSetPages"."MachineID" =
"qryPhotopicDensity"."MachineID") ON "tblColors"."ColorID" =
"qryPQSetPages"."ColorID"
LEFT JOIN "tblZones" ON "qryYield"."ZoneID" = "tblZones"."ZoneID"
LEFT JOIN "tblPrinters" ON "qryYield"."PrinterID" =
"tblPrinters"."PrinterID"
LEFT JOIN "tblSuppliers" ON "qryYield"."SupplierID" =
"tblSuppliers"."SupplierID"
GROUP BY "qryPQSetPages"."MachineID", "qryPQSetPages"."PrintCopyID",
"tblColors"."Color", "tblSuppliers"."Supplier", "tblZones"."Zone",
"tblPrinters"."Name", "qryYield"."Yield",
"qryPrintCopyComments"."PrintCopyComments"
ORDER BY "qryPQSetPages"."MachineID", "qryPQSetPages"."PrintCopyID",
"tblColors"."Color";
========================================================================
=

For the sake of brevity, I'll only list one of the other views to give
you a rough idea of what they look like. Most of the other views look
similar to the one in the first left join, qryCRMS
========================================================================
=
SELECT "PrintSamples"."MachineID", "PrintSamples"."PrintCopyID",
"tblColors"."ColorID", "ParameterValues"."ParameterValue" AS
"CRMS_Value"
FROM "AnalysisModules"
JOIN ("tblColors"
JOIN ("tblTPNamesAndColors"
JOIN "PrintSamples" ON "tblTPNamesAndColors"."TestPatternName"::text
= "PrintSamples"."TestPatternName"::text) ON "tblColors"."ColorID" =
"tblTPNamesAndColors"."ColorID"
JOIN ("DigitalImages"
JOIN "PrintSampleAnalyses" ON "DigitalImages"."ImageID" =
"PrintSampleAnalyses"."ImageID"
JOIN ("ParameterNames"
JOIN ("Measurements"
JOIN "ParameterValues" ON "Measurements"."MeasurementID" =
"ParameterValues"."MeasurementID") ON "ParameterNames"."ParameterID" =
"ParameterValues"."ParameterID") ON "PrintSampleAnalyses"."psaID" =
"Measurements"."psaID") ON "PrintSamples"."PrintSampleID" =
"DigitalImages"."PrintSampleID") ON "AnalysisModules"."MetricID" =
"Measurements"."MetricID"
WHERE "tblTPNamesAndColors"."ColorID" = 3 AND
"tblTPNamesAndColors"."TestPatternName"::text ~~ '%TP8%'::text AND
("Measurements"."msmTag"::text = 'DAC'::text OR
"Measurements"."msmTag"::text = '<tag>'::text) AND
"AnalysisModules"."AnalysisModuleName"::text = 'MacroUniformity'::text
AND "ParameterNames"."ParameterName"::text = 'CRMS'::text;
========================================================================
=

I'm running this on XP Pro with a P4 3.2ghz, 1.5G memory and a single
SATA Raptor.

My conf settings are:
shared_buffers = 12288
work_mem = 262144
maintenance_work_mem = 131072
effective_cache_size = 10000
random_page_cost = 2.0

Postgres isn't the only app running on this computer that requires
significant resources. I've got another proprietary app on there that
is very CPU and memory intensive. This other app should be allocated
resources before postgres.

Autovacuum is running with the default settings, and I also do a vacuum
full analyze nightly (probably overkill for me, but the server doesn't
see any other activity at night anyway)

How should I resolve this problem? Rewrite qrySummary to be more
efficient (not a job I look forward to)? Get a better server/upgrade
the memory in the one I have? Adjust some settings in my conf file?
Some other solution I haven't thought of? I'm looking for a point in
the right direction.

Mike

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Milen Kulev 2006-06-14 19:44:40 Re: Partitioning... -> solved
Previous Message Phil Thornhill 2006-06-14 17:05:04 Initdb logging on xp