Re: indexes not working very well

"Vilson farias" <vilson.farias@digitro.com.br> writes:
>     I have a little problem here and need some help. I created a table where
> indexes are not working very well here.

Well, in the first place, an index on (hora, hora_minuto) is useless
for a query like WHERE hora_minuto > '13:43:00', because there's no
constraint on hora and so the index is in the wrong order: the desired
values do not fall into a subrange of the index order.

If you don't plan to do any queries on hora alone, then just reverse the
order of the primary key components.  Otherwise you might need two
indexes, one on (hora, hora_minuto) and one on just (hora_minuto).  See
http://www.postgresql.org/devel-corner/docs/postgres/indices-multicolumn.html
for more about that.

In the second place, the system will not use an indexscan unless the
planner thinks that it's cheaper than a sequential scan.  In practice
that means that the planner needs to think that the scan is going to
select only a small percentage of the rows in the table.  A one-sided
inequality (WHERE x > something) might or might not select a small
percentage.  In the absence of any VACUUM ANALYZE stats the default
estimate is that one-third of the rows will be selected by "x > something",
and that's too much to use an indexscan for.

You will see an indexscan if you (a) load up a bunch more data,
(b) VACUUM ANALYZE, and (c) use a constant that's close to the end
of the range of times, so that the planner realizes that not very
many rows will actually be scanned.

            regards, tom lane