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