CODEWITHSUNDEEP
postgresqlquery-optimizationtimescaledb
Alex
Alex

Reputation: 4995

Postgres with timescale extension does not use skip scan in DISTINCT query

I want my query to use skip scan and followed this post: https://www.timescale.com/blog/how-we-made-distinct-queries-up-to-8000x-faster-on-postgresql/

Skip scan should be available from timescale version 2.2.1 onwards. I am using 2.3.1.

For this I generated a new index as described in the post (psr_type_tzc_timestampidx) enter image description here

If I run this query

EXPLAIN ANALYSE SELECT DISTINCT ON (psr_type) psr_type, utc_timestamp
   FROM generation_per_type
   ORDER BY psr_type, utc_timestamp DESC

the query plan looks like this:

Unique  (cost=126.37..2781479.65 rows=20 width=12) (actual time=2114.861..21926.310 rows=20 loops=1)
  ->  Merge Append  (cost=126.37..2678236.07 rows=41297430 width=12) (actual time=2114.858..18887.986 rows=41297430 loops=1)
        Sort Key: _hyper_2_279_chunk.psr_type, _hyper_2_279_chunk.utc_timestamp DESC
        ->  Index Only Scan using _hyper_2_279_chunk_psr_type_ztc_timestampidx on _hyper_2_279_chunk  (cost=0.42..1651.47 rows=85650 width=12) (actual time=11.596..36.191 rows=85650 loops=1)
              Heap Fetches: 0
        ->  Index Only Scan using _hyper_2_280_chunk_psr_type_ztc_timestampidx on _hyper_2_280_chunk  (cost=0.42..2881.85 rows=149635 width=12) (actual time=15.261..59.023 rows=149635 loops=1)
              Heap Fetches: 0
        ->  Index Only Scan using _hyper_2_281_chunk_psr_type_ztc_timestampidx on _hyper_2_281_chunk  (cost=0.42..2889.45 rows=150069 width=12) (actual time=15.068..57.579 rows=150069 loops=1)
              Heap Fetches: 0
        ->  Index Only Scan using _hyper_2_282_chunk_psr_type_ztc_timestampidx on _hyper_2_282_chunk  (cost=0.42..2891.31 rows=150119 width=12) (actual time=14.395..67.302 rows=150119 loops=1)
              Heap Fetches: 0
        ->  Index Only Scan using _hyper_2_283_chunk_psr_type_ztc_timestampidx on _hyper_2_283_chunk  (cost=0.42..2891.56 rows=150136 width=12) (actual time=19.602..70.908 rows=150136 loops=1)
              Heap Fetches: 0
        ->  Index Only Scan using _hyper_2_284_chunk_psr_type_ztc_timestampidx on _hyper_2_284_chunk  (cost=0.42..2888.48 rows=150004 width=12) (actual time=16.743..63.102 rows=150004 loops=1)
              Heap Fetches: 0
        ->  Index Only Scan using _hyper_2_285_chunk_psr_type_ztc_timestampidx on _hyper_2_285_chunk  (cost=0.42..2892.36 rows=150189 width=12) (actual time=15.833..62.003 rows=150189 loops=1)

No sign of something like Custom Scan (SkipScan) on _hyper_8_79_chunk. What are possible reasons why skip scan has not been used for this query? And any idea what I could try to make it work?

Upvotes: 1

Views: 1071

Answers (1)

greenweeds
greenweeds

Reputation: 554

There's a cost analysis and it might choose to not use skipscan if the number of unique values is close to the number of total rows.

The other possibility is that you are using the Apache 2.0 licensed version of TimescaleDB in which case skipscan isn't available.

If neither of these factors apply I wonder if you might be able to get a reproducible case to share with the team as an issue? Hope this helps? Transparency: I work for Timescale

Upvotes: 1

Related Questions