ST_MemSize
Prev  7.4. Geometry Accessors  Next

Name

ST_MemSize — Returns the amount of memory space a geometry takes.

Synopsis

integer ST_MemSize(geometry geomA);

Description

Returns the amount of memory space (in bytes) the geometry takes.

This complements the PostgreSQL built-in database object functions pg_column_size, pg_size_pretty, pg_relation_size, pg_total_relation_size.

[Note]

pg_relation_size which gives the byte size of a table may return byte size lower than ST_MemSize. This is because pg_relation_size does not add toasted table contribution and large geometries are stored in TOAST tables.

pg_total_relation_size - includes, the table, the toasted tables, and the indexes.

pg_column_size returns how much space a geometry would take in a column considering compression, so may be lower than ST_MemSize

This function supports 3d and will not drop the z-index.

This method supports Circular Strings and Curves.

This function supports Polyhedral surfaces.

This function supports Triangles and Triangulated Irregular Network Surfaces (TIN).

Changed: 2.2.0 name changed to ST_MemSize to follow naming convention.

Examples

Compare the memory used by all geometries in a Massachusetts town dataset with the subset for Boston.

Code
SELECT pg_size_pretty(sum(ST_MemSize(geom))) AS totgeomsum,
       pg_size_pretty(sum(CASE WHEN town = 'BOSTON' THEN ST_MemSize(geom) ELSE 0 END)) AS bossum,
       cast(
           sum(CASE WHEN town = 'BOSTON' THEN ST_MemSize(geom) ELSE 0 END) * 100.0
           / sum(ST_MemSize(geom)) AS numeric(10, 2)
       ) AS perbos
FROM towns;
Output
 totgeomsum | bossum | perbos
------------+--------+--------
 1522 kB    | 30 kB  |   1.99
(1 row)

Return the memory size of a CircularString in bytes.

Code
SELECT ST_MemSize('CIRCULARSTRING(220268 150415,220227 150505,220227 150406)'::geometry);
Output
 st_memsize
------------
         80
(1 row)

Calculate the percentage of a table's total size used by geometry data.

Code
SELECT pg_total_relation_size('public.neighborhoods') AS fulltable_size,
       sum(ST_MemSize(geom)) AS geomsize,
       sum(ST_MemSize(geom)) * 100.0
           / pg_total_relation_size('public.neighborhoods') AS pergeom
FROM neighborhoods;
Output
 fulltable_size | geomsize |         pergeom
----------------+----------+--------------------------
         262144 |    96238 | 36.71188354492187500000
(1 row)