useful commands

join_generation.sql

 SELECT format('begin;create table foo_%1s(id serial primary key, data1 int, data2 text, data3 json); SELECT create_distributed_table(''foo_%1s'',''id'');commit;', g.i, g.i) FROM generate_series(1, 10) g(i)
\gexec




select ' select count(*) from users_table ' || string_Agg('INNER
JOIN users_table u'|| x::text || ' USING (user_id)',' ') from
generate_Series(1,25)x;
\gexec


select ' select count(*) from users_table ' || string_Agg('LEFT
JOIN users_table u'|| x::text || ' USING (user_id)',' ') from
generate_Series(1,10)x;
\gexec


select ' select count(*) from users_table ' || string_Agg('FULL
JOIN users_table u'|| x::text || ' USING (user_id)',' ') from
generate_Series(1,50)x;
\gexec


select ' select count(*) from users_table ' || string_Agg('FULL
JOIN users_table u'|| x::text || ' USING (user_id)',' ') from
generate_Series(1,100)x;
\gexec


select ' select count(*) from users_table ' || string_Agg('FULL
JOIN users_table u'|| x::text || ' USING (user_id)',' ') from
generate_Series(1,250)x;
\gexec


SELECT 

format ('(SELECT users_table.user_id, users_table.value_1 FROM users_table, events_table WHERE users_table.user_id = events_table.user_id AND event_type IN (1,2,3,4)) as foo%1s,' , g.i) 
FROM generate_series(1, 10) g(i) 


UNION  
SELECT format (' foo1.user_id =  foo%1s.user_id AND ' , g.i) 
FROM generate_series(1, 10) g(i) 


ORDER BY 1;




 SELECT
        foo1.user_id, random()
    FROM
        (SELECT users_table.user_id, users_table.value_1 FROM users_table, events_table WHERE users_table.user_id = events_table.user_id AND event_type IN (1,2,3,4)) as foo1,
        (SELECT users_table.user_id, users_table.value_1 FROM users_table, events_table WHERE users_table.user_id = events_table.user_id AND event_type IN (5,6,7,8)) as foo2,
        (SELECT users_table.user_id, users_table.value_1 FROM users_table, events_table WHERE users_table.user_id = events_table.user_id AND event_type IN (9,10,11,12)) as foo3,
        (SELECT users_table.user_id, users_table.value_1 FROM users_table, events_table WHERE users_table.user_id = events_table.user_id AND event_type IN (13,14,15,16)) as foo4,
        (SELECT users_table.user_id, users_table.value_1 FROM users_table, events_table WHERE users_table.user_id = events_table.user_id AND event_type IN (17,18,19,20)) as foo5
    WHERE
        foo1.user_id = foo4.user_id AND
        foo1.user_id = foo2.user_id AND
        foo1.user_id = foo3.user_id AND
        foo1.user_id = foo4.user_id AND
        foo1.user_id = foo5.user_id;



 SELECT
        foo1.user_id, random()
    FROM
 (SELECT users_table.user_id, users_table.value_1 FROM users_table, events_table WHERE users_table.user_id = events_table.user_id AND event_type IN (1,2,3,4)) as foo9,
 (SELECT users_table.user_id, users_table.value_1 FROM users_table, events_table WHERE users_table.user_id = events_table.user_id AND event_type IN (1,2,3,4)) as foo8,
 (SELECT users_table.user_id, users_table.value_1 FROM users_table, events_table WHERE users_table.user_id = events_table.user_id AND event_type IN (1,2,3,4)) as foo7,
 (SELECT users_table.user_id, users_table.value_1 FROM users_table, events_table WHERE users_table.user_id = events_table.user_id AND event_type IN (1,2,3,4)) as foo6,
 (SELECT users_table.user_id, users_table.value_1 FROM users_table, events_table WHERE users_table.user_id = events_table.user_id AND event_type IN (1,2,3,4)) as foo5,
 (SELECT users_table.user_id, users_table.value_1 FROM users_table, events_table WHERE users_table.user_id = events_table.user_id AND event_type IN (1,2,3,4)) as foo4,
 (SELECT users_table.user_id, users_table.value_1 FROM users_table, events_table WHERE users_table.user_id = events_table.user_id AND event_type IN (1,2,3,4)) as foo3,
 (SELECT users_table.user_id, users_table.value_1 FROM users_table, events_table WHERE users_table.user_id = events_table.user_id AND event_type IN (1,2,3,4)) as foo2,
 (SELECT users_table.user_id, users_table.value_1 FROM users_table, events_table WHERE users_table.user_id = events_table.user_id AND event_type IN (1,2,3,4)) as foo10,
 (SELECT users_table.user_id, users_table.value_1 FROM users_table, events_table WHERE users_table.user_id = events_table.user_id AND event_type IN (1,2,3,4)) as foo1
 WHERE
  foo1.user_id =  foo9.user_id AND 
  foo1.user_id =  foo8.user_id AND 
  foo1.user_id =  foo7.user_id AND 
  foo1.user_id =  foo6.user_id AND 
  foo1.user_id =  foo5.user_id AND 
  foo1.user_id =  foo4.user_id AND 
  foo1.user_id =  foo3.user_id AND 
  foo1.user_id =  foo2.user_id AND 
  foo1.user_id =  foo10.user_id AND 
  foo1.user_id =  foo1.user_id  ;
  
  
  
  
WITH start AS (select ' SELECT * FROM ( select u1.*, random() from users_table '),
     mid AS ( SELECT string_Agg('INNER JOIN users_table u'|| x::text || ' USING (user_id)',' ') from generate_Series(1,10)x ),
     end_cte AS (SELECT ' ) as foo')
SELECT * FROM mid ;




  SELECT * FROM ( select count(*), random() from users_table 
 INNER JOIN users_table u1 USING (user_id) INNER JOIN users_table u2 USING (user_id) INNER JOIN users_table u3 USING (user_id)  ) as foo



  SELECT * FROM ( select u1.*, random() from users_table 
 INNER JOIN users_table u1 USING (user_id) INNER JOIN users_table u2 USING (user_id) INNER JOIN users_table u3 USING (user_id) INNER JOIN users_table u4 USING (user_id) INNER JOIN users_table u5 USING (user_id) INNER JOIN users_table u6 USING (user_id) INNER JOIN users_table u7 USING (user_id) INNER JOIN users_table u8 USING (user_id) INNER JOIN users_table u9 USING (user_id) INNER JOIN users_table u10 USING (user_id) INNER JOIN users_table u11 USING (user_id) INNER JOIN users_table u12 USING (user_id) INNER JOIN users_table u13 USING (user_id) INNER JOIN users_table u14 USING (user_id) INNER JOIN users_table u15 USING (user_id) INNER JOIN users_table u16 USING (user_id) INNER JOIN users_table u17 USING (user_id) INNER JOIN users_table u18 USING (user_id) INNER JOIN users_table u19 USING (user_id) INNER JOIN users_table u20 USING (user_id) INNER JOIN users_table u21 USING (user_id) INNER JOIN users_table u22 USING (user_id) INNER JOIN users_table u23 USING (user_id) INNER JOIN users_table u24 USING (user_id) INNER JOIN users_table u25 USING (user_id) INNER JOIN users_table u26 USING (user_id) INNER JOIN users_table u27 USING (user_id) INNER JOIN users_table u28 USING (user_id) INNER JOIN users_table u29 USING (user_id) INNER JOIN users_table u30 USING (user_id) INNER JOIN users_table u31 USING (user_id) INNER JOIN users_table u32 USING (user_id) INNER JOIN users_table u33 USING (user_id) INNER JOIN users_table u34 USING (user_id) INNER JOIN users_table u35 USING (user_id) INNER JOIN users_table u36 USING (user_id) INNER JOIN users_table u37 USING (user_id) INNER JOIN users_table u38 USING (user_id) INNER JOIN users_table u39 USING (user_id) INNER JOIN users_table u40 USING (user_id) INNER JOIN users_table u41 USING (user_id) INNER JOIN users_table u42 USING (user_id) INNER JOIN users_table u43 USING (user_id) INNER JOIN users_table u44 USING (user_id) INNER JOIN users_table u45 USING (user_id) INNER JOIN users_table u46 USING (user_id) INNER JOIN users_table u47 USING (user_id) INNER JOIN users_table u48 USING (user_id) INNER JOIN users_table u49 USING (user_id) INNER JOIN users_table u50 USING (user_id)
  ) as foo

useful_commands.sql

 SELECT format('begin;create table top_%1s(id serial primary key, data1 int, data2 text, data3 json, data_%1s int);commit;', g.i, 2 * g.i) FROM generate_series(1, 100) g(i)
\gexec

postgres[2627319][1]=# SELECT format('begin;create table foo_%1s(id serial primary key, data1 int, data2 text, data3 json);commit;', g.i) FROM generate_series(1, 100000) g(i)
\gexec


Query to access all tables named foo*:

DO $$
  DECLARE
    cnt int := 0;
    v record;
  BEGIN
    FOR v IN SELECT * FROM pg_class WHERE relkind = 'r' and relname LIKE 'foo%' LOOP
        EXECUTE format('SELECT count(*) FROM %s', v.oid::regclass::text);
        cnt = cnt + 1;
        IF cnt % 100 = 0 THEN
            COMMIT;
        END IF;
    END LOOP;
    RAISE NOTICE 'tables %1', cnt;
  END;$$;


WITH RECURSIVE contexts AS (
    SELECT * FROM pg_backend_memory_contexts),
    caches AS (
        SELECT *
        FROM contexts
        WHERE name = 'CacheMemoryContext'

      UNION ALL
        SELECT contexts.*
        FROM caches
        JOIN contexts ON (contexts.parent = caches.name)
    )
SELECT
    name, parent,
    sum(total_bytes) size_bytes,
    pg_size_pretty(sum(total_bytes)) size_human,
    count(*) AS num_contexts
FROM caches
GROUP BY name, parent
ORDER BY SUM(total_bytes) DESC;