Re: Doesn't use index, why? - Mailing list pgsql-general

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