41–46 of 46 posts
I would have tried selecting just the needed column (let's call it "foo"), with following indexes:
event_types (app, id)
prop_keys (event_id, foo)
This should cover the entire query with indexes (i.e. allow for index-only scan).
Earlier quoted context omitted.
That feels like something the database could do for itself - checking whether a range of mmap()ed blocks are actually in memory or not is a single syscall. I guess for large indexes the overhead of walking the page tables is going to be large though, so it’s not necessarily going to be a net win.
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.
Really? Postgres doesn't know which blocks are part of which tables / indexes? That seems ... suboptimal if so.
Earlier quoted context omitted.
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.
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.
Earlier quoted context omitted.
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.
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.
This seems like something that should be measured at startup?
Also I imagine the some expensive SAN solutions would be pretty tricky to measure given how smart they try to be with caching and moving between different kinds of disks.
Earlier quoted context omitted.
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.
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.