Need for box type with 1/4 precision and gist indexes

From: Anzor Apshev <anzor(dot)apshev(at)gmail(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: Need for box type with 1/4 precision and gist indexes
Date: 2020-04-16 19:57:20
Message-ID: CAH3JP3YMKpbaneBNhwzoGWZ_AbVVU91zAn=xwaPeyFDmWb_52g@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Hi all! I would like to use postgres for time-series data and need
geometric index. For this reason I am using timescale extension and GiST
index on box type. Overall ingest and query performance is fantastic! But I
would like to optimize disk usage a bit. More technically my table schema:

CREATE EXTENSION IF NOT EXISTS timescaledb CASCADE;

CREATE TABLE tracks (
tstamp timestamp NOT NULL,
objectId integer NOT NULL,
rect box NOT NULL,
-- five more smallint fields
);
SELECT create_hypertable('tracks', 'tstamp', chunk_time_interval =>
interval '30 day');

At this point I am ingesting my test data ~85M rows. An checking table size

>SELECT pg_size_pretty( pg_database_size('db_test') );
>14 GB
Then created index like this:
CREATE INDEX rect_indx ON tracks USING gist(rect);
After index is created reported table size is - 16 GB.

Then I started to optimize disk usage. Builtin box type is 32 bytes (4x8
bytes), for my case i need only 2 bytes for coordinate precision, I changed
table schema like this:

CREATE TABLE tracks2 (
tstamp timestamp NOT NULL,
objectId integer NOT NULL,

-- replaced box type with 4 explicit coordinates
rleft smallint NOT NULL,
rtop smallint NOT NULL,
rright smallint NOT NULL,
rbottom smallint NOT NULL,

-- five more smallint fields
);

Ingesting the same test data, database size is only 7.2 GB!
After that I have created index like this:
CREATE INDEX rect_indx ON tracks2 USING gist( box( point(rleft,rtop),
point(rright,rbottom))) ;

With this index created my table size is 14 GB, which is disappointing. I
can't explain why db became so bloated.

Any help on this is much appreciated.

Thank you for reading to this point.

--
ГукIи псэкIи фыфей / Sincerely yours
Iэпщэ Анзор / Anzor Apshev

Browse pgsql-general by date

  From Date Subject
Next Message Magnus Hagander 2020-04-16 21:24:10 Re: Using of --data-checksums
Previous Message Stephen Carboni 2020-04-16 18:39:17 Using unlogged tables for web sessions