On Wed, 7 Apr 2004, Hemapriya wrote: > Hi, > > we have table having 23 million rows. > This is the table structure. > Table Request: > > Column | Type | Modifiers > -----------+-----------------------------+----------- > origindb | character(1) | not null > uid | integer | not null > rtype | integer | > senderid | integer | > destaddr | character varying(15) | > opid | integer | > devmodel | integer | > ikind | integer | > itemid | character varying(10) | > tranfk | integer | > enteredon | timestamp without time zone | > status | integer | > accountid | integer | > Indexes: > "request_pkey" primary key, btree (origindb, uid) > > I do max Query like this > > select max(uid) from request where originDB=1; > > it took around 20 min to return the result.. Since > max, count functions do the full table scan, i tried > the workaround given.. > > select uid from request where originDB=1 order by uid > desc limit 1; > > this query runs forever.. i tried even without where > condition..no result..
You really want an index on origindb and uid - the order by ... desc limit 1 workaround is only quick if there's an index on the order by field, and and where clause is faster if it can use an index to speed up the query. I would say you might want an index on both of them together, a joint index. -- Sam Barnett-Cormack Software Developer | Student of Physics & Maths UK Mirror Service (http://www.mirror.ac.uk) | Lancaster University ---------------------------(end of broadcast)--------------------------- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match