Ruben Blanco <rubenblan@xxxxxxxxx> writes: > I'm trying to reduce execution time on a query using a partial index, > but Postgres doesn't make a significant improvement, even when the > partial index is 30 times smaller than the index used currently. That doesn't really matter that much. The part of the index a given query will actually access is about the same size either way, ie same number of leaf tuples of the same size. If you're lucky, you might save one level of btree descent to reach the leaf pages; but considering that btree fanout for integer-size keys is several hundred to one, you need a size ratio of several hundred even to be assured of that. The planner does have a small correction to favor smaller indexes over larger, but it's so small that it's often lost in the noise. In this case I think it's probably getting swamped by rounding off the estimate of the number of leaf pages accessed to the nearest number of pages. So it doesn't see the partial index as being any cheaper to use than the full index. > Indexes: > "calls_201109_index_2" btree (company, call_date, caller_cli) > "calls_201109_index_partial" btree (company, call_date, caller_cli) WHERE call_date = '2011-09-01'::date In this case you could have made the partial index smaller in a useful way (ie, reducing the number of leaf pages touched) by omitting the call_date column, which is quite redundant given the WHERE clause. I experimented a bit with that, but found that there actually is a planner bug in that case --- it misestimates the number of index tuples to be read because of failing to account for the partial index predicate in one place. I'll see about fixing that, but in the meantime I don't think you are really going to get any win with the above line of thought, for a couple reasons: First, is there some reason why 2011-09-01 is such a special date that it deserves its own index, or are you just showing us a fragment of a grand plan to manually partition the index through creating a large set of partial indexes? That sort of approach is almost certainly going to be a dead loss once you consider the extra overhead of updating the indexes and the extra planning costs. Basically, while partitioning a table can be useful when it comes time to drop one partition, it doesn't save anything for routine queries except in very special cases; and since there's no equivalent administrative need at the index level, partitioning an index is not going to be a win. Second, even if you can omit the call_date column, that's not going to make this index much smaller, perhaps even not at all smaller depending on alignment considerations. You need to reduce the size of an index entry by probably a factor of 2 before it's worth the extra complexity. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
