Postgresql – TimescaleDB Performance Database Administrators Stack Exchange

I try to optimize some queries at a large table, but can not speed it up. I was wondering if it is possible to run TimescaleDB even faster?

Test results:




Select tracker => number (*) from clicks where click_at is between & # 39; 2019-04-15 00: 00: 00 & # 39; and & # 39; 2019-04-17 23: 59: 59 & # 39 ;;;
number
----------
31385884
(1st row)

Time: 2306.110 ms (00: 02.306)

When I try to group the results, it gets a little worse:




select tracker => time_bucket (& # 39; 1 day & # 39 ;, click_at) as ts, (*) count from clicks, where click_at is between & # 39; 2019-04-15 00: 00: 00 & # 39; and & # 39; 2019-04-17 23: 59: 59 & # 39; group after ts;
ts | number
------------------------ + ----------
2019-04-15 02: 00: 00 + 02 | 28855475
2019-04-14 02: 00: 00 + 02 | 2530409
(2 rows)

Time: 3453,420 ms (00: 03.453)

Both queries execute a parallel index or seq scan, depending on the block size.

It was expected that a piece has at least 100 million records.

Can you do it faster or not?

The clicks table structure is below.

Thank you in advance!

        
        
        
        Column | Type | Sorting | Nullable |
default
----------------------- + -------------------------- + ----------- + ---------- + ------
-------------------------------
id | bigint | | not null | NexTV
al (& # 39; clicks_id_seq1 & # 39;; regclass)
click_at | Timestamp with time zone | | not null | now()
hash_id | Characters vary | | not null |
offer_id | Integer | | not null |
affiliate_id | Integer | | not null |
affiliate_sub | Text | | |
affiliate_sub2 | Text | | |
affiliate_sub3 | Text | | |
affiliate_sub4 | Text | | |
affiliate_sub5 | Text | | |
Source | Text | | |
ip | inet | | |
country_iso | Characters vary | | |
Connection type | smallint | | |
asn | Integer | | |
long | Characters vary | | |
referer | Text | | |
device_types_id | Integer | | |
browsers_id | Integer | | |
offer_payout_types_id | Integer | | |
offer_urls_id | Integer | | |
offer_files_id | Integer | | |
affiliate_click_id | Text | | |
affiliate_unique1 | Text | | |
affiliate_unique2 | Text | | |
affiliate_unique3 | Text | | |
affiliate_unique4 | Text | | |
affiliate_unique5 | Text | | |
user_variables | Text | | |
devices_id | Integer | | |
ip_proxy | inet | | |
tracking_users_id | Integer | | |
Conversion status | smallint | | |
indices:
"clicks_affiliate_id_idx" btree (affiliate_id)
"clicks_click_at_idx" btree (click_at DESC)
"clicks_hash_id_idx" btree (hash_id)
"clicks_id_idx" btree (id)
"clicks_offer_id_idx" btree (offer_id)
"clicks_tracking_users_id_idx" btree (tracking_users_id)
Triggers:
ts_insert_blocker BEFORE INSERTING INCLUDE Click _ti for each series execution method
mescaledb_internal.insert_blocker ()