Re: [GENERAL] Need advice to avoid ORDER BY

2013-04-06 Thread Jasen Betts
On 2013-04-04, Condor 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 fr

Re: [GENERAL] Need advice to avoid ORDER BY

2013-04-05 Thread Condor
On 2013-04-05 01:54, Merlin Moncure wrote: On Thu, Apr 4, 2013 at 5:15 PM, Tom Lane wrote: Merlin Moncure 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 be

Re: [GENERAL] Need advice to avoid ORDER BY

2013-04-04 Thread Scott Marlowe
Try an index like: create index yada on mytable (id) where valids=0; then select max(jobid) from mytable where valids=0; On Thu, Apr 4, 2013 at 3:32 PM, Condor 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 o

Re: [GENERAL] Need advice to avoid ORDER BY

2013-04-04 Thread Merlin Moncure
On Thu, Apr 4, 2013 at 5:15 PM, Tom Lane wrote: > Merlin Moncure 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. s

Re: [GENERAL] Need advice to avoid ORDER BY

2013-04-04 Thread Tom Lane
Merlin Moncure 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 in

Re: [GENERAL] Need advice to avoid ORDER BY

2013-04-04 Thread John R Pierce
On 4/4/2013 2:49 PM, Condor wrote: 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. the query can't use that index, and the separate index on id at the same time, it has to p

Re: [GENERAL] Need advice to avoid ORDER BY

2013-04-04 Thread Merlin Moncure
On Thu, Apr 4, 2013 at 4:49 PM, Condor 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. >> >>

Re: [GENERAL] Need advice to avoid ORDER BY

2013-04-04 Thread Condor
On 2013-04-05 00:38, Merlin Moncure wrote: On Thu, Apr 4, 2013 at 4:32 PM, Condor 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 f

Re: [GENERAL] Need advice to avoid ORDER BY

2013-04-04 Thread Merlin Moncure
On Thu, Apr 4, 2013 at 4:32 PM, Condor 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

[GENERAL] Need advice to avoid ORDER BY

2013-04-04 Thread Condor
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