Unindex Postgresql

The issue of foreign keys without indexing was the starting point of my blog. Just browsing and I found a postgresql version of unindex query. Copied the query here. Visit the original authors page for more explanation and an example. Expecially liked the commented code /*the first index columns must be the same as the key columns, but order doesn't matter*/.

SELECT c.conrelid::regclass AS "table",
       /* list of key column names in order */
       string_agg(a.attname, ',' ORDER BY x.n) AS columns,
       pg_catalog.pg_size_pretty(
          pg_catalog.pg_relation_size(c.conrelid)
       ) AS size,
       c.conname AS constraint,
       c.confrelid::regclass AS referenced_table,
       'create index '||c.conname||'_idx on '||c.conrelid::regclass||'('||string_agg(a.attname, ',' ORDER BY x.n)||');' idx
FROM pg_catalog.pg_constraint c
   /* enumerated key column numbers per foreign key */
   CROSS JOIN LATERAL
      unnest(c.conkey) WITH ORDINALITY AS x(attnum, n)
   /* name for each key column */
   JOIN pg_catalog.pg_attribute a
      ON a.attnum = x.attnum
         AND a.attrelid = c.conrelid
WHERE NOT EXISTS
        /* is there a matching index for the constraint? */
        (SELECT 1 FROM pg_catalog.pg_index i
         WHERE i.indrelid = c.conrelid
           /* the first index columns must be the same as the
              key columns, but order doesn't matter */
           AND (i.indkey::smallint[])[0:cardinality(c.conkey)-1]
               @> c.conkey)
  AND c.contype = 'f'
GROUP BY c.conrelid, c.conname, c.confrelid
ORDER BY pg_catalog.pg_relation_size(c.conrelid) DESC;

Access Path Suggestor - Postgresql

Here is an improved access path suggestor from postgresql metadata. Based from my Oracle version. In addition to visualized path alternative this generates a query based on the found access path. Could be used in getting familiar with a new data model. Should work also with foreign keys with multiple columns. Cycle detection missing. Just give names of the tables whose path alternatives you are interested in as :foo and :bar binds.

WITH RECURSIVE fks as (
SELECT cc.fk_schematable,
       cc.fk_table,
       string_agg(a.attname, ',' ORDER BY x.n) AS fk_columns,
       cc.conname AS constraint_name,
       cc.uk_schematable,
       cc.uk_table,
       string_agg(b.attname, ',' ORDER BY y.n) AS pk_columns,
       string_agg(cc.uk_table||'.'||b.attname||' = '||cc.fk_table||'.'||a.attname, ' and ' ORDER BY y.n) AS joi
  FROM (
  SELECT c.conrelid::regclass as uk_schematable,
         pk.relname uk_table,
         c.conname,
         c.confrelid::regclass as fk_schematable,
         fk.relname fk_table,
         c.conkey,
         c.confkey,
         c.conrelid,
         c.confrelid
    FROM pg_catalog.pg_constraint c
   INNER JOIN pg_catalog.pg_class pk ON c.conrelid!=0 AND c.conrelid=pk.oid
   INNER JOIN pg_catalog.pg_class fk ON c.confrelid!=0 AND c.confrelid=fk.oid
   WHERE c.contype = 'f'
  ) cc
   /* enumerated key column numbers per foreign key */
 CROSS JOIN LATERAL
      unnest(cc.conkey) WITH ORDINALITY AS x(attnum, n)
   /* name for each foreign key column */
  JOIN pg_catalog.pg_attribute a
    ON a.attnum = x.attnum
   AND a.attrelid = cc.conrelid
 CROSS JOIN LATERAL
      unnest(cc.confkey) WITH ORDINALITY AS y(attnum, n)
   /* name for each unique key column */
  JOIN pg_catalog.pg_attribute b
    ON b.attnum = y.attnum
   AND b.attrelid = cc.confrelid
 WHERE x.n=y.n 
 GROUP BY cc.fk_schematable, cc.fk_table, cc.conname, cc.uk_schematable, cc.uk_table
), pths as (
SELECT uk_schematable as o1,uk_table as t1,constraint_name,fk_schematable as o2,fk_table as t2,'-<' dir, joi
  FROM fks
 UNION ALL
SELECT fk_schematable as o1,fk_table as t1,constraint_name,uk_schematable as o2,uk_table as t2,'>-' dir, joi
  FROM fks
), rcte(o1,t1,constraint_name,o2,t2,dir,lvl,pth,fro,joi) as (
SELECT o1,t1,constraint_name,o2,t2,dir,1 lvl,o1||dir||o2 pth, o1||' '||t1||', '||o2||' '||t2, joi
  FROM pths
 WHERE t1 = :foo
 UNION ALL 
SELECT s.o1,s.t1,s.constraint_name,s.o2,s.t2
     , s.dir,lvl+1
     , prio.pth||s.dir||s.o2
     , prio.fro||', '||s.o2||' '||s.t2
     , prio.joi||' and '||s.joi
 FROM rcte prio INNER JOIN pths s ON prio.o2=s.o1 AND prio.constraint_name != s.constraint_name
WHERE prio.lvl < 30
)
SELECT pth,'select * from '||fro||' where '||joi||';' sq
  FROM rcte
 WHERE t2 = :baz
 ORDER BY lvl,pth
;

Testing

create table foo(i int primary key);

create table bar(j int primary key, i int references foo);

create table baz(k int primary key, j int references bar);

pth             sq
foo>-bar>-baz select * from foo foo, bar bar, baz baz where bar.i = foo.i and baz.j = bar.j;

Pascal Matrix - Wishes Come True

Years ago I made a wish in my blog that an analytic function should work in a recursive query. My approach to generate Pascal Matrix seems to work with Oracle 19c database.

with n (u) as (
select 1 from dual
union all
select n.u+1 
  from n
 where n.u < 8
), q as (
select n.u v, m.u w 
  from n, n m 
), r (v,w,s,d,e) as (
select v,w, v,w,sum(w)over(order by w)
  from q
 where v = 1
union all
select q.v,q.w
      ,r.d
      ,r.e
      ,sum(r.e)over(order by r.w)
  from r 
 inner join q 
    on r.w=q.w and r.v+1=q.v
)
select v,w,s
  from r
;