Re: Is partition pruning impacted by data type

Thank you.

Yes, I tried creating a table manually with column timestamptz(6) type and
partitioned on that and then executed select query with the filter on that
column and I do see partition pruning happening. Not able to visualize any
other issues though, however some teammates say it may have a negative
impact on aggregation type queries , not sure how but will try to test it.
Thanks again for the response.

On Wed, Mar 6, 2024 at 12:35 AM Lok P <loknath(dot)73(at)gmail(dot)com> wrote:

>
> On Tue, Mar 5, 2024 at 1:09 AM sud <suds1434(at)gmail(dot)com> wrote:
>
>>
>> However the question we have is ,
>> 1)If there is any downside of having the partition key with "timestamp
>> with timezone" type? Will it impact the partition pruning of the queries
>> anyway by appending any run time "time zone" conversion function during the
>> query planning/execution phase?
>> 2) As it will take the default server times , so during daylight saving
>> the server time will change, so in that case, can it cause any unforeseen
>> issue?
>> 3)Will this cause the data to be spread unevenly across partitions and
>> make the partitions unevenly sized? If will go for UTC/GMT as db time, the
>> user's one day transaction might span across two daily partitions.
>>
>>
> My 2 cents.
> We have cases which use the "timestamp with timezone" column as partition
> key and the partition pruning happens for the read queries without any
> issue, so we don't see any conversion functions applied to the predicate as
> such which is partition key. I think if the users go global it's better to
> have the database time in UTC time zone. and it's obvious that, In case of
> global users the data ought to be span across multiple days as the days
> won't be as per the users time zone rather UTC.
>
>
>
>