Postgres Pro
Downloads
Home   >   mailing lists

Thread: indexes not working very well

indexes not working very well

From
"Vilson farias"
Date:
Greetings,

    I have a little problem here and need some help. I created a table where
indexes are not working very well here. Please take a look at the code below
(it's easier).

  Two databases with same problem : 7.0.2 and 7.0.3 both on linux redhat
6.2.

  Am I doing something wrong?

bxs=#
bxs=# CREATE TABLE hora_minuto(
bxs(#        hora        char(5),
bxs(#        hora_minuto TIME,
bxs(#        CONSTRAINT XPKhora_minuto PRIMARY KEY (hora, hora_minuto)
bxs(# )
bxs-# ;
NOTICE:  CREATE TABLE/PRIMARY KEY will create implicit index
'xpkhora_minuto' for table 'hora_minuto'
CREATE
bxs=#
bxs=#
bxs=# \d hora_minuto
       Table "hora_minuto"
  Attribute  |  Type   | Modifier
-------------+---------+----------
 hora        | char(5) | not null
 hora_minuto | time    | not null
Index: xpkhora_minuto

bxs=#
bxs-#
bxs-#
bxs-#
bxs-#
bxs-# INSERT INTO hora_minuto VALUES( '', '13:38:00');
ERROR:  parser: parse error at or near "]"
bxs=# INSERT INTO hora_minuto VALUES( '', '13:39:00');
INSERT 2675143 1
bxs=# INSERT INTO hora_minuto VALUES( '', '13:40:00');
INSERT 2675144 1
bxs=# INSERT INTO hora_minuto VALUES( '', '13:41:00');
INSERT 2675145 1
bxs=# INSERT INTO hora_minuto VALUES( '', '13:42:00');
INSERT 2675146 1
bxs=# INSERT INTO hora_minuto VALUES( '', '13:43:00');
INSERT 2675147 1
bxs=# INSERT INTO hora_minuto VALUES( '', '13:44:00');
INSERT 2675148 1
bxs=# INSERT INTO hora_minuto VALUES( '', '13:45:00');
INSERT 2675149 1
bxs=# INSERT INTO hora_minuto VALUES( '', '13:46:00');
INSERT 2675150 1
bxs=#
bxs=#
bxs=#
bxs=#
bxs=#
bxs=#
bxs=# EXPLAIN SELECT hora_minuto FROM hora_minuto WHERE hora_minuto >
'13:43:00';
NOTICE:  QUERY PLAN:

Seq Scan on hora_minuto  (cost=0.00..22.50 rows=333 width=8)

EXPLAIN
bxs=#
bxs=#
bxs=#



Regards,

José Vilson de Mello de Farias
Dígitro Tecnologia Ltda.

Re: indexes not working very well

From
Stephan Szabo
Date:
Have you vacuum analyzed recently?

On Fri, 23 Mar 2001, Vilson farias wrote:

> Greetings,
>
>     I have a little problem here and need some help. I created a table where
> indexes are not working very well here. Please take a look at the code below
> (it's easier).
>
> bxs=# EXPLAIN SELECT hora_minuto FROM hora_minuto WHERE hora_minuto >
> '13:43:00';
> NOTICE:  QUERY PLAN:
>
> Seq Scan on hora_minuto  (cost=0.00..22.50 rows=333 width=8)
>
> EXPLAIN

Re: indexes not working very well

From
Tom Lane
Date:
"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