Обсуждение: Out of memory error in 8.1.0 Win32
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
""Relyea, Mike"" <Mike.Relyea@xerox.com> wrote > I've just started receiving an out of memory error with my most complex > query. Can you post the memory usage log after the error the server reports? Regards, Qingqing
Is this what you're looking for? If you need something different, just specify. Remember this is WinXP. This is all of the memory output from perfmon measured every 15 seconds. The query runs for about 20-30 minutes before the error message comes up. This shows the entire time it took to run the query and one measurement after the error came up. -----Original Message----- From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-owner@postgresql.org] On Behalf Of Qingqing Zhou Sent: Wednesday, June 14, 2006 11:13 PM To: pgsql-general@postgresql.org Subject: Re: [GENERAL] Out of memory error in 8.1.0 Win32 ""Relyea, Mike"" <Mike.Relyea@xerox.com> wrote > I've just started receiving an out of memory error with my most complex > query. Can you post the memory usage log after the error the server reports? Regards, Qingqing ---------------------------(end of broadcast)--------------------------- TIP 6: explain analyze is your friend
Вложения
""Relyea, Mike"" <Mike.Relyea@xerox.com> wrote > Is this what you're looking for? No. I mean per-context memory usage output like this in your log file: 2006-06-08 16:33:09 LOG: autovacuum: processing database "ibox" TopMemoryContext: 84400 total in 7 blocks; 12696 free (22 chunks); 71704 used Operator class cache: 8192 total in 1 blocks; 4936 free (0 chunks); 3256 used TopTransactionContext: 8192 total in 1 blocks; 7552 free (10 chunks); 640 used MessageContext: 8192 total in 1 blocks; 7368 free (1 chunks); 824 used smgr relation table: 8192 total in 1 blocks; 3904 free (0 chunks); 4288 used ... Regards, Qingqing
"Qingqing Zhou" <zhouqq@cs.toronto.edu> wrote > > > ExecutorState: 550339936 total in 123 blocks; 195005920 free (740144 chunks); 355334016 used > ... > HashBatchContext: 293593176 total in 44 blocks; 3107384 free (80 chunks); 290485792 used > TIDBitmap: 2088960 total in 8 blocks; 1012120 free (27 chunks); 1076840 used > ... > HashBatchContext: 2080768 total in 7 blocks; 346888 free (11 chunks); 1733880 used > HashTableContext: 0 total in 0 blocks; 0 free (0 chunks); 0 used > HashBatchContext: 2080768 total in 7 blocks; 325264 free (11 chunks); 1755504 used > HashTableContext: 0 total in 0 blocks; 0 free (0 chunks); 0 used > HashBatchContext: 41877664 total in 11 blocks; 4845648 free (15 chunks); 37032016 used > TIDBitmap: 2088960 total in 8 blocks; 1012120 free (27 chunks); 1076840 used > > [repeated sequenced like this ...] > > 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 > LOG: autovacuum: processing database "iqdb" > LOG: autovacuum: processing database "template1"LOG: autovacuum: processing database "postgres" Er, looks like a huge hash-join but not sure if it is a memory leak, Tom? Regards, Qingqing
"Qingqing Zhou" <zhouqq@cs.toronto.edu> writes:
>> ExecutorState: 550339936 total in 123 blocks; 195005920 free (740144
>> chunks); 355334016 used
>> ...
>> HashBatchContext: 293593176 total in 44 blocks; 3107384 free (80 chunks);
>> 290485792 used
> Er, looks like a huge hash-join but not sure if it is a memory leak, Tom?
A hash join could suck a lot of memory in HashBatchContext, but what
seems wrong about your report is all that memory in ExecutorState.
Please see if you can track where that went.
BTW, what was work_mem set to in this example? In theory the
HashBatchContext shouldn't get much bigger than work_mem.
regards, tom lane
work_mem = 262144
I'm still trying to figure out how to track the memory usage in
ExecutorState
-----Original Message-----
From: pgsql-general-owner@postgresql.org
[mailto:pgsql-general-owner@postgresql.org] On Behalf Of Tom Lane
Sent: Sunday, June 18, 2006 11:13 PM
To: Qingqing Zhou
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Out of memory error in 8.1.0 Win32
"Qingqing Zhou" <zhouqq@cs.toronto.edu> writes:
>> ExecutorState: 550339936 total in 123 blocks; 195005920 free (740144
>> chunks); 355334016 used
>> ...
>> HashBatchContext: 293593176 total in 44 blocks; 3107384 free (80
chunks);
>> 290485792 used
> Er, looks like a huge hash-join but not sure if it is a memory leak,
Tom?
A hash join could suck a lot of memory in HashBatchContext, but what
seems wrong about your report is all that memory in ExecutorState.
Please see if you can track where that went.
BTW, what was work_mem set to in this example? In theory the
HashBatchContext shouldn't get much bigger than work_mem.
regards, tom lane
---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
On Wed, 21 Jun 2006, Relyea, Mike wrote: > ExecutorState: 550339936 total in 123 blocks; 195003544 free (740135 > chunks); 355336392 used > HashBatchContext: 293593176 total in 44 blocks; 3107384 free (80 > chunks); 290485792 used > TIDBitmap: 2088960 total in 8 blocks; 924720 free (27 chunks); 1164240 > used > HashBatchContext: 41877664 total in 11 blocks; 4845648 free (15 chunks); > 37032016 used > TIDBitmap: 2088960 total in 8 blocks; 1012120 free (27 chunks); 1076840 > used The same problem. ExecutorState uses much more memory than we expect -- but not sure where they are from :-( Regards, Qingqing
So what's my next step? How do I track down what is causing this problem? -----Original Message----- From: Qingqing Zhou [mailto:zhouqq@cs.toronto.edu] Sent: Wednesday, June 21, 2006 11:01 PM To: Relyea, Mike Cc: pgsql-general@postgresql.org; Tom Lane Subject: RE: [GENERAL] Out of memory error in 8.1.0 Win32 On Wed, 21 Jun 2006, Relyea, Mike wrote: > ExecutorState: 550339936 total in 123 blocks; 195003544 free (740135 > chunks); 355336392 used > HashBatchContext: 293593176 total in 44 blocks; 3107384 free (80 > chunks); 290485792 used > TIDBitmap: 2088960 total in 8 blocks; 924720 free (27 chunks); 1164240 > used > HashBatchContext: 41877664 total in 11 blocks; 4845648 free (15 chunks); > 37032016 used > TIDBitmap: 2088960 total in 8 blocks; 1012120 free (27 chunks); 1076840 > used The same problem. ExecutorState uses much more memory than we expect -- but not sure where they are from :-( Regards, Qingqing
Hi, I am consistently running into out-of-memory issues in 8.1.4 running on RHEL3 and 8.0.5 on RHEL4. The logs show entries like this: AggContext: -2130714624 total in 271 blocks; 9688 free (269 chunks); -2130724312 used TupleHashTable: 893902872 total in 119 blocks; 1088688 free (449 chunks); 892814184 used which looks mighty suspicious to me. :-; I can provide a self-contained test case if anyone wants to look at it. -- todd Relyea, Mike wrote: > So what's my next step? How do I track down what is causing this > problem? > > -----Original Message----- > From: Qingqing Zhou [mailto:zhouqq@cs.toronto.edu] > Sent: Wednesday, June 21, 2006 11:01 PM > To: Relyea, Mike > Cc: pgsql-general@postgresql.org; Tom Lane > Subject: RE: [GENERAL] Out of memory error in 8.1.0 Win32 > > > > On Wed, 21 Jun 2006, Relyea, Mike wrote: > >> ExecutorState: 550339936 total in 123 blocks; 195003544 free (740135 >> chunks); 355336392 used >> HashBatchContext: 293593176 total in 44 blocks; 3107384 free (80 >> chunks); 290485792 used >> TIDBitmap: 2088960 total in 8 blocks; 924720 free (27 chunks); 1164240 >> used >> HashBatchContext: 41877664 total in 11 blocks; 4845648 free (15 > chunks); >> 37032016 used >> TIDBitmap: 2088960 total in 8 blocks; 1012120 free (27 chunks); > 1076840 >> used > > The same problem. ExecutorState uses much more memory than we expect -- > but not sure where they are from :-( > > Regards, > Qingqing > > ---------------------------(end of broadcast)--------------------------- > TIP 9: In versions below 8.0, the planner will ignore your desire to > choose an index scan if your joining column's datatypes do not > match >
"Todd A. Cook" <tcook@blackducksoftware.com> writes:
> I am consistently running into out-of-memory issues in 8.1.4 running on
> RHEL3 and 8.0.5 on RHEL4. The logs show entries like this:
> AggContext: -2130714624 total in 271 blocks; 9688 free (269 chunks); -2130724312 used
> TupleHashTable: 893902872 total in 119 blocks; 1088688 free (449 chunks); 892814184 used
Misestimated hash aggregation, perhaps? What is the query and what does
EXPLAIN show for it? What have you got work_mem set to?
regards, tom lane
Tom Lane wrote:
>
> Misestimated hash aggregation, perhaps? What is the query and what does
> EXPLAIN show for it? What have you got work_mem set to?
oom_test=> \d oom_tab
Table "public.oom_tab"
Column | Type | Modifiers
--------+---------+-----------
val | integer |
oom_test=> explain select val,count(*) from oom_tab group by val;
QUERY PLAN
-------------------------------------------------------------------------
HashAggregate (cost=1163446.13..1163448.63 rows=200 width=4)
-> Seq Scan on oom_tab (cost=0.00..867748.42 rows=59139542 width=4)
The row estimitate for oom_tab is close to the actual value. Most of
the values are unique, however, so the result should have around 59M
rows too.
I've tried it with work_mem set to 32M, 512M, 1G, and 2G. It fails in
all cases, but it hits the failure point quicker with work_mem=32M.
-- todd
"Todd A. Cook" <tcook@blackducksoftware.com> writes:
> oom_test=> explain select val,count(*) from oom_tab group by val;
> QUERY PLAN
> -------------------------------------------------------------------------
> HashAggregate (cost=1163446.13..1163448.63 rows=200 width=4)
> -> Seq Scan on oom_tab (cost=0.00..867748.42 rows=59139542 width=4)
> The row estimitate for oom_tab is close to the actual value. Most of
> the values are unique, however, so the result should have around 59M
> rows too.
Well, that's the problem right there :-(. Have you ANALYZEd this table?
I think 200 is the default estimate for number of groups in the absence
of any ANALYZE stats, but it should surely not be that far off if it's
got real stats to play with.
If you need to make the query not fail without stats, you could set
enable_hashagg false, but I wouldn't recommend that as a production
choice (unless you set it just for this one query).
regards, tom lane
"Todd A. Cook" <tcook@blackducksoftware.com> writes: "Todd A. Cook" <tcook@blackducksoftware.com> writes: > QUERY PLAN > ------------------------------------------------------------------------- > HashAggregate (cost=1163446.13..1163448.63 rows=200 width=4) > -> Seq Scan on oom_tab (cost=0.00..867748.42 rows=59139542 width=4) > > The row estimitate for oom_tab is close to the actual value. Most of > the values are unique, however, so the result should have around 59M > rows too. ouch. it's estimating 200 distinct values. The estimates for distinct values are known to be unreliable but they shouldn't be *that* bad. Do you have a few hundred extremely common values and then a few million other values? What does this say: select * from pg_statistic where starelid = (select oid from pg_class where relname = 'oom_tab') You may need to reanalyze and maybe increase the statistics target (possibly by a lot). It may be interesting to compare the results of the above query before and after analyzing too. -- greg
Tom Lane wrote: > "Todd A. Cook" <tcook@blackducksoftware.com> writes: >> oom_test=> explain select val,count(*) from oom_tab group by val; >> QUERY PLAN >> ------------------------------------------------------------------------- >> HashAggregate (cost=1163446.13..1163448.63 rows=200 width=4) >> -> Seq Scan on oom_tab (cost=0.00..867748.42 rows=59139542 width=4) > >> The row estimitate for oom_tab is close to the actual value. Most of >> the values are unique, however, so the result should have around 59M >> rows too. > > Well, that's the problem right there :-(. Have you ANALYZEd this table? My production table and query are more complex. In the original, the query above was in a sub-select; the work-around was to create a temp table with the sub-query results, analyze it, and then do the larger query based off of the temp table. There have been off and on discussions on the pg lists about out of memory issues (see http://archives.postgresql.org/pgsql-bugs/2006-03/msg00102.php). I was just offering my test case as an example in case it might be of any use in tracking those problems down. :) -- todd
I've zipped the results of EXPLAIN INSERT INTO "tblSummary" SELECT * FROM "qrySummary"; for my case. It's a zip file that I've renamed to .txt in order to get around the attachment being blocked by certain mail servers.
Вложения
"Todd A. Cook" <tcook@blackducksoftware.com> writes:
> Tom Lane wrote:
>> Well, that's the problem right there :-(. Have you ANALYZEd this table?
> My production table and query are more complex. In the original, the
> query above was in a sub-select; the work-around was to create a temp
> table with the sub-query results, analyze it, and then do the larger
> query based off of the temp table.
Hmm. One of the things that's on my TODO list is to make the planner
smarter about drilling down into sub-selects to extract statistics.
I think that's what's called for here, but your example has eliminated
all the interesting details. Can you show us the actual query, its
EXPLAIN plan, and the definitions of the tables/views involved?
regards, tom lane
"Relyea, Mike" <Mike.Relyea@xerox.com> writes:
> I've zipped the results of EXPLAIN INSERT INTO "tblSummary" SELECT *
> FROM "qrySummary"; for my case. It's a zip file that I've renamed to
> .txt in order to get around the attachment being blocked by certain mail
> servers.
Egad, what a mess :-(. By my count you have 89 hash joins, 24 sorts,
and 8 hash aggregations in there. In total these will feel authorized
to use 121 times work_mem. Since you've got work_mem set to 256 meg,
an out-of-memory condition doesn't seem that surprising. You need to
make work_mem drastically smaller for this query. Or else break it down
into multiple steps.
regards, tom lane
On Jun 22, 2006, at 2:23 PM, Tom Lane wrote: > "Relyea, Mike" <Mike.Relyea@xerox.com> writes: >> I've zipped the results of EXPLAIN INSERT INTO "tblSummary" SELECT * >> FROM "qrySummary"; for my case. It's a zip file that I've renamed to >> .txt in order to get around the attachment being blocked by >> certain mail >> servers. > > Egad, what a mess :-(. By my count you have 89 hash joins, 24 sorts, > and 8 hash aggregations in there. In total these will feel authorized > to use 121 times work_mem. Since you've got work_mem set to 256 meg, > an out-of-memory condition doesn't seem that surprising. You need to > make work_mem drastically smaller for this query. Or else break it > down > into multiple steps. Except won't the sorts pull in all data from their underlying node before proceeding, which should free the memory from those underlying nodes? If so, it looks like it's not nearly as bad, only taking about 20x work_mem (which of course still isn't great...) -- Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com Pervasive Software http://pervasive.com work: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461
Tom Lane wrote: > > Hmm. One of the things that's on my TODO list is to make the planner > smarter about drilling down into sub-selects to extract statistics. > I think that's what's called for here, but your example has eliminated > all the interesting details. Can you show us the actual query, its > EXPLAIN plan, and the definitions of the tables/views involved? I've already altered the queries that ran into this problem. I'll dig the old ones out of CVS and send them to you tomorrow. -- todd
Thanks Jim and Tom. At least now I've got a direction to head in. I think for now I'll probably reduce work_mem as a stop-gap measure to get the query running again. This will buy me some time to redesign it. I'll probably separate out each sub query and store the results in a table (would a temp table be a good solution here?) before I pull it all together with the final query. > Egad, what a mess :-(. By my count you have 89 hash joins, 24 sorts, > and 8 hash aggregations in there. In total these will feel authorized > to use 121 times work_mem. Since you've got work_mem set to 256 meg, > an out-of-memory condition doesn't seem that surprising. You need to > make work_mem drastically smaller for this query. Or else break it > down > into multiple steps. Except won't the sorts pull in all data from their underlying node before proceeding, which should free the memory from those underlying nodes? If so, it looks like it's not nearly as bad, only taking about 20x work_mem (which of course still isn't great...) -- Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com Pervasive Software http://pervasive.com work: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461
On Jun 22, 2006, at 4:02 PM, Relyea, Mike wrote: > Thanks Jim and Tom. At least now I've got a direction to head in. I > think for now I'll probably reduce work_mem as a stop-gap measure > to get > the query running again. This will buy me some time to redesign it. > I'll probably separate out each sub query and store the results in a > table (would a temp table be a good solution here?) before I pull > it all > together with the final query. Yes, it would. It's also possible that you could structure the query better, to reduce the amount of concurrent sorting/hashing going on. -- Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com Pervasive Software http://pervasive.com work: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461