0

I am trying to optimize some queries on a huge table but I can't manage to make then faster. I was wondering is it possible to make timescaleDB run any faster?

Test results:

tracker=> select count(*) from clicks where click_at between '2019-04-15 00:00:00' and '2019-04-17 23:59:59';                     
  count   
----------
 31385884
(1 row)

Time: 2306,110 ms (00:02,306)

If I try to group the results, it gets a bit worse:

tracker=> select time_bucket('1 day', click_at) as ts, count(*) from clicks where click_at between '2019-04-15 00:00:00' and '2019-04-17 23:59:59' group by ts;
           ts           |  count   
------------------------+----------
 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 runs parallel index or seq scan, depending of the chunk size.

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

Is it possible to make it faster or not?

The clicks table structure is below.

Thanks in advance!

        Column         |           Type           | Collation | Nullable |      
         Default               
-----------------------+--------------------------+-----------+----------+------
-------------------------------
 id                    | bigint                   |           | not null | nextv
al('clicks_id_seq1'::regclass)
 click_at              | timestamp with time zone |           | not null | now()
 hash_id               | character varying        |           | 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           | character varying        |           |          | 
 connection_type       | smallint                 |           |          | 
 asn                   | integer                  |           |          | 
 lang                  | character varying        |           |          | 
 referrer              | 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                     |           |          | 
 custom_variables      | text                     |           |          | 
 devices_id            | integer                  |           |          | 
 ip_proxy              | inet                     |           |          | 
 tracking_users_id     | integer                  |           |          | 
 conversion_status     | smallint                 |           |          | 
Indexes:
    "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 INSERT ON clicks FOR EACH ROW EXECUTE PROCEDURE _ti
mescaledb_internal.insert_blocker()

  • It's not just you; count(*) is always slow because it requires a table scan due to MVCC ( so that you see rows inserted before your query started and not rows inserted after that point ). [Citus has a blog post][1] about speeding up counts which is well worth reading and covers the tradeoffs. The fact that usingtime_bucket() makes it slower suggests that the issue may be a shortage of memory. So you may need to tune your postgres configuration to increase the memory available for shared buffers and work_mem. [1]: citusdata.com/blog/2016/10/12/count-performance – Larry Apr 20 at 23:32

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.