Re: Query Fails with error calloc - Cannot alocate memory

If you're trying to retrieve 26 million rows into RAM in one go of
course it'll be trouble.

Just use a cursor. (DECLARE/FETCH/MOVE)

Chris

Howard Oblowitz wrote:
> Hi …
>
> I am trying to run a query that selects 26 million rows from a
>
> table with 68 byte rows.
>
> When run on the Server via psql the following error occurs:
>
> calloc : Cannot allocate memory
>
> When run via ODBC from Cognos Framework Manager only works
>
> if we limit the retrieval to 3 million rows.
>
> I notice that the memory used by the query when run on the Server increases
>
> to about 2.4 GB before the query fails.
>
> Postgres version is 7.3.4
>
> Running on Linux Redhat 7.2
>
> 4 GB memory
>
> 7 Processor 2.5 Ghz
>
> Shmmax set to 2 GB
>
> Configuration Parameters
>
> Shared Buffers 12 288
>
> Max Connections 16
>
> Wal buffers 24
>
> Sort Mem 40960
>
> Vacuum Mem 80192
>
> Checkpoint Timeout 600
>
> Enable Seqscan false
>
> Effective Cache Size 200000
>
>
> Results of explain analyze and expain analyze verbose:
>
> explain analyze select * from flash_by_branches;
>
> QUERY
> PLAN
>
> ----------------------------------------------------------------------------------------------------------------------------------------------
>
> Seq Scan on flash_by_branches (cost=100000000.00..100567542.06
> rows=26854106 width=68) (actual time=12.14..103936.35 rows=26854106 loops=1)
>
> Total runtime: 122510.02 msec
>
> (2 rows)
>
> explain analyze verbose:
>
> { SEQSCAN
>
> :startup_cost 100000000.00
>
> :total_cost 100567542.06
>
> :rows 26854106
>
> :width 68
>
> :qptargetlist (
>
> { TARGETENTRY
>
> :resdom
>
> { RESDOM
>
> :resno 1
>
> :restype 1043
>
> :restypmod 8
>
> :resname br_code
>
> :reskey 0
>
> :reskeyop 0
>
> :ressortgroupref 0
>
> :resjunk false
>
> }
>
> :expr
>
> { VAR
>
> :varno 1
>
> :varattno 1
>
> :vartype 1043
>
> :vartypmod 8
>
> :varlevelsup 0
>
> :varnoold 1
>
> :varoattno 1
>
> }
>
> }
>
> { TARGETENTRY
>
> :resdom
>
> { RESDOM
>
> :resno 2
>
> :restype 23
>
> :restypmod -1
>
> :resname fty_code
>
> :reskey 0
>
> :reskeyop 0
>
> :ressortgroupref 0
>
> :resjunk false
>
> }
>
> :expr
>
> { VAR
>
> :varno 1
>
> :varattno 2
>
> :vartype 23
>
> :vartypmod -1
>
> :varlevelsup 0
>
> :varnoold 1
>
> :varoattno 2
>
> }
>
> }
>
> { TARGETENTRY
>
> :resdom
>
> { RESDOM
>
> :resno 3
>
> :restype 1082
>
> :restypmod -1
>
> :resname period
>
> :reskey 0
>
> :reskeyop 0
>
> :ressortgroupref 0
>
> :resjunk false
>
> }
>
> :expr
>
> { VAR
>
> :varno 1
>
> :varattno 3
>
> :vartype 1082
>
> :vartypmod -1
>
> :varlevelsup 0
>
> :varnoold 1
>
> :varoattno 3
>
> }
>
> }
>
> { TARGETENTRY
>
> :resdom
>
> { RESDOM
>
> :resno 4
>
> :restype 1700
>
> :restypmod 786436
>
> :resname value
>
> :reskey 0
>
> :reskeyop 0
>
> :ressortgroupref 0
>
> :resjunk false
>
> }
>
> :expr
>
> { VAR
>
> :varno 1
>
> :varattno 4
>
> :vartype 1700
>
> :vartypmod 786436
>
> :varlevelsup 0
>
> :varnoold 1
>
> :varoattno 4
>
> }
>
> }
>
> { TARGETENTRY
>
> :resdom
>
> { RESDOM
>
> :resno 7
>
> :restype 1700
>
> :restypmod 786438
>
> :resname value1
>
> :reskey 0
>
> :reskeyop 0
>
> :ressortgroupref 0
>
> :resjunk false
>
> }
>
> :expr
>
> { VAR
>
> :varno 1
>
> :varattno 7
>
> :vartype 1700
>
> :vartypmod 786438
>
> :varlevelsup 0
>
> :varnoold 1
>
> :varoattno 7
>
> }
>
> }
>
> )
>
> :qpqual <>
>
> :lefttree <>
>
> :righttree <>
>
> :extprm ()
>
> :locprm ()
>
> :initplan <>
>
> :nprm 0
>
> :scanrelid 1
>
> }
>
> Seq Scan on flash_by_branches (cost=100000000.00..100567542.06
> rows=26854106 width=68) (actual time=6.59..82501.15 rows=2685
>
> 4106 loops=1)
>
> Total runtime: 102089.00 msec
>
> (196 rows)
>
>
>
> Please assist.
>
> Thanks,
>
> Howard Oblowitz
>
>
>
> ---
> Outgoing mail is certified Virus Free.
> Checked by AVG anti-virus system (http://www.grisoft.com).
> Version: 6.0.859 / Virus Database: 585 - Release Date: 14/02/2005
>
>