In brief:
Skip scan is not being used.
TimescaleDB version:
2.16.1
PostgreSQL version:
16.4
Install method:
Kubernetes using timescale/timescaledb-ha:pg16 image.
Environment:
Prod
Here’s my table and indices:
# \d submissions
Partitioned table "client_771.submissions"
Column | Type | Collation | Nullable | Default
----------------+-----------------------------+-----------+----------+-----------------------------------------
id | bigint | | not null | nextval('submissions_id_seq'::regclass)
award_cycle_id | bigint | | not null |
application_id | bigint | | not null |
opportunity_id | bigint | | not null |
category_id | bigint | | not null |
user_id | bigint | | not null |
answers | jsonb | | |
answer_sets | jsonb | | |
created_at | timestamp without time zone | | not null |
Partition key: LIST (award_cycle_id)
Indexes:
"submissions_application_id_index" btree (application_id)
"submissions_award_cycle_id_opportunity_id_application_id_create" UNIQUE, btree (award_cycle_id, opportunity_id, application_id, created_at DESC)
"submissions_user_id_index" btree (user_id)
Number of partitions: 14 (Use \d+ to list them.)
And here is the explain analyze:
# explain analyze select distinct on (application_id) * from submissions where award_cycle_id = 63 and opportunity_id = 52050 order by application_id, created_at DESC;
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Unique (cost=0.56..5926688.19 rows=211694 width=2037) (actual time=0.207..178324.380 rows=164306 loops=1)
-> Index Scan using submissions_award_cycle_63_award_cycle_id_opportunity_id_ap_idx on submissions_award_cycle_63 submissions (cost=0.56..5867244.18 rows=23777601 width=2037) (actual time=0.205..176616.915 rows=23802543 loops=1)
Index Cond: ((award_cycle_id = 63) AND (opportunity_id = 52050))
Planning Time: 0.891 ms
JIT:
Functions: 3
Options: Inlining true, Optimization true, Expressions true, Deforming true
Timing: Generation 0.180 ms, Inlining 39.524 ms, Optimization 12.590 ms, Emission 10.633 ms, Total 62.927 ms
Execution Time: 178391.822 ms
(9 rows)
Output of \dx:
# \dx
List of installed extensions
Name | Version | Schema | Description
-------------+---------+------------+---------------------------------------------------------------------------------------
plpgsql | 1.0 | pg_catalog | PL/pgSQL procedural language
timescaledb | 2.16.1 | client_771 | Enables scalable inserts and complex queries for time-series data (Community Edition)
(2 rows)
How can I use skip scan instead of index scan? Thanks for the help.