TimescaleDB to handle complex join

Asked Feb 28 '19 at 08:40

Active Jun 02 '19 at 17:01

Viewed 657 times

I'm using Postgres 11 with timescaleDB in a LINUX machine(16 cores and 32 RAM). I have following 2 tables and only one is the time column.

usersTransations (time timestamp without time zone, userId String, locationID string)
location (locationID string, longitude double, latitude double )

This usersTransations is a hypertable with 24 Chunks which has created for 1 hour interval. and location is a Postgres table. I have created index on locationID (with time column). usersTransations table is very large about 1 billion records. I used following query to join above 2 tables but it take a very large time.

SELECT *
FROM usersTransations
LEFT JOIN location ON location."locationID"= usersTransations."locationID"

Is there any way to optimise join queries in TimescaleDB?

edited Jun 02 '19 at 17:01

marc_s

732,580
175
1,330
1,459

asked Feb 28 '19 at 08:40

AYD

3

what's your definition of a "very large time" - (it IS a billion records) – Michael Mullany Mar 01 '19 at 23:46
It took around 10 - 20 minutes – AYD Mar 03 '19 at 14:28
What version of TimescaleDB? – suntruth Mar 22 '19 at 14:00
Please clarify via edits not comments. Please give as much as possible of a [mcve]. – philipxy Jun 01 '19 at 09:11

TimescaleDB to handle complex join

0 Answers0