Richard Neill <rn214@xxxxxxxxxxxxxxxx> writes: > The problem is, when I now run my query, the planner ignores the > dedicated index "tbl_tracker_performance_1_idx", and instead uses both > of the full indexes... resulting in a much much slower query (9ms vs > 0.08ms). > A psql session is below. This shows that, if I force the planner to use > the partial index, by dropping the others, then it's fast. But as soon > as I put the full indexes back (which I need for other queries), the > query planner chooses them instead, and is slow. [ experiments with a similar test case ... ] I think the reason why the planner is overestimating the cost of using the partial index is that 9.1 and earlier fail to account for the partial-index predicate when estimating the number of index rows that will be visited. Because the partial-index predicate is so highly selective in this case, that results in a significant overestimate of how much of the index will be traversed. We fixed this for 9.2 in http://git.postgresql.org/gitweb/?p=postgresql.git&a=commitdiff&h=21a39de5809cd3050a37d2554323cc1d0cbeed9d but did not want to risk back-patching such a behavioral change. If you're stuck on 9.1 you might want to think about applying that as a local patch though. (BTW, the "fudge factor" change in that patch has been criticized recently; we've changed it again already for 9.3 and might choose to back-patch that into 9.2.3. But it's the rest of it that you care about anyway.) regards, tom lane -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
