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