Merlin Moncure <mmoncure@xxxxxxxxx> writes: > problem is that you are looking for needles (valids = 0) in the > haystack. the problem wasn't really the order, but the fact that you > had to scan an arbitrary amount of rows before finding a candidate > record. so the partial index manages this problem by creating index > entries *only for records that match a criteria*, and the planner > recognizes this and prefers that index when the criteria is also > present in the query. In other words, index only the needles. The other way to fix it is a two-column index on (valids, id), which will be more useful if sometimes you need the minimum/maximum id for some nonzero value of valids. The real point here is that you want the index to contain consecutive entries for the rows with the particular valids value you want, *in order by id*. Then the planner knows the first/last such index entry contains the answer. When you index only valids, it has to collect all the matching rows and sort them by id. 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
