Re: Doesn't use index, why?

On 04 Jan 2001 11:55:14 -0500, Dave Smith wrote:
> Marco Catunda wrote:
>
> > Hello,
> >
> > I have some doubt about index.
> > Look the follow example:
> >
> >
> > naslog=# \d desconexao
> > Table "desconexao"
> > Attribute | Type | Modifier
> > ----------------+-------------+-------------------------------------------------
> > id | integer | not null default
> > nextval('id_desconexao'::text)
> > time | timestamp |
> > client_user | varchar(20) |
> > client | varchar(40) |
> > ip_nas | inet |
> > ip_client_user | inet |
> > disconnect | smallint |
> > user_time | interval |
> > data_rate | integer |
> > called_number | varchar(14) |
> > calling_number | varchar(14) |
> > filtrado | boolean | default 'f'::bool
> > Indices: client_desconexao_idx,
> > desconexao_pkey,
> > filtro_idx,
> > time_idx
> >
> >
> > naslog=# \d time_idx
> > Index "time_idx"
> > Attribute | Type
> > -----------+-----------
> > time | timestamp
> > btree
> >
> >
> > naslog=# explain select * from desconexao where time = '2000-12-01';
> > NOTICE: QUERY PLAN:
> >
> > Index Scan using time_idx on desconexao (cost=0.00..20.18 rows=5
> > width=103)
> >
> > EXPLAIN
> >
> >
> >
> > Every thing is perfect, but when i execute de follow query:
> >
> >
> > naslog=# explain select * from desconexao where time > '2000-12-01';
> > NOTICE: QUERY PLAN:
> >
> > Seq Scan on desconexao (cost=0.00..19547.71 rows=657958 width=103)
> >
> > EXPLAIN
> >
> >
> > Why postgresql didn't use the time_idx index? I only changed the
> > operator
> > "=" to ">" in clause "where".
> >
> >
> > Thank you.
> > Regards
> > -- Marco Catunda
> >
> >
> Firstly you should include your version along with the report.
>
> Two thoughts ... did you run vacuum?
> Of the total number of records how many are greater than 2000-12-01? It
> could be that postgresql figures that a large portion of the result set
> is greater than this date and it is faster to do a sequential scan

Sorry, the version of Postgresql is 7.0.3

This is the number of records in database:

naslog=# select count(*) from desconexao;
count
--------
658617
(1 row)

I changed the data of example because all records is greater than
'2000-12-01', sorry.
But when I try '2000-12-10' the samething happens.

naslog=# explain select * from desconexao where time < '2000-12-10';
NOTICE: QUERY PLAN:

Seq Scan on desconexao (cost=0.00..19547.71 rows=231489 width=103)

EXPLAIN
naslog=# explain select * from desconexao where time >=
'2000-12-10';
NOTICE: QUERY PLAN:

Seq Scan on desconexao (cost=0.00..19547.71 rows=427128 width=103)

EXPLAIN

The number of records are:

naslog=# select count(*) from desconexao where time >= '2000-12-10';
count
--------
585789
(1 row)

naslog=# select count(*) from desconexao where time < '2000-12-10';
count
-------
72828
(1 row)

I think the estimate rows (231489) in query < '2000-12-10' is far away
to real value (72828).
So I execute vacuum analyze:

naslog=# vacuum verbose analyze desconexao;

NOTICE: --Relation desconexao--
NOTICE: Pages 11318: Changed 0, reaped 2616, Empty 0, New 0; Tup
658617: Vac 200, Keep/VTL 0/0, Crash 0, UnUsed 8007, MinLen 105,
MaxLen 166; Re-using: Free/Avail. Space 150084/35492;
EndEmpty/Avail. Pages 0/105. CPU 3.08s/27.90u sec.
NOTICE: Index client_desconexao_idx: Pages 9786; Tuples 658617:
Deleted 200. CPU 2.50s/3.33u sec.
NOTICE: Index filtro_idx: Pages 2946; Tuples 658617: Deleted 200.
CPU 0.70s/3.20u sec.
NOTICE: Index time_idx: Pages 5503; Tuples 658617: Deleted 200. CPU
1.52s/3.04u sec.
NOTICE: Index desconexao_pkey: Pages 5893; Tuples 658617: Deleted
200. CPU 1.59s/3.12u sec.
NOTICE: Rel desconexao: Pages: 11318 --> 11315; Tuple(s) moved:
199. CPU 5.41s/1.03u sec.
NOTICE: Index client_desconexao_idx: Pages 9786; Tuples 658617:
Deleted 199. CPU 2.85s/1.79u sec.
NOTICE: Index filtro_idx: Pages 2946; Tuples 658617: Deleted 199.
CPU 0.71s/1.60u sec.
NOTICE: Index time_idx: Pages 5503; Tuples 658617: Deleted 199. CPU
1.51s/1.65u sec.
NOTICE: Index desconexao_pkey: Pages 5893; Tuples 658617: Deleted
199. CPU 1.59s/1.58u sec.
VACUUM

naslog=# explain select * from desconexao where time < '2000-12-10';
NOTICE: QUERY PLAN:

Seq Scan on desconexao (cost=0.00..19547.71 rows=231489 width=103)

EXPLAIN
naslog=# explain select * from desconexao where time >=
'2000-12-10';
NOTICE: QUERY PLAN:

Seq Scan on desconexao (cost=0.00..19547.71 rows=427128 width=103)

EXPLAIN

The same estimates values happen.
Is there a way to force index?

Thank you
-- Marco Catunda