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
