ST_MemSize

ST_MemSize
Indietro	7.4. Accessori alla geometria	Avanti

Nome

ST_MemSize — Restituisce il tipo di geometria per il valore ST_Geometry.

Sinossi

integer ST_MemSize(geometry geomA);

Descrizione

Restituisce il tipo di geometria per il valore ST_Geometry.

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


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

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

Questa funzione supporta il 3d e non distrugge gli z-index.

Questo metodo supporta le Curve e le Circular String.

Questa funzione supporta le Polyhedral Surface.

Questa funzione supporta i Triangoli e le Triangulated Irregular Network Surfaces (TIN).

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

Esempi

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)