Re: is it normal behavior of index?

On Sun, 1 Jan 2012 14:11:11 +0100
Andreas Kretschmer <akretschmer(at)spamfence(dot)net> wrote:

>
> 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.

I *need* it to work to present ordered lists!
And I don't agree, essentially because of that:

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_lmetaphone_ix" btree (lower(metaphone(name::text, 16)) varchar_pattern_ops)
"tst1m_name_lu_ix" btree (lower(jyunaccent(name)::text) text_pattern_ops)
"tst1m_name_regular_ix" btree (name varchar_pattern_ops)
"tst1m_note_ix" btree (note varchar_pattern_ops)
Has OIDs: no

EXPLAIN ANALYZE SELECT * FROM tst1m ORDER BY note;
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------
Sort (cost=25402.82..25652.82 rows=100000 width=138) (actual time=9429.292..12468.852 rows=100000 loops=1)
Sort Key: note
Sort Method: external merge Disk: 14576kB
-> Seq Scan on tst1m (cost=0.00..3084.00 rows=100000 width=138) (actual time=0.020..97.160 rows=100000 loops=1)
Total runtime: 12516.256 ms
(5 rows)

DROP INDEX tst1m_note_ix ;

CREATE INDEX tst1m_note_ix ON tst1m(note);

EXPLAIN ANALYZE SELECT * FROM tst1m ORDER BY note;
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------
Index Scan using tst1m_note_ix on tst1m (cost=0.00..15635.95 rows=100000 width=138) (actual time=0.142..372.800 rows=100000 loops=1)
Total runtime: 415.164 ms
(2 rows)

I know the planner's "intelligent", and with 100k rows is is *very*
strange that it don't use index - remember: note strings are [32-64]
length filled with random characters from any CE languages.
Even @ creation, I had a unique index on name ([16-32] chars) and
not any ONE doublon...

What I don't understand is it was working with the former version
which appear to be exactly the same - AND why the varchar.... version
isn't used at all when the regular version is used and brings a 30x
acceleration.

JY
--
Absent, adj.:
Exposed to the attacks of friends and acquaintances;
defamed; slandered.