I have a query that is aborting because of out of memory, and am
wondering what I can do to prevent this from *ever* happening (shouldn't
it be able to use disk)?

=# explain INSERT INTO term_doc_rel3 (term, docid)
-# select w.termid, t.docid from
-# (select (stat('select idxfti from sampledocs3 d2 where d2.docid=' ||
d.docid || ';')).word as term,
(# d.docid as docid from sampledocs3 d offset 200000) as t join
word_ids as w on w.term = t.term;
QUERY PLAN
------------------------------------------------------------------------------------------------------
Merge Join (cost=27163.12..150051.70 rows=55635 width=8)
Merge Cond: ("outer".term = "inner".term)
-> Index Scan using word_ids_term_key on word_ids w
(cost=0.00..109952.57 rows=4840597 width=18)
-> Sort (cost=27163.12..27302.21 rows=55635 width=36)
Sort Key: t.term
-> Subquery Scan t (cost=17385.49..22778.05 rows=55635 width=36)
-> Limit (cost=17385.49..22221.70 rows=55635 width=4)
-> Seq Scan on sampledocs3 d (cost=0.00..22221.70
rows=255635 width=4)
(8 rows)

=# explain analyze INSERT INTO term_doc_rel3 (term, docid)
-# select w.termid, t.docid from
-# (select (stat('select idxfti from sampledocs3 d2 where d2.docid=' ||
d.docid || ';')).word as term,
(# d.docid as docid from sampledocs3 d offset 200000) as t join
word_ids as w on w.term = t.term;

ERROR: out of memory
DETAIL: Failed on request of size 32.

Memory stats from log:

TopMemoryContext: 40960 total in 4 blocks; 12736 free (23 chunks); 28224
used
CFuncHash: 8192 total in 1 blocks; 4936 free (0 chunks); 3256 used
Type information cache: 8192 total in 1 blocks; 1864 free (0 chunks);
6328 used
Operator class cache: 8192 total in 1 blocks; 4936 free (0 chunks); 3256
used
TopTransactionContext: 1548738560 total in 197 blocks; 6008 free (189
chunks); 1548732552 used
MessageContext: 57344 total in 3 blocks; 25432 free (3 chunks); 31912 used
smgr relation table: 8192 total in 1 blocks; 2872 free (0 chunks); 5320 used
Portal hash: 8192 total in 1 blocks; 3912 free (0 chunks); 4280 used
PortalMemory: 8192 total in 1 blocks; 8040 free (1 chunks); 152 used
PortalHeapMemory: 1024 total in 1 blocks; 896 free (0 chunks); 128 used
ExecutorState: 1325826956 total in 175 blocks; 1191164528 free (11490374
chunks); 134662428 used
ExecutorState: 318758912 total in 47 blocks; 5853360 free (47 chunks);
312905552 used
ExprContext: 8192 total in 1 blocks; 8176 free (0 chunks); 16 used
ExprContext: 0 total in 0 blocks; 0 free (0 chunks); 0 used
ExprContext: 0 total in 0 blocks; 0 free (0 chunks); 0 used
ExprContext: 0 total in 0 blocks; 0 free (0 chunks); 0 used
ExprContext: 0 total in 0 blocks; 0 free (0 chunks); 0 used
ExprContext: 0 total in 0 blocks; 0 free (0 chunks); 0 used
ExprContext: 0 total in 0 blocks; 0 free (0 chunks); 0 used
Relcache by OID: 8192 total in 1 blocks; 3376 free (0 chunks); 4816 used
CacheMemoryContext: 516096 total in 6 blocks; 182448 free (1 chunks);
333648 used
pg_toast_1917987_index: 1024 total in 1 blocks; 328 free (0 chunks); 696
used
sampledocs3_docid_idx: 1024 total in 1 blocks; 392 free (0 chunks); 632 used
sampledocs3_uri_idx: 1024 total in 1 blocks; 392 free (0 chunks); 632 used
word_ids_term_termid_idx: 1024 total in 1 blocks; 328 free (0 chunks);
696 used
word_ids_termid_idx: 1024 total in 1 blocks; 392 free (0 chunks); 632 used
word_ids_term_key: 1024 total in 1 blocks; 392 free (0 chunks); 632 used
pg_index_indrelid_index: 1024 total in 1 blocks; 392 free (0 chunks);
632 used
pg_attrdef_adrelid_adnum_index: 1024 total in 1 blocks; 328 free (0
chunks); 696 used
pg_type_typname_nsp_index: 1024 total in 1 blocks; 328 free (0 chunks);
696 used
pg_type_oid_index: 1024 total in 1 blocks; 392 free (0 chunks); 632 used
pg_trigger_tgrelid_tgname_index: 1024 total in 1 blocks; 328 free (0
chunks); 696 used
pg_statistic_relid_att_index: 1024 total in 1 blocks; 328 free (0
chunks); 696 used
pg_auth_members_member_role_index: 1024 total in 1 blocks; 328 free (0
chunks); 696 used
pg_auth_members_role_member_index: 1024 total in 1 blocks; 328 free (0
chunks); 696 used
pg_rewrite_rel_rulename_index: 1024 total in 1 blocks; 328 free (0
chunks); 696 used
pg_proc_proname_args_nsp_index: 1024 total in 1 blocks; 256 free (0
chunks); 768 used
pg_proc_oid_index: 1024 total in 1 blocks; 392 free (0 chunks); 632 used
pg_operator_oprname_l_r_n_index: 1024 total in 1 blocks; 192 free (0
chunks); 832 used
pg_operator_oid_index: 1024 total in 1 blocks; 392 free (0 chunks); 632 used
pg_opclass_oid_index: 1024 total in 1 blocks; 392 free (0 chunks); 632 used
pg_opclass_am_name_nsp_index: 1024 total in 1 blocks; 256 free (0
chunks); 768 used
pg_namespace_oid_index: 1024 total in 1 blocks; 392 free (0 chunks); 632
used
pg_namespace_nspname_index: 1024 total in 1 blocks; 392 free (0 chunks);
632 used
pg_language_oid_index: 1024 total in 1 blocks; 392 free (0 chunks); 632 used
pg_language_name_index: 1024 total in 1 blocks; 392 free (0 chunks); 632
used
pg_inherits_relid_seqno_index: 1024 total in 1 blocks; 328 free (0
chunks); 696 used
pg_index_indexrelid_index: 1024 total in 1 blocks; 392 free (0 chunks);
632 used
pg_authid_oid_index: 1024 total in 1 blocks; 392 free (0 chunks); 632 used
pg_authid_rolname_index: 1024 total in 1 blocks; 392 free (0 chunks);
632 used
pg_database_datname_index: 1024 total in 1 blocks; 392 free (0 chunks);
632 used
pg_conversion_oid_index: 1024 total in 1 blocks; 392 free (0 chunks);
632 used
pg_conversion_name_nsp_index: 1024 total in 1 blocks; 328 free (0
chunks); 696 used
pg_conversion_default_index: 1024 total in 1 blocks; 192 free (0
chunks); 832 used
pg_class_relname_nsp_index: 1024 total in 1 blocks; 328 free (0 chunks);
696 used
pg_class_oid_index: 1024 total in 1 blocks; 392 free (0 chunks); 632 used
pg_cast_source_target_index: 1024 total in 1 blocks; 328 free (0
chunks); 696 used
pg_attribute_relid_attnum_index: 1024 total in 1 blocks; 328 free (0
chunks); 696 used
pg_attribute_relid_attnam_index: 1024 total in 1 blocks; 328 free (0
chunks); 696 used
pg_amproc_opc_proc_index: 1024 total in 1 blocks; 256 free (0 chunks);
768 used
pg_amop_opr_opc_index: 1024 total in 1 blocks; 328 free (0 chunks); 696 used
pg_amop_opc_strat_index: 1024 total in 1 blocks; 256 free (0 chunks);
768 used
pg_aggregate_fnoid_index: 1024 total in 1 blocks; 392 free (0 chunks);
632 used
MdSmgr: 8192 total in 1 blocks; 6976 free (0 chunks); 1216 used
LockTable (locallock hash): 8192 total in 1 blocks; 3912 free (0
chunks); 4280 used
Timezones: 47592 total in 2 blocks; 5968 free (0 chunks); 41624 used
ErrorContext: 8192 total in 1 blocks; 8176 free (0 chunks); 16 used
ERROR: out of memory
DETAIL: Failed on request of size 32.


This happens about 20+ minutes into the query. This query will generate
approximately 70 million rows (rather than the 250k that the optimizer
expects), as stat() is a set-valued function from tsearch2 (using it to
get a list of words from tsvector; while that's not quite what it was
intended to do, and if this protyping works I can write a specialized
function to just extract the words, but it doesn't seem to me it should
crash?)

My primary concern is not to make the query work; I can turn off merge
joins, or process it in smaller batches so that the cost optimizer
understands that nested loops should be used (for this data they are
much faster and also don't crash for large datasets).

Mostly I just want to figure out why postgresql is ever returning
out-of-memory rather than using the disk as a backing store. Is my
database misconfigured, or is this a bug? I would think that postgresql
would be able to handle large datasets that exceed work_mem?

Thanks,
Joe Malicki


---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo (AT) postgresql (DOT) org so that your
message can get through to the mailing list cleanly

Joe Malicki <joe.malicki (AT) metacarta (DOT) com> writes:
It looks like you've got a couple of different problems:

Does the INSERT's target table have foreign key constraints? The
after-trigger event list would explain bloat in TopTransactionContext.
There is not much you can do about that except to insert fewer rows
per statement --- changing plans won't help.

This is curious. I'm inclined to suspect a memory leak in the tsearch2
functions you're using. Can you provide a self-contained test case?

I believe this is probably memory used by the sort step, so it ought to
be more or less bounded by your work_mem setting. What have you got
that set to?

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org