Обсуждение: 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..  

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..