Обсуждение: Index not used,
Hi!<br />I've have this table:<br /><br />TABLE BB : There isn't any primary key, and is it more or less order (I mean,tt always is increased in each row, and id is nearly ordered)<br />ex:<br /><br />id, op, atr, tt<br /><br /> 1 0 X, 1 <br />2 0 A 3<br />3 0 X 5<br />..........<br />1 0 X 51<br />.......<br />85 1 l 150<br/>86 2 po 155<br />2 0 X 178<br />87 3 1 189<br />....<br /><br />I VACUUM ANALYZE each 10.000 insertsmore or less<br />in my case op only can have 3 values;<br />I've created an index on (id,op,tt) to improve the nextquery, that is executed very often:<br />"SELECT * FROM BB WHERE id="+ id+" AND op=0 order by tt desc;";<br />(becausethe only row I need is the one that has the highest tt)<br /><br />but, after an hour running the program (morethan 90.000 rows), I stopped it and <br />"EXPAIN SELECT * FROM BB WHERE id="+ id+" AND op=0 order by tempst desc;";<br/>But, my sorprise was that the index wasn't be used. Always do a Seq Scan.<br /><br />Have I to define the indexin other columns? Have I to VACUUM ANALYZE more often? <br /><br />There is other option to avoid the sort that is donebecause of ORDER BY?<br /><br />Thanks a lot<br /><br />yours,<br /><br />Cris..
On Wed, 9 Apr 2003, Cris wrote: > I've have this table: > > TABLE BB : There isn't any primary key, and is it more or less order (I mean, tt always is increased in each row, andid is > nearly ordered) > ex: > > id, op, atr, tt > > 1 0 X, 1 > 2 0 A 3 > 3 0 X 5 > .......... > 1 0 X 51 > ....... > 85 1 l 150 > 86 2 po 155 > 2 0 X 178 > 87 3 1 189 > .... > > I VACUUM ANALYZE each 10.000 inserts more or less > in my case op only can have 3 values; > I've created an index on (id,op,tt) to improve the next query, that is executed very often: > "SELECT * FROM BB WHERE id="+ id+" AND op=0 order by tt desc;"; > (because the only row I need is the one that has the highest tt) You might want to use limit 1 then to prevent it from getting all the rest of the rows as well. > but, after an hour running the program (more than 90.000 rows), I stopped it and > "EXPAIN SELECT * FROM BB WHERE id="+ id+" AND op=0 order by tempst desc;"; > But, my sorprise was that the index wasn't be used. Always do a Seq Scan. What is the actual explain output?
Stephan Szabo <sszabo@megazone23.bigpanda.com> writes: > On Wed, 9 Apr 2003, Cris wrote: >> I've created an index on (id,op,tt) to improve the next query, that is executed very often: >> "SELECT * FROM BB WHERE id="+ id+" AND op=0 order by tt desc;"; >> (because the only row I need is the one that has the highest tt) > You might want to use limit 1 then to prevent it from getting all the rest > of the rows as well. Also, I think you'll need to say "ORDER BY id, op, tt" to get it to realize that the index matches the required ordering. regards, tom lane
Cris, What version are you running and what are the datatypes of your columns? Prior to 7.3, postgres wouldn't use an index in the following case: column = 1 if the datatype of the column "column" wasn't integer. In order to get it to use the index you needed a cast, so if for example column was of type int8, you would use: column = 1::int8 so that both sides of the condition were the same datatype. Then the index would be used. thanks, --Barry Cris wrote: > Hi! > I've have this table: > > TABLE BB : There isn't any primary key, and is it more or less order (I mean, > tt always is increased in each row, and id is nearly ordered) > ex: > > id, op, atr, tt > > 1 0 X, 1 > 2 0 A 3 > 3 0 X 5 > .......... > 1 0 X 51 > ....... > 85 1 l 150 > 86 2 po 155 > 2 0 X 178 > 87 3 1 189 > .... > > I VACUUM ANALYZE each 10.000 inserts more or less > in my case op only can have 3 values; > I've created an index on (id,op,tt) to improve the next query, that is executed > very often: > "SELECT * FROM BB WHERE id="+ id+" AND op=0 order by tt desc;"; > (because the only row I need is the one that has the highest tt) > > but, after an hour running the program (more than 90.000 rows), I stopped it and > "EXPAIN SELECT * FROM BB WHERE id="+ id+" AND op=0 order by tempst desc;"; > But, my sorprise was that the index wasn't be used. Always do a Seq Scan. > > Have I to define the index in other columns? Have I to VACUUM ANALYZE more often? > > There is other option to avoid the sort that is done because of ORDER BY? > > Thanks a lot > > yours, > > Cris..