Re: is it normal behavior of index?

From: Andreas Kretschmer <akretschmer(at)spamfence(dot)net>
To: pgsql-novice(at)postgresql(dot)org
Subject: Re: is it normal behavior of index?
Date: 2012-01-01 13:11:11
Message-ID: 20120101131111.GA12530@tux
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-novice

Jean-Yves F. Barbier <12ukwn(at)gmail(dot)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°

In response to

Responses

Browse pgsql-novice by date

  From Date Subject
Next Message Jean-Yves F. Barbier 2012-01-01 13:35:04 Re: is it normal behavior of index?
Previous Message Jean-Yves F. Barbier 2012-01-01 12:44:37 Re: is it normal behavior of index?