Re: is it normal behavior of index?

Jean-Yves F. Barbier <12ukwn@gmail.com> wrote:

> Yep, I HAD this behavior with my prior test version (nothing has
> changed: I just droped/recreated it) but not anymore:
> 
>                                              Table "public.tst1m"
>  Column |         Type          |                     Modifiers                      | Storage  | Description 
> --------+-----------------------+----------------------------------------------------+----------+-------------
>  id     | integer               | not null default nextval('tst1m_id_seq'::regclass) | plain    | 
>  name   | character varying(32) | not null                                           | extended | 
>  note   | character varying(64) | not null                                           | extended | 
> Indexes:
>     "tst1m_pkey" PRIMARY KEY, btree (id)
>     "tst1m_name_ix" UNIQUE, btree (name)
>     "tst1m_name_lmetaphone_ix" btree (lower(metaphone(name::text, 16)) varchar_pattern_ops)
>     "tst1m_name_lu_ix" btree (lower(jyunaccent(name)::text) text_pattern_ops)
>     "tst1m_note_ix" btree (note varchar_pattern_ops)
>     "tst1m_note_lu_ix" btree (note)
> Has OIDs: no
> 
> CREATE INDEX tst1m_name_regular_ix ON tst1m(name varchar_pattern_ops);
> 
> EXPLAIN  SELECT * FROM tst1m ORDER BY name;
>                              QUERY PLAN                              
> ---------------------------------------------------------------------
>  Sort  (cost=25402.82..25652.82 rows=100000 width=138)
>    Sort Key: name
>    ->  Seq Scan on tst1m  (cost=0.00..3084.00 rows=100000 width=138)
> (3 rows)

select *, without a WHERE-condition. In this case an index is useless,
the whole table is the result and a seq-scan the fastest way.


Andreas
-- 
Really, I'm not out to destroy Microsoft. That will just be a completely
unintentional side effect.                              (Linus Torvalds)
"If I was god, I would recompile penguin with --enable-fly."   (unknown)
Kaufbach, Saxony, Germany, Europe.              N 51.05082°, E 13.56889°