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