Is there that much of a performance difference, though? In your example the btree of the index of the 2 years of data is probably 1 level deeper than that of an individual month. If you're mostly querying the last month, only that part of the btree will be kept in memory. The size of the hot data of the index is similar in both cases. I fail to see how that would make much of a performance difference.
For bulk operations like (auto-)VACUUM, CLUSTER or dumping/restoring data it can make a big difference, though.
Yes. Sometimes up to 500x faster. It's not the index which is important but the table itself. PostgreSQL tables are heap tables. The index might be ordered by month but the rows it referrs to can be spread throught the entire large table.
There are realistic scenarios where each row can end up costing you an 8kb page fetch, reducing query speed by ~250x for narrow tables as you're bound by memory bandwidth, or worse, disk bandwidth.
There's also an additional performance benefit in being able to skip the index scan when querying by month, and just sequentially scanning the entire partition which is usually 2-3x faster to access the same amount of data.
> Yes. Sometimes up to 500x faster. It's not the index which is important but the table itself. PostgreSQL tables are heap tables. The index might be ordered by month but the rows it referrs to can be spread throught the entire large table.
That makes sense.
> There are realistic scenarios where each row can end up costing you an 8kb page fetch, reducing query speed by ~250x for narrow tables as you're bound by memory bandwidth, or worse, disk bandwidth.
I've actually had a similar scenario. CLUSTERing by the index solved that. But of course running CLUSTER on a huge table is very slow. If you can partition the data so you only have to cluster one of the partitions, that's a huge win.
> There's also an additional performance benefit in being able to skip the index scan when querying by month, and just sequentially scanning the entire partition which is usually 2-3x faster to access the same amount of data.
For bulk operations like (auto-)VACUUM, CLUSTER or dumping/restoring data it can make a big difference, though.