Skip site navigation (1) Skip section navigation (2)

Re: Indexes not always used after inserts/updates/vacuum analyze

From: "Michael G(dot) Martin" <michael(at)vpmonline(dot)com>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: pgsql-bugs(at)postgresql(dot)org
Subject: Re: Indexes not always used after inserts/updates/vacuum analyze
Date: 2002-02-28 04:15:17
Message-ID: 3C7DAED5.7020108@vpmonline.com (view raw or flat)
Thread:
Lists: pgsql-bugs
Hi Tom,

Here is what is actually there:

 select count(*) from symbol_data where symbol_name='ELTE';
 count
-------
   687

Here is the pg_stat query:


 select * from pg_stats where tablename = 'symbol_data' and attname 
='symbol_name';
  tablename  |   attname   | null_frac | avg_width | n_distinct 
|               most_common_vals               
|                                               
most_common_freqs                                               
|                  histogram_bounds                   | correlation
-------------+-------------+-----------+-----------+------------+----------------------------------------------+---------------------------------------------------------------------------------------------------------------+-----------------------------------------------------+-------------
 symbol_data | symbol_name |         0 |         7 |     152988 | 
{EBALX,ELTE,LIT,OEX,RESC,BS,ESH,HOC,IBC,IDA} | 
{0.0183333,0.0173333,0.00166667,0.00166667,0.00166667,0.00133333,0.00133333,0.00133333,0.00133333,0.00133333} 
| {A,BMO,DBD,FSCHX,IIX,MAS,NSANY,PTEC,SR,UTIL,_^^VPM} |    0.128921
(1 row)


Interesting eh? EBALX also does a full scan--all others in the above 
list get an index scan.

Here's the  variable stuff--I attached the verbose outputs.
 set enable_seqscan = on;
SET VARIABLE
vpm=> explain select * from symbol_data where symbol_name='ELTE';
NOTICE:  QUERY PLAN:

Seq Scan on symbol_data  (cost=0.00..707611.68 rows=355958 width=129)

 set enable_seqscan = off;
SET VARIABLE
vpm=> explain select * from symbol_data where symbol_name='ELTE';
NOTICE:  QUERY PLAN:

Index Scan using symbol_data_pkey on symbol_data  (cost=0.00..1420899.51 
rows=355958 width=129)

Index scan appears slower in explain, but the rows value is weird.

Thanks,
Michael


Tom Lane wrote:

>"Michael G. Martin" <michael(at)vpmonline(dot)com> writes:
>
>>Here is what I would expect which usually happens:
>>
>
>>explain select * from symbol_data where symbol_name='IBM';
>>Index Scan using symbol_data_pkey on symbol_data  (cost=0.00..512.99 rows=128 width=129)
>>
>
>>Here is one that fails:
>>
>
>>explain select * from symbol_data where symbol_name='ELTE';
>>Seq Scan on symbol_data  (cost=0.00..707415.32 rows=438015 width=129)
>>
>
>The planner thinks that there are 438K occurrences of 'ELTE' in your
>table.  If that's true, a sequential scan is not obviously the wrong
>choice.  How many are there, in reality?  What do you get from
>
>select * from pg_stats where tablename = 'symbol_data' and attname =
>'symbol_name';
>
>Also, to put the rubber to the road: if you force an indexscan by
>doing "set enable_seqscan = off", does it get faster or slower?
>(EXPLAIN ANALYZE would be useful here.)
>
>			regards, tom lane
>


Attachment: verbose_seqscan_on
Description: text/plain (4.1 KB)
Attachment: verbose_seqscan_off
Description: text/plain (4.6 KB)

In response to

Responses

pgsql-bugs by date

Next:From: Tom LaneDate: 2002-02-28 04:28:19
Subject: Re: Indexes not always used after inserts/updates/vacuum analyze
Previous:From: Tom LaneDate: 2002-02-28 03:57:10
Subject: Re: Indexes not always used after inserts/updates/vacuum analyze

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group