| From: | Alexey Bashtanov <bashtanov(at)imap(dot)cc> |
|---|---|
| To: | pgsql-hackers(at)postgresql(dot)org |
| Subject: | patch: optimize information_schema.constraint_column_usage |
| Date: | 2017-02-02 13:27:51 |
| Message-ID: | 2d533e5b-687a-09fa-a772-dac9e6cf9dd4@imap.cc |
| Views: | Whole Thread | Raw Message | Download mbox | Resend email |
| Thread: | |
| Lists: | pgsql-hackers |
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.
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.
Best Regards,
Alexey Bashtanov
| Attachment | Content-Type | Size |
|---|---|---|
| many-constraints.sql | application/sql | 811 bytes |
| constraint_column_usage.1.patch | text/x-patch | 880 bytes |
| From | Date | Subject | |
|---|---|---|---|
| Next Message | Alvaro Herrera | 2017-02-02 13:30:56 | Re: Patch: Write Amplification Reduction Method (WARM) |
| Previous Message | Ruben Buchatskiy | 2017-02-02 13:27:16 | Non-deterministic behavior with floating point in parallel mode |