From: | "Jean-Yves F(dot) Barbier" <12ukwn(at)gmail(dot)com> |
---|---|
To: | pgsql-novice(at)postgresql(dot)org |
Subject: | Re: is it normal behavior of index? |
Date: | 2012-01-01 13:35:04 |
Message-ID: | 20120101143504.76ca0b10@anubis.defcon1 |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-novice |
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.
From | Date | Subject | |
---|---|---|---|
Next Message | avalon78 geek | 2012-01-01 14:01:08 | ERROR: invalid input syntax for integer: SQL state: 22P02 |
Previous Message | Andreas Kretschmer | 2012-01-01 13:11:11 | Re: is it normal behavior of index? |