From: | Doug Reynolds <mav(at)wastegate(dot)net> |
---|---|
To: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
Cc: | Teja Jakkidi <teja(dot)jakkidi05(at)gmail(dot)com>, gogala(dot)mladen(at)gmail(dot)com, pgsql-admin <pgsql-admin(at)lists(dot)postgresql(dot)org> |
Subject: | Re: Performance issue after creating partitions |
Date: | 2022-08-31 17:59:20 |
Message-ID: | 01000182f50ebddc-6a59c418-9495-41e7-882c-22b574f8d6bc-000000@email.amazonses.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-admin |
Tom is spot on with his suggestions.
The only thing that I'll add is that sometimes there is a mismatch with timestamp resolution, which prevents a direct inner join. In this case, you filter both partitioned tables with the greater than/less than constants, THEN complete the inner join with truncated dates. You will still have to do a full scan; however, you'll only be doing a full scan of the selected partitions.
Other than that, you need to provide the query for additional advice.
Doug
> On Aug 30, 2022, at 4:40 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>
> Teja Jakkidi <teja(dot)jakkidi05(at)gmail(dot)com> writes:
>> Anyone ever encountered the below situation as what I am noticing with partitions?
>
> You haven't shown us your query, so any answer would be blind speculation.
>
> However, in the spirit of blind speculation, I'm wondering if you're
> expecting those range constraints to propagate across a join. They
> won't; you'd need to duplicate the conditions for the other table.
>
> That is, if you have WHERE+JOIN/ON conditions amounting to
>
> WHERE a.a = b.b AND b.b = constant
>
> the planner is able to derive "a.a = constant" on the assumption of
> transitivity, and use that to constrain the scan of table a (ie,
> use an index, reject partitions at plan time, etc). But no such
> deduction happens for
>
> WHERE a.a = b.b AND b.b >= constant
>
> If you want a constrained scan of a, you need to write it out:
>
> WHERE a.a = b.b AND b.b >= constant AND a.a >= constant
>
> regards, tom lane
>
>
From | Date | Subject | |
---|---|---|---|
Next Message | Sbob | 2022-09-02 16:13:01 | pgbench log output |
Previous Message | Olivier Gautherot | 2022-08-31 06:01:58 | Re: Performance issue after creating partitions |