> t > '2020-01-01'::timestamp and t < '2021-01-01'::timestamp
>Not at all important, but it seems odd to be exclusive of the start and end
>both. I would >consider including the start with >=
>Michael Lewis | Database Engineer
>Entrata
Michael, funny I was thinking that myself minutes after posti
‐‐‐ Original Message ‐‐‐
On Monday, September 7, 2020 9:04 AM, Klaudie Willis
wrote:
> Maybe the right way to improve this is to bypass add_paths_to_append_rel
> entirely when there's exactly one surviving child rel, and make it
> just use all the surviving paths for that child.
> re
Index Scan using bigtable_y2020_cars_ref_idx on bigtable_y2020 bigtable
(cost=0.57..2966738.51 rows=2873818 width=636) (actual time=0.026..0.026
rows=1 loops=1)
Given the system expects to get almost 3 million rows when it should be
just 1, it seems like a stats problem to me. How is ndistinct on
Klaudie Willis writes:
> I'd like to add, that when I do the same query DIRECTLY on the bigtable_y2020
> (instead of the partition parent) it does change to "index scan" again.
Yeah. I think the issue here is that add_paths_to_append_rel only
considers cheapest-total paths for the member relati
> 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
> p
On Fri, 2020-09-04 at 11:42 +, Klaudie Willis wrote:
> Postgres 13 beta3
>
> set enable_bitmapscan=1; -- default
> explain (analyze,buffers)
> select *
> from bigtable
> where cars_ref = 1769854207 and t > '2020-01-01'::timestamp and t <
> '2021-01-01'::timestamp
> limit 1
>
> Short sto
Postgres 13 beta3
set enable_bitmapscan=1; -- default
explain (analyze,buffers)
select *
from bigtable
where cars_ref = 1769854207 and t > '2020-01-01'::timestamp and t <
'2021-01-01'::timestamp
limit 1
Short story. Big table > 100M rows. b-tree index on cars_ref, the t constraints
limits it to
