From: | Ashutosh Bapat <ashutosh(dot)bapat(at)enterprisedb(dot)com> |
---|---|
To: | Alexey Bashtanov <bashtanov(at)imap(dot)cc> |
Cc: | pgsql-hackers <pgsql-hackers(at)postgresql(dot)org> |
Subject: | Re: patch: optimize information_schema.constraint_column_usage |
Date: | 2017-02-03 14:26:41 |
Message-ID: | CAFjFpRdz63+1m7VGxUuqb5c1YaHS5md+U=mNK+nQRJtnv5Lu-w@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
Please add this patch to the commitfest so that it's not forgotten.
On Fri, Feb 3, 2017 at 6:10 PM, Ashutosh Bapat
<ashutosh(dot)bapat(at)enterprisedb(dot)com> wrote:
> The patch applies cleanly. No regression test fails.
>
> On Thu, Feb 2, 2017 at 6:57 PM, Alexey Bashtanov <bashtanov(at)imap(dot)cc> wrote:
>> Hello hackers,
>>
>> The view information_schema.constraint_column_usage becomes slow when the
>> number of columns and constraints raise to substantial values.
>> This is because of a join condition that allows only join filter to enforce.
>> The patch is to optimize it.
>
> Right. Because there are three relations involved in the condition,
> it's evaluation is delayed till all three of them are joined, thus
> increasing the sizes of minion joins. With this change, the size of
> minion joins reduces. Since this is an all INNER join, splitting the
> condition should be fine.
>
>> See many_constraints.sql file attached for a performance test: create 3000
>> tables with 10 columns and a PK each and select * from the view.
>> The last statement works for 22 seconds on master branch, 34 milliseconds
>> optimized on my laptop.
>
> Yes, that's a lot of improvement. I can reproduce similar results on my laptop.
>
> --
> Best Wishes,
> Ashutosh Bapat
> EnterpriseDB Corporation
> The Postgres Database Company
--
Best Wishes,
Ashutosh Bapat
EnterpriseDB Corporation
The Postgres Database Company
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2017-02-03 14:43:33 | Re: pgsql: pageinspect: Try to fix some bugs in previous commit. |
Previous Message | Fabien COELHO | 2017-02-03 13:24:33 | Re: \if, \elseif, \else, \endif (was Re: PSQL commands: \quit_if, \quit_unless) |