[prev in list] [next in list] [prev in thread] [next in thread]
List: postgresql-general
Subject: Re: Question on overall design
From: Ron Johnson <ronljohnsonjr () gmail ! com>
Date: 2023-12-12 4:53:34
Message-ID: CANzqJaCQTA5Om-rm0LBVVORWRY9pWp8EHfDhHpM7nXpZGo_zNw () mail ! gmail ! com
[Download RAW message or body]
On Mon, Dec 11, 2023 at 10:34 PM Chris Travers <chris.travers@gmail.com>
wrote:
> On Tue, Dec 12, 2023 at 2:11 AM Ron Johnson <ronljohnsonjr@gmail.com>
> wrote:
>
>> On Mon, Dec 11, 2023 at 4:41 AM Dominique Devienne <ddevienne@gmail.com>
>> wrote:
>>
>>> On Sun, Dec 10, 2023 at 5:56 PM Ron Johnson <ronljohnsonjr@gmail.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.
[Attachment #3 (text/html)]
<div dir="ltr"><div dir="ltr">On Mon, Dec 11, 2023 at 10:34 PM Chris Travers <<a \
href="mailto:chris.travers@gmail.com">chris.travers@gmail.com</a>> \
wrote:<br></div><div class="gmail_quote"><blockquote class="gmail_quote" \
style="margin:0px 0px 0px 0.8ex;border-left:1px solid \
rgb(204,204,204);padding-left:1ex"><div dir="ltr"><div dir="ltr">On Tue, Dec 12, 2023 \
at 2:11 AM Ron Johnson <<a href="mailto:ronljohnsonjr@gmail.com" \
target="_blank">ronljohnsonjr@gmail.com</a>> wrote:<br></div><div \
class="gmail_quote"><blockquote class="gmail_quote" style="margin:0px 0px 0px \
0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex"><div dir="ltr"><div \
dir="ltr">On Mon, Dec 11, 2023 at 4:41 AM Dominique Devienne <<a \
href="mailto:ddevienne@gmail.com" target="_blank">ddevienne@gmail.com</a>> \
wrote:<br></div><div class="gmail_quote"><blockquote class="gmail_quote" \
style="margin:0px 0px 0px 0.8ex;border-left:1px solid \
rgb(204,204,204);padding-left:1ex"><div dir="ltr"><div dir="ltr">On Sun, Dec 10, 2023 \
at 5:56 PM Ron Johnson <<a href="mailto:ronljohnsonjr@gmail.com" \
target="_blank">ronljohnsonjr@gmail.com</a>> wrote:<br></div><div \
class="gmail_quote"><blockquote class="gmail_quote" style="margin:0px 0px 0px \
0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex"><div dir="ltr"><div>* \
We departitioned because SELECT statements were <i>slow</i>. All partitions were \
scanned, even when the partition key was specified in the WHERE \
clause.</div></div></blockquote><div><br></div><div>Surely that's no the case on \
newer PostgreSQL, is it? Otherwise what's the point of \
partitioning?</div><div>Also, I remember reading something about recent improvements \
with a large number of partitions, no?</div><div><br></div><div>As someone who's \
interested on partitioning, I'd appreciate details. Thanks, \
--DD</div></div></div></blockquote><div><br></div><div>This was on 12.5. v13 was \
just released, and we weren't confident about running a mission-critical system \
on a .1 version.</div></div></div></blockquote><div><br></div><div>Something's \
wrong if all partitions are scanned even when the partition clause is explicit in the \
where clause.</div><div><br></div><div>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. \
</div></div></div></blockquote><div><br></div><div>Here's a snippet. part_date \
(type timestamp without time zone) is the partition \
key:</div><div><br></div><div><font face="monospace"> and \
separation0_.part_date>=to_date('01-Jun-2021', 'DD-Mon-YYYY')<br> \
and transmissi1_.part_date>=to_date('01-Jun-2021', \
'DD-Mon-YYYY')<br> and \
separation0_.part_date=transmissi1_.part_date<br></font></div><div> \
</div><blockquote class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left:1px \
solid rgb(204,204,204);padding-left:1ex"><div dir="ltr"><div \
class="gmail_quote"><blockquote class="gmail_quote" style="margin:0px 0px 0px \
0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex"><div dir="ltr"><div \
class="gmail_quote"><div><br></div><div>All "transaction" tables were \
partitioned by month on partion_date, while the PK was table_name_id, \
partition_date.</div><div><br></div><div>Queries were _slow_, even when the \
application knew the partion_date range (since queries might span months). PG just \
wouldn't prune. </div></div></div></blockquote><div><br></div><div>Was there a \
datatype issue here? Like having a partition key of type timestamp, but the query \
casting from date? </div></div></div></blockquote><div><br></div><div>The partition \
key was of type timestamp, while "the right hand side of the \
predicate".would be whatever to_char() generated.</div><div> </div></div></div>
[prev in list] [next in list] [prev in thread] [next in thread]
Configure |
About |
News |
Add a list |
Sponsored by KoreLogic