Re: Query Fails with error calloc - Cannot alocate memory - Mailing list pgsql-performance

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
>
>