CODEWITHSUNDEEP
postgresqlperformanceindexingtimescaledb
xaroulis gekas
xaroulis gekas

Reputation: 165

Indexing on Timescaledb

I am testing some queries on Postgresql extension Timescaledb. The table is called timestampdb and i run some queries on that seems like this

select id13 from timestampdb where timestamp1 >='2010-01-01 00:05:00' and timestamp1<='2011-01-01 00:05:00',  
select avg(id13)::numeric(10,2) from timestasmpdb where timestamp1>='2015-01-01 00:05:00' and timestamp1<='2015-01-01 10:30:00'

When i create a hypertable i do this. create hyper_table('timestampdb','timestamp1') The thing is that now i want to create an index on id13.

should i try something like this?:

create hyper_table('timestampdb','timestamp1') ,import data of the table and then  create index on timestampdb(id13)

or something like this:

create table timestampdb,then create hypertable('timestampdb',timestamp1') ,import the data and then CREATE INDEX ON timestampdb (timestamp1,id13)

What is the correct way to do this?

Upvotes: 0

Views: 298

Answers (1)

k_rus
k_rus

Reputation: 3219

You can create an index without time dimension column, since you don't require it to be unique. Including time dimension column into an index is needed if an index contains UNIQUE or is PRIMARY KEY, since TimescaleDB partitions a hypertable into chunks on the time dimension column, which is timestamp1 in the question. If partitioning key will include space dimension columns in addition to time, they will need to be included too.

So in your case the following should be sufficient after the migration to hypertable:

create index on timestampdb(id13);

The question contains two queries and none of them need index on id13. It will be valuable to create the index on id13 if you expect different queries than in the question, which will contain condition or join on id13 column.

Upvotes: 2

Related Questions