undefined | Better HN

0 pointspgaddict8y ago0 comments

But you don't know which blocks you'll need at planning time, so you can't really check that.

You could of course check if the total database size is within RAM, but it's much more common to have database much larger than RAM (say 1TB on a machine with 128GB of RAM), but the actual working set (recent data processed by queries) is much smaller.

0 comments

4 comments · 1 top-level

pja8y ago· 3 in thread

But you don't know which blocks you'll need at planning time, so you can't really check that.

Really? Postgres doesn't know which blocks are part of which tables / indexes? That seems ... suboptimal if so.

jeltz8y ago

I guess one could implement a job which samples the pages of tables and indexes to check what percentage is typically in RAM and use that for an estimate, but to get which actual pages will be hit by a query you need to start executing it (you need to do an index lookup to see which index pages and table pages which will be accessed) which would defeat the purpose of query planning.

pja8y ago

Granted, but (AFAICT) in this case the database was doing a full table scan because it didn’t think the index was in memory. Checking to see whether the index itself is already loaded seems like something the query planner in principle ought to be able to do efficiently. (Obviously the existing codebase might make it difficult.)

anarazel8y ago

You can know it, but you've to actually evaluate the index lookups first, so ...?

j / k navigate · click thread line to collapse