Hi everyone,
I've a table:
Table "cdsongs"
Attribute | Type | Modifier
----------------+-------------------+-------------------------------------
songid | integer | default
nextval('seq_songid'::text)
cdid | integer |
track | integer |
song | character varying |
extt | text |
fk_products_id | integer |
Indices: cdsongs_cdid,
cdsongs_songid,
idx_cdsongs_song
wich an index :
CREATE INDEX idx_cdsongs_song ON cdsongs (lower(song));
But postgresql refuses to use it. The vacuum output is :
depos=# VACUUM VERBOSE ANALYZE cdsongs;
NOTICE: --Relation cdsongs--
NOTICE: Pages 41232: Changed 0, reaped 0, Empty 0, New 0; Tup
4210874: Vac 0, Keep/VTL 0/0, Crash 0, UnUsed 0, MinLen 52, MaxLen
2025; Re-using: Free/Avail. Space 0/0; EndEmpty/Avail. Pages 0/0. CPU
0.64s/0.72u sec.
NOTICE: Index cdsongs_cdid: Pages 9223; Tuples 4210874. CPU
0.47s/3.80u sec.
NOTICE: Index cdsongs_songid: Pages 9223; Tuples 4210874. CPU
0.46s/3.68u sec.
NOTICE: Index idx_cdsongs_song: Pages 21888; Tuples 4210874. CPU
1.24s/3.93u sec.
NOTICE: --Relation pg_toast_61094022--
NOTICE: Pages 5: Changed 0, reaped 0, Empty 0, New 0; Tup 28: Vac 0,
Keep/VTL 0/0, Crash 0, UnUsed 0, MinLen 63, MaxLen 2034; Re-using:
Free/Avail. Space 0/0; EndEmpty/Avail. Pages 0/0. CPU 0.00s/0.00u sec.
NOTICE: Index pg_toast_61094022_idx: Pages 2; Tuples 28. CPU
0.00s/0.00u sec.
NOTICE: Analyzing...
There are 4210874 rows, which is a lot compared to the expected rows
returned, so why does it still use seq scan ? (I'd expect seq scan if
it was returning loads of rows!).
depos=# explain select * from cdsongs where lower(song) like
'mushroom festival in hell';
NOTICE: QUERY PLAN:
Seq Scan on cdsongs (cost=0.00..104395.11 rows=42109 width=40)
EXPLAIN
depos=# set enable_seqscan = off;
SET VARIABLE
depos=# explain select * from cdsongs where lower(song) like
'mushroom festival in hell';
NOTICE: QUERY PLAN:
Index Scan using idx_cdsongs_song on cdsongs (cost=0.00..115549.17
rows=42109 width=40)
EXPLAIN
And indead, if I force seqscan off and perform the query, it's dead
fast. Otherwise it uses seqscan and takes a long long time.
Cheers for any help!
Nick
--
Part 3 MEng Cybernetics; Reading, UK http://www.nickpiper.co.uk/
Change PGP actions of mailer or fetch key see website 1024D/3ED8B27F
Choose life. Be Vegan :-) Please reduce needless cruelty + suffering !
