Re: is it normal behavior of index? - Mailing list pgsql-novice

On Sun, 1 Jan 2012 14:11:11 +0100
Andreas Kretschmer <akretschmer@spamfence.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=100000loops=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.