Cannot get to use index scan on a big table!
От | Rajesh Kumar Mallah |
---|---|
Тема | Cannot get to use index scan on a big table! |
Дата | |
Msg-id | 3CC511E1.F19105F6@trade-india.com обсуждение исходный текст |
Ответы |
Re: Cannot get to use index scan on a big table!
(Stephan Szabo <sszabo@megazone23.bigpanda.com>)
|
Список | pgsql-sql |
<tt>Hi Folks,</tt><tt></tt><p><tt>i guess by now it is the most freq. asked question on list ;-)</tt><tt></tt><p><tt>i havea simple SQL query and it does not seems to use index</tt><br /><tt>despite its existance and VCUUMING of table.</tt><tt></tt><p><tt><fontcolor="#000099">tradein_clients=> explain select email_id from email_source where source_id=186;</font></tt><br /><tt><font color="#000099">NOTICE: QUERY PLAN:</font></tt><tt><font color="#000099"></font></tt><p><tt><fontcolor="#000099">Seq Scan on email_source (cost=0.00..19191.50 rows=</font><b><fontcolor="#FF0000">41602</font></b><font color="#000099"> width=4)</font></tt><tt><font color="#000099"></font></tt><p><tt><fontcolor="#000099">EXPLAIN</font></tt><br /><tt><font color="#000099">tradein_clients=></font></tt><tt></tt><p><tt>(cananyone please explain why the figure 41602??)</tt><br/><tt></tt> <tt></tt><p><tt>i am using postgresql 7.1.2 (ples. do not curse me for not upg.)</tt><br /><tt>ihave done "VACUUM ANALYZE" on the table in question</tt><tt></tt><p><tt>the table has ~ 1.1 million records and seqscan is</tt><br /><tt>killing my apps</tt><tt></tt><p><tt>regds</tt><br /><tt>mallah.</tt><br /><tt></tt> <br /><tt></tt> <tt></tt><p><b><tt>Somemore info below:</tt></b><tt></tt><p><tt>tradein_clients=> VACUUM VERBOSE ANALYZE email_source;</tt><br /><tt>NOTICE: --Relation email_source--</tt><br /><tt>NOTICE: Pages 5794: Changed 1, reaped 30, Empty0, New 0; Tup 1071800: Vac 81, Keep/VTL 9/0, Crash 0, UnUsed 9, MinLen 40, MaxLen 40; Re-using: Free/Avail. Space 4560/2208;EndEmpty/Avail. Pages 0/29. CPU 0.30s/0.12u sec.</tt><br /><tt>NOTICE: Index email_source_email_id: Pages 2350;Tuples 1071800: Deleted 0. CPU 0.16s/1.08u sec.</tt><br /><tt>NOTICE: Index email_source_source_id: Pages 2350; Tuples1071800: Deleted 0. CPU 0.14s/1.02u sec.</tt><br /><tt>NOTICE: Rel email_source: Pages: 5794 --> 5794; Tuple(s)moved: 32. CPU 0.01s/0.01u sec.</tt><br /><tt>NOTICE: Index email_source_email_id: Pages 2350; Tuples 1071800: Deleted32. CPU 0.15s/0.84u sec.</tt><br /><tt>NOTICE: Index email_source_source_id: Pages 2350; Tuples 1071800: Deleted32. CPU 0.11s/0.79u sec.</tt><br /><tt>NOTICE: Analyzing...</tt><br /><tt>VACUUM</tt><br /><tt></tt> <tt></tt><p><b><tt>tablestructures:</tt></b><tt></tt><p><tt>tradein_clients=> \d email_source</tt><br /><tt> Table "email_source"</tt><br /><tt> Column | Type | Modifiers</tt><br /><tt>-----------+---------+-----------</tt><br/><tt> email_id | integer |</tt><br /><tt> source_id | integer |</tt><br/><tt>Indexes: email_source_email_id,</tt><br /><tt> email_source_source_id</tt>
В списке pgsql-sql по дате отправления: