Re: 0ut of Memory Error during Vacuum Analyze and - Mailing list pgsql-performance

No special data types. The table is pretty large one with over 15GB. The
index is about 1.5 GB. Here's the table structure :

     Column      |         Type          | Modifiers
-----------------+-----------------------+-----------
 county_id       | numeric(5,0)          | not null
 batch_dt        | numeric(8,0)          | not null
 batch_seq       | numeric(5,0)          | not null
 mtg_seq_nbr     | numeric(1,0)          | not null
 mtg_rec_dt      | numeric(8,0)          |
 mtg_doc_nbr     | character varying(12) |
 mtg_rec_bk      | character varying(6)  |
 mtg_rec_pg      | character varying(6)  |
 mtg_amt         | numeric(11,0)         |
 lndr_cd         | character varying(10) |
 lndr_nm         | character varying(30) |
 mtg_assm_ind    | character(1)          |
 mtg_typ         | character varying(5)  |
 adj_rate_ind    | character(1)          |
 mtg_term_nbr    | numeric(5,0)          |
 mtg_term_cd     | character varying(4)  |
 mtg_due_dt      | numeric(8,0)          |
 mtg_deed_typ    | character varying(6)  |
 reverse_mtg_ind | character(1)          |
 refi_ind        | character(1)          |
 conform_ind     | character(1)          |
 cnstr_ln_ind    | character(1)          |
 title_co_cd     | character varying(5)  |
 state_id        | numeric(5,0)          |
 msa             | numeric(4,0)          |
Indexes:
    "uq_mortgage" UNIQUE, btree (county_id, batch_dt, batch_seq,
mtg_seq_nbr)
    "mortgage_idxc_county_id_mtg_rec_dt" btree (county_id, mtg_rec_dt)
    "mortgage_idxc_state_id_mtg_rec_dt" btree (state_id, mtg_rec_dt)

---------

 Here's the test I did with maintenance_work_mem = 1GB:


mtrac=# show maintenance_work_mem ;
 maintenance_work_mem
----------------------
 1048576        <======
(1 row)

mtrac=#
mtrac=#
mtrac=# create index mort_ht on mortgage(county_id,mtg_rec_dt);
ERROR:  out of memory                                               <===
DETAIL:  Failed on request of size 134217728.               <===

............ Then I changed the parameter to 512 MB:


mtrac=# show maintenance_work_mem ;
 maintenance_work_mem
----------------------
 524288         <===
(1 row)

mtrac=#  create index mort_ht_512 on mortgage(county_id,mtg_rec_dt);
CREATE INDEX
-----------------------------------------------



Regards,
----

    Husam

-----Original Message-----
From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
Sent: Tuesday, February 14, 2006 2:16 PM
To: Tomeh, Husam
Cc: pgsql-performance@postgresql.org
Subject: Re: [PERFORM] 0ut of Memory Error during Vacuum Analyze and
Create Index

"Tomeh, Husam" <htomeh@firstam.com> writes:
> I have run pg_dump and had no errors. I also got this error when
> creating one index but not another. When I lowered my
> maintenance_work_mem, the create index succeeded.

Create index too?  Hm.  That begins to sound more like a memory leak.
Do you have any custom data types or anything like that in this
table?  Can you put together a self-contained test case using dummy
data?

            regards, tom lane
**********************************************************************
This message contains confidential information intended only for the use of the addressee(s) named above and may
containinformation that is legally privileged.  If you are not the addressee, or the person responsible for delivering
itto the addressee, you are hereby notified that reading, disseminating, distributing or copying this message is
strictlyprohibited.  If you have received this message by mistake, please immediately notify us by replying to the
messageand delete the original message immediately thereafter. 

Thank you.

                                   FADLD Tag
**********************************************************************