Re: partition tree inspection functions

On 2018/06/29 6:19, Peter Eisentraut wrote:
> 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.

Hmm, it would be nice if the user-specified filters could get pushed down
under the CTE scan that will get generated for recursive union, but it
doesn't afaics.  If there's no way to write the query such that they do
get pushed down, then using a C function to build the tree sounds better
than using a query.

For example, I compared using the quoted query (thanks Michael) and the
proposed pg_partition_tree_tables function on a partition tree with 1000
partitions and don't see much difference.

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 = 'ht' AND
              relkind = 'p'
      UNION ALL
        SELECT
             c.oid AS relid,
             c.relname AS relname,
             pg_relation_size(c.oid) AS relsize,
             c.relispartition AS relispartition,
             c.relkind AS relkind
        FROM partition_info AS p,
             pg_catalog.pg_inherits AS i,
             pg_catalog.pg_class AS c
        WHERE p.relid = i.inhparent AND
             c.oid = i.inhrelid AND
             c.relispartition
      )
    SELECT * FROM partition_info WHERE relname LIKE '%01%';
 relid │ relname │ relsize │ relispartition │ relkind
───────┼─────────┼─────────┼────────────────┼─────────
 18616 │ ht_101  │       0 │ t              │ r
 18916 │ ht_201  │       0 │ t              │ r
 19216 │ ht_301  │       0 │ t              │ r
 19516 │ ht_401  │       0 │ t              │ r
 19816 │ ht_501  │       0 │ t              │ r
 20116 │ ht_601  │       0 │ t              │ r
 20416 │ ht_701  │       0 │ t              │ r
 20716 │ ht_801  │       0 │ t              │ r
 21016 │ ht_901  │       0 │ t              │ r
(9 rows)

Time: 47.562 ms

select p::oid as relid, p as relname, pg_relation_size(p) as relsize,
c.relispartition, c.relkind
from pg_partition_tree_tables('ht') p, pg_class c
where p::oid = c.oid and p::text like '%01%';
 relid │ relname │ relsize │ relispartition │ relkind
───────┼─────────┼─────────┼────────────────┼─────────
 18616 │ ht_101  │       0 │ t              │ r
 18916 │ ht_201  │       0 │ t              │ r
 19216 │ ht_301  │       0 │ t              │ r
 19516 │ ht_401  │       0 │ t              │ r
 19816 │ ht_501  │       0 │ t              │ r
 20116 │ ht_601  │       0 │ t              │ r
 20416 │ ht_701  │       0 │ t              │ r
 20716 │ ht_801  │       0 │ t              │ r
 21016 │ ht_901  │       0 │ t              │ r
(9 rows)

Time: 34.357 ms

Am I missing something?

Now, if the users write the query themselves and add whatever filters they
want to use, then that might be the fastest.

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 = 'ht' AND
              relkind = 'p'
      UNION ALL
        SELECT
             c.oid AS relid,
             c.relname AS relname,
             pg_relation_size(c.oid) AS relsize,
             c.relispartition AS relispartition,
             c.relkind AS relkind
        FROM partition_info AS p,
             pg_catalog.pg_inherits AS i,
             pg_catalog.pg_class AS c
        WHERE p.relid = i.inhparent AND
             c.oid = i.inhrelid AND
             c.relispartition AND c.relname LIKE '%01%'
      )
    SELECT * FROM partition_info p WHERE p.relname LIKE '%01%';
 relid │ relname │ relsize │ relispartition │ relkind
───────┼─────────┼─────────┼────────────────┼─────────
 18616 │ ht_101  │       0 │ t              │ r
 18916 │ ht_201  │       0 │ t              │ r
 19216 │ ht_301  │       0 │ t              │ r
 19516 │ ht_401  │       0 │ t              │ r
 19816 │ ht_501  │       0 │ t              │ r
 20116 │ ht_601  │       0 │ t              │ r
 20416 │ ht_701  │       0 │ t              │ r
 20716 │ ht_801  │       0 │ t              │ r
 21016 │ ht_901  │       0 │ t              │ r
(9 rows)

Time: 27.276 ms

Thanks,
Amit