PostgreSQL partition pruning problem

Question

Let us suppose that I have a table d_day containing two columns, id_day, and date, and a table fact that contains two columns, id_day and fact. My table fact is partitioned across id_day.

The request

SELECT * FROM fact
WHERE "ID_DAY" between <ID_A> and <ID_B>

is pruning partitions properly, and is almost instant but

SELECT * FROM fact
INNER JOIN d_dat USING (id_day)
WHERE date between <DATE_A> and <DATE_B>

is not. I have a UNIQUE constraint over date in my d_day column. So, I have a 1:1 relationship between the two columns.

On a broader level, I do not understand how the query planner deals with this demand.

Even something like :

SELECT * FROM fact
WHERE id_day IN (
 SELECT DISTINCT(id_day) WHERE date BETWEEN <DATE_A> and <DATE_B>
)

is slow.

Here is a practical example when filtering on DATEJ:

set enable_partitionwise_join = true; set enable_partitionwise_aggregate = true;
EXPLAIN (ANALYZE, COSTS, VERBOSE, BUFFERS, FORMAT JSON)(
select * from "F_TDOJ_HIST_2" fth
inner join "D_TRAIN" dt using ("ID_TRAIN")
inner join "DL_CAPTURE_DATE" dj using ("ID_JOUR", "JX")
where "JX" = -72 
and "ID_ENTNAT" between 4000 and 4999
and "DATEJ" = '2023-12-08'::date);

(Explain analyze)

And here is the example when filtering on ID_JOUR

set enable_partitionwise_join = true; set enable_partitionwise_aggregate = true;
EXPLAIN (ANALYZE, COSTS, VERBOSE, BUFFERS, FORMAT JSON)(
select * from "F_TDOJ_HIST_2" fth
inner join "D_TRAIN" dt using ("ID_TRAIN")
inner join "DL_CAPTURE_DATE" dj using ("ID_JOUR", "JX")
where "JX" = -72 
and "ID_ENTNAT" between 4000 and 4999
and "ID_JOUR" = 4756);

(Explain analyze)

Also, when I play the request using DATEJ on a copy of my database, partitioned across a different ID_JOUR span (30 ID_JOUR in a partition vs 3 ID_JOUR) now, I get this plan, which is much faster, so I know doing something is possible. I just do not understand why it is now slower, what to change, and why :

set enable_partitionwise_join = true; set enable_partitionwise_aggregate = true;
EXPLAIN (ANALYZE, COSTS, VERBOSE, BUFFERS, FORMAT JSON)(
select * from "F_TDOJ_HIST_2" fth
inner join "D_TRAIN" dt using ("ID_TRAIN")
inner join "DL_CAPTURE_DATE" dj using ("ID_JOUR", "JX")
where "JX" = -72 
and "ID_ENTNAT" between 4000 and 4999
and "DATEJ" = '2023-12-08'::date);

(Explain analyze)

• RonJohn: Do yourself a favor, and change all those object names to lower case.
• Vérace: And remove quoted identifiers!
• Doe Jowns: I know this is the usual standardization, but I am undergoing a legacy system. But still, I will change it in the question.

Answer 1

The decision which partitions must be scanned is either made during query planning or when the query starts executing. But PostgreSQL cannot know at that time what id_day values belong to the d_day rows where date satisfies the condition. So it cannot exclude any partition.

• Doe Jowns: You confirm that there is absolutely no way to filter on "DATE" through some reindexing of my table ? I have a lot of code to change if I must change all my filters from "DATE" to "ID_DAY".
• Laurenz Albe: Can you think of a theoretical way how this could work? I cannot, short of executing a query on d_day first and querying fact second, with a list of id_day.
• Doe Jowns: I have a 1:1 relationship between d_day and id_day in my table day (1 <--> 01/01/2010, 2 <--> 02/01/2010, ...), so it should be possible to deduce it. Still, I also tried a select from where id_day in (select distinct id_day from day where d_day = ...) and it was just as slow
• Laurenz Albe: Yes, that won't help either. You won't get partition pruning that way.
• Doe Jowns: But why doesn't it work ?