Re: partition tree inspection functions

On 6/28/18 13:30, Michael Paquier wrote:
> On Thu, Jun 28, 2018 at 12:37:14PM +0200, Peter Eisentraut wrote:
>> I'm thinking, an SQL query might be more efficient if you want to
>> qualify the query further. For example, give me all tables in this tree
>> that match '2018'. If you wrote your functions as SQL-language
>> functions, the optimizer could perhaps inline them and optimize them
>> further.
>
> Are you thinking about SQL functions here? Here is an example of query
> able to fetch an entire partition tree.
> WITH RECURSIVE partition_info
> (relid,
> relname,
> relsize,
> relispartition,
> relkind) AS (
> SELECT oid AS relid,
> relname,
> pg_relation_size(oid) AS relsize,
> relispartition,
> relkind
> FROM pg_catalog.pg_class
> WHERE relname = 'your_parent_table_name_here' AND
> relkind = 'p'
[...]

Yes, this kind of thing should be more efficient than building the
entire tree in a C function and then filtering it afterwards.

--
Peter Eisentraut http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services