Re: Question on overall design

On Mon, Dec 11, 2023 at 10:34 PM Chris Travers <chris(dot)travers(at)gmail(dot)com>
wrote:

> On Tue, Dec 12, 2023 at 2:11 AM Ron Johnson <ronljohnsonjr(at)gmail(dot)com>
> wrote:
>
>> On Mon, Dec 11, 2023 at 4:41 AM Dominique Devienne <ddevienne(at)gmail(dot)com>
>> wrote:
>>
>>> On Sun, Dec 10, 2023 at 5:56 PM Ron Johnson <ronljohnsonjr(at)gmail(dot)com>
>>> wrote:
>>>
>>>> * We departitioned because SELECT statements were *slow*. All
>>>> partitions were scanned, even when the partition key was specified in the
>>>> WHERE clause.
>>>>
>>>
>>> Surely that's no the case on newer PostgreSQL, is it? Otherwise what's
>>> the point of partitioning?
>>> Also, I remember reading something about recent improvements with a
>>> large number of partitions, no?
>>>
>>> As someone who's interested on partitioning, I'd appreciate details.
>>> Thanks, --DD
>>>
>>
>> This was on 12.5. v13 was just released, and we weren't confident about
>> running a mission-critical system on a .1 version.
>>
>
> Something's wrong if all partitions are scanned even when the partition
> clause is explicit in the where clause.
>
> There are however some things which can cause problems here, such as type
> casts of the partition key, or when the partition key is being brought in
> from a join.
>

Here's a snippet. part_date (type timestamp without time zone) is the
partition key:

and separation0_.part_date>=to_date('01-Jun-2021', 'DD-Mon-YYYY')
and transmissi1_.part_date>=to_date('01-Jun-2021', 'DD-Mon-YYYY')
and separation0_.part_date=transmissi1_.part_date

>
>> All "transaction" tables were partitioned by month on partion_date, while
>> the PK was table_name_id, partition_date.
>>
>> Queries were _slow_, even when the application knew the partion_date
>> range (since queries might span months). PG just wouldn't prune.
>>
>
> Was there a datatype issue here? Like having a partition key of type
> timestamp, but the query casting from date?
>

The partition key was of type timestamp, while "the right hand side of the
predicate".would be whatever to_char() generated.