> PostgreSQL estimates that 2817675 rows satisfy the index condition and expects
> that it will have to scan many of them before it finds one that satisfies the
> filter condition. That turns out to be a wrong guess.
>
> You could create an index on (cars_ref, t), then PostgreSQL will certainly
> pick an index scan.
Thanks! But, the t (time constraint) already isolates a particular partition. The bigtable is partitioned on exactly
t,by year. This is why you do not see any other indexes/partitions being queried in the EXPLAIN.
...
PARTITION BY RANGE (t)
...
CREATE TABLE public.bigtable_y2020 PARTITION OF public.bigtable
FOR VALUES FROM ('2020-01-01 00:00:00') TO ('2021-01-01 00:00:00');
To me, it seems like filter on date is unnecessary when you already IS on such a partition!
I'd like to add, that when I do the same query DIRECTLY on the bigtable_y2020 (instead of the partition parent) it does
changeto "index scan" again.
best regards
K
