From: | Nicholas Piper <nick(at)nickpiper(dot)co(dot)uk> |
---|---|
To: | PostgreSQL-general <pgsql-general(at)postgresql(dot)org> |
Subject: | Another seq scan instead of index question |
Date: | 2001-08-07 15:30:07 |
Message-ID: | 20010807163007.K7365@piamox7.haus |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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 !
From | Date | Subject | |
---|---|---|---|
Next Message | Richard Huxton | 2001-08-07 15:32:29 | Re: Transactions and timestamps |
Previous Message | Joe Conway | 2001-08-07 15:27:44 | Re: Still wondering about random numbers... |