Strategies for long term data growth optimization for a query with an index on a joined table

Question

Given two simple, contrived example tables

create schema test;

create table test.exchange (
  id serial primary key,
  created_at date not null
);
create index on test.exchange(created_at);

create table test.exchange_content (
  id serial primary key,
  content text not null,
  exchange integer not null references "exchange"(id)
);
create index on test.exchange_content(exchange);

with a lot fake data

INSERT INTO test.exchange(id,created_at)
SELECT id, NOW() + (random() * (NOW()+'360 days' - NOW())) + '30 days'
FROM generate_series(1,10000000) id;

INSERT INTO test.exchange_content(id,content,exchange)
SELECT id, md5(random()::text), id % 10000000 + 1
FROM generate_series(0,30000000) id;

Attempting this query...

select count(*) from exchange
left join exchange_content ec on exchange.id = ec.exchange
where created_at > '2021-10-14';

runs a sequential scan on exchange_content (for Postgres 9.6)

With these tables and large queries frequently being executed on them together, what are the strategies for ensuring that the queries will scale well, as the tables grow?

(Table partitions seem like the best option so far, is there any other options that I'm missing?)

Update: Based on comments it looks like upgrading to Postgres 12 would be best, but I'm still wondering if there are idiomatic strategies for designing tables with this structure and large amounts of data.

---

What have I considered:

• Composite indexes but they don't work across multiple tables
• Duplicating the created_at field over to exchange_content and giving it its own index
• Using a materialized view with the created_at field and the data I need from exchange_content
• It may be using Seq Scan because Seq Scan may be faster than using the index
• Partial indexes, but I don't think they apply given my requirements
• Using a table partition for date ranges

Answer 1

It is not inevitable that it will keep using the seq scan. Perhaps at some point it will change (indeed, if the tables are vacuumed and analyzed, I don't get the seq scan in the first place, as the anonymous horse indicates.)

However, if your tables keep growing into the future (rather than into the past) your query will count more and more rows, and this will take longer and longer to do.

Since you are using fake data anyway, why not just use more of it and see what happens? This is the key benefit of working with simulated data sets, you can simulate whatever you want.

With 9.6, I get a nested loop over two index scans as long as the exchange_content table is well-vacuumed enough that the index-only scan seems worthwhile. So I would say that the main strategy you have overlooked is to design your indexes so that you can get index-only scans, and make sure to vacuum often enough. For insert only/mostly tables, it can be hard to get autovac to vacuum often enough, (until v13 introduces autovacuum_vacuum_insert_scale_factor) and so until then it might be best accomplished by "manually" VACUUMing from cron jobs.

Moving the created_at column to the other table would be an obvious winner if you were using an inner join rather than left join (at which point, you could drop the join with exchange altogether and just query exchange_content). But if the left join is really needed, it is not clear how this could work. Also, assuming that exchange_content is not where the column naturally belongs, then moving/copying it there would be a denormalization. This generally comes at a high cost from a integrity and maintenance perspective even if it does pay off for SELECT performance.

It is not clear how partitioning could help here. You can't partition exchange_content by created_at without having the created_at column exist in that table. And if it did exist in that table, you wouldn't need partitioning you could just index it directly.