On Thu, Apr 4, 2013 at 4:49 PM, Condor <condor@xxxxxxxxxx> wrote: >> SELECT jobid FROM mytable WHERE valids = 0 ORDER BY id ASC LIMIT 1; >> >> should return in zero time since btree indexes can optimize order by >> expressions and the partial index will bypass having to wade through >> the rows you don't want. >> >> merlin > > > > Hm, > I only can say: Thank You! > Your solution is work, but Im now a little confused. I has a index > CREATE INDEX ON mytable (valids) USING BTREE (valids) and the > query to find valids = 0 tooks 137 ms. 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. merlin -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
