Re: partition tree inspection functions - Mailing list pgsql-hackers

From Peter Eisentraut
Subject Re: partition tree inspection functions
Date
Msg-id 27f51cb0-b448-66ac-5bc8-12edeb656873@2ndquadrant.com
Whole thread Raw
In response to Re: partition tree inspection functions  (Michael Paquier <michael@paquier.xyz>)
Responses Re: partition tree inspection functions
List pgsql-hackers
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


pgsql-hackers by date:

Previous
From: Alvaro Herrera
Date:
Subject: Re: Listing triggers in partitions (was Re: Remove mention in docsthat foreign keys on partitioned tables)
Next
From: Peter Eisentraut
Date:
Subject: Re: Listing triggers in partitions (was Re: Remove mention in docsthat foreign keys on partitioned tables)