Thread: Slow performance on MAX(primary_key)
Help, I have just been comparing some large table performance under 7.1 using the select max(primary key)from table; We are using this for various functions including sequence. It is taking 9 seconds to return this from around 1 million records. Shouldn't this be an instantaneous lookup? -- Keith Gray Technical Services Manager Heart Consulting Services
Hi Keith: --- Keith Gray <keith@heart.com.au> wrote: > Help, > > I have just been comparing some large table > performance > under 7.1 using the > > select max(primary key)from table; > > We are using this for various functions including > sequence. > Try using the following as alternative : SELECT primary_key FROM table ORDER BY primary_key desc LIMIT 1; This should work if primary_key is indexes. As of now, Max() doesn't utilizes the indices hence it always do a sequential scan. Hope that helps, regards, ludwig. __________________________________________________ Do you Yahoo!? New DSL Internet Access from SBC & Yahoo! http://sbc.yahoo.com
Ludwig Lim wrote: >>I have just been comparing some large table >>performance under 7.1 using the >> >> select max(primary key)from table; >> > > Try using the following as alternative : > > SELECT primary_key > FROM table > ORDER BY primary_key desc > LIMIT 1; > > This should work if primary_key is indexes. > > As of now, Max() doesn't utilizes the indices hence > it always do a sequential scan. Thanks Ludwig, That does help performance, but I was using a "standard" SQL command wrapped in a VB6 ADO ODBC program. Is this likely to be sorted in 7.2 ? Is anyone looking at this? -- Keith Gray Technical Services Manager Heart Consulting Services
On Tuesday 15 Oct 2002 8:47 am, Keith Gray wrote: > Ludwig Lim wrote: > > As of now, Max() doesn't utilizes the indices hence > > it always do a sequential scan. > > Thanks Ludwig, > > That does help performance, but I was using a "standard" > SQL command wrapped in a VB6 ADO ODBC program. > > Is this likely to be sorted in 7.2 ? > Is anyone looking at this? As I understand, the problem is that the optimisation only applies for simple cases, and for certain aggregate functions (e.g. not sum()). This means that the parser would need special-case code to spot these cases, along with tags for those functions that can be optimised. Thinking further, it might also vary from type to type. Given that there is a simple workaround and the need for the optimisation to be added cleanly to the code I believe this has a fairly low priority. There is a todo list on the developers' side of the website which has a list of changes in upcoming releases, you could check there for details. -- Richard Huxton
Richard Huxton wrote: >>> As of now, Max() doesn't utilizes the indices hence >>>it always do a sequential scan. >>Is this likely to be sorted in 7.2 ? >>Is anyone looking at this? > As I understand, the problem is that the optimisation only applies for simple > cases... Getting MIN() adn MAX() seems fairly trivial to me. When is on an index or more importantly Primary Key it must be a common SQL. Would it be possible in the code to look at the field in MIN() or MAX() and if it is indexed use a similar method to the suggested SQL work around? Can I help this to happen? -- Keith Gray Technical Services Manager Heart Consulting Services
Keith: I think it would be great to get the optimizer to do something smart on such a simple (and common) query. I am porting an app to Postgresql and I am not looking forward to having to fix all thepostgres-ism that seem trivial like this. Postgres gets a bad rap for this kinda simple qweak that makes out of the box queries perform slowly and I'd like to help improve the image. Let me know if you need another tester for the fixes. Currently on 7.2. here (7.2.3. I memory serves correct). Charlie Keith Gray wrote: > Richard Huxton wrote: > >>>> As of now, Max() doesn't utilizes the indices hence >>>> it always do a sequential scan. >>> > > >>> Is this likely to be sorted in 7.2 ? >>> Is anyone looking at this? >> > > >> As I understand, the problem is that the optimisation only applies >> for simple cases... > > > > Getting MIN() adn MAX() seems fairly trivial to me. > > When is on an index or more importantly Primary > Key it must be a common SQL. > > Would it be possible in the code to look at > the field in MIN() or MAX() and if it is > indexed use a similar method to the suggested > SQL work around? > > Can I help this to happen? > > > -- Charles H. Woloszynski ClearMetrix, Inc. 115 Research Drive Bethlehem, PA 18015 tel: 610-419-2210 x400 fax: 240-371-3256 web: www.clearmetrix.com
On Thursday 17 Oct 2002 12:46 am, Keith Gray wrote: > Getting MIN() adn MAX() seems fairly trivial to me. > > When is on an index or more importantly Primary > Key it must be a common SQL. > > Would it be possible in the code to look at > the field in MIN() or MAX() and if it is > indexed use a similar method to the suggested > SQL work around? > > Can I help this to happen? Subscribe to pgsql-hackers and talk through a proposed hack there. The developers always seem happy to receive contributions (you've just got to look at the contrib folder for evidence). -- Richard Huxton