Make deparsing of column defaults faster

From: Jeff Janes <jeff(dot)janes(at)gmail(dot)com>
To: pgsql-hackers <pgsql-hackers(at)postgresql(dot)org>
Cc: Justin Pryzby <pryzby(at)telsasoft(dot)com>
Subject: Make deparsing of column defaults faster
Date: 2018-06-05 00:55:10
Message-ID: CAMkU=1xPqHP=7YPeChq6n1v_qd4WGf+ZvtnR-b+gyzFqtJqMMQ@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

The user docs say about column defaults: "The value is any variable-free
expression (subqueries and cross-references to other columns in the current
table are not allowed)"

And also say about pg_get_expr "If the expression might contain Vars,
specify the OID of the relation they refer to as the second parameter; if
no Vars are expected, zero is sufficient"

Since defaults can't contain Vars, this patch converts the second parameter
to zero in places where pg_get_expr is invoked on column defaults. Doing
this drops the time needed to run `pg_dump -s` on a 1600 column table with
defaults on all columns by a factor of 40. So pg_upgrade users with such
tables should see a big win (cc Justin).

I also made the change to the other places in the source which I could
identify with this issue. \d is now much faster for such tables as well,
but I didn't test the performance of the others.

There are surely more opportunities to speed up pg_get_expr for things
other than column defaults, but I wanted to submit this for now.

I have not tested the pg_upgrade using source versions <8.4, because I
can't get anything that old to "make check" correctly, so I have nothing to
test on.

Cheers,

Jeff

Attachment Content-Type Size
default_deparse_novar.patch application/octet-stream 4.3 KB

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Peter Eisentraut 2018-06-05 02:00:53 Re: Make deparsing of column defaults faster
Previous Message Michael Paquier 2018-06-05 00:42:24 Re: commitfest 2018-07