On Thu, Apr 4, 2013 at 4:32 PM, Condor <condor@xxxxxxxxxx> wrote: > Hello, > > I have one query in my postgresql 9.2.3 that took 137 ms to me executed and > looking a way > what I can do to optimize it. I have one table generated numbers from 1 to 1 > 000 000 and > I need to get first free id, meanwhile id's when is taken can be free > (deleted data and id > is free for next job). Table is simple: > > > id serial, > jobid text, > valids int default 0 > > (Yes, I have index). > > > my query is: SELECT jobid FROM mytable WHERE valids = 0 ORDER BY id ASC > LIMIT 1 > > I need the first id only. > > My question is: Is there a way how I can avoid using ORDER BY to receive the > first > free id from mytable ? well, you can (via EXISTS()), but you can really optimize this with partial index. CREATE INDEX ON mytable (id) WHERE valids = 0; then, 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 -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
