1

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
4
  • 1
    What is your Postgres version? With Postgres 12 I get this plan: explain.depesz.com/s/uWMN and if I enable parallel query I get this: explain.depesz.com/s/xFlb – a_horse_with_no_name Sep 21 '20 at 15:45
  • What exactly are you trying to count? If you want to only count rows in the exchange table an exists condition might be more efficient than the left join. You current query would count rows in the exchange table multiple times if there are multiple matching rows in the exchange_content table. – a_horse_with_no_name Sep 21 '20 at 15:46
  • @a_horse_with_no_name, Postgres 9.6. The query is just a contrived example. I have 2 real tables with a similar structure and the nature of the first table having the index on the date and the second table having a ton of data is making my queries huge and run Sequential Scans, so I'm looking for optimizations. – Jordan Sep 21 '20 at 16:00
  • 1
    you might want to consider upgrading to 12 nevertheless if you are really dealing with large tables like that to make use of the performance improvements and parallel query. (and apparently the optimizer in 12 does not use a seq scan in this case) – a_horse_with_no_name Sep 21 '20 at 16:17
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.

1
  • Good point, I've updated the question to reflect more that I'm asking if there are known, idomatic ways of designing tables / constraints around this type of situation – Jordan Sep 21 '20 at 16:27

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.