Hi all - I resolved my issue - and took a 25 minute query down to 5 seconds. > I have a 400GB joining table (one SMALLINT and the other INTEGER - > What I would like to do is to partition by the SMALLINT (1 - 1000) > value - which would give 1,000 tables of 400MB each. I found this site very helpful for explaining the basics of partitioning (LIST, RANGE and HASH): https://www.enterprisedb.com/postgres-tutorials/how-use-table-partitioning-scale-postgresql I then found this absolute beaut of a site which was like manna from heaven: https://www.depesz.com/2021/01/17/are-there-limits-to-partition-counts/ which explained (they had a different issue - I adapted the code) how what I required can be done entirely from the psql client without the need for bash or PL/pgSQL or anything else. The "trick" here is to combine the FORMAT function with GENERATE_SERIES as follows (code from site): CREATE TABLE test_ranged (id serial PRIMARY KEY, payload TEXT) partition BY range (id); SELECT FORMAT ('CREATE TABLE %I partition OF test_ranged FOR VALUES FROM (%s) to (%s);', 'test_ranged_' || i, i, i+1) FROM generate_series(1, 2) i \gexec Output of this (having removed \gexec - another thing I learnt): format --------------------------------------------------------------------------------- CREATE TABLE test_ranged_1 partition OF test_ranged FOR VALUES FROM (1) to (2); CREATE TABLE test_ranged_2 partition OF test_ranged FOR VALUES FROM (2) to (3); So, I generated the series for (1, 1000) with my own fields using the LIST method with a single INTEGER value in the list. Ran the script - had my 1000 partitions in a matter of seconds. Loading them (750GB with indexes) was an overnight job however - but that's not PostgreSQL's fault! :-) I really love the way that PostgreSQL/psql is so flexible that it's possible to do heaps of stuff without having to resort to other tools. I posted this answer to my own question in the hope that it may help others in my situation. If I haven't been clear, or there's something missing, please let me know - or add your own opinions/experience if there's an alternative which may or may not be as efficient. I'm trying to collect as many strings to my bow as possible! Rgs, Pól...
