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

Wed, 27 Feb 2002 20:01:46 -0800

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:
[EMAIL PROTECTED]"> 
"Michael G. Martin" <[EMAIL PROTECTED]> 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

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


{ INDEXSCAN :startup_cost 0.00 :total_cost 1420899.51 :rows 355958 :width 129 
:qptargetlist ({ TARGETENTRY :resdom { RESDOM :resno 1 :restype 1043 :restypmod 14 
:resname symbol_name :reskey 0 :reskeyop 0 :ressortgroupref 0 :resjunk false } :expr { 
VAR :varno 1 :varattno 1 :vartype 1043 :vartypmod 14  :varlevelsup 0 :varnoold 1 
:varoattno 1}} { TARGETENTRY :resdom { RESDOM :resno 2 :restype 1082 :restypmod -1 
:resname date :reskey 0 :reskeyop 0 :ressortgroupref 0 :resjunk false } :expr { VAR 
:varno 1 :varattno 2 :vartype 1082 :vartypmod -1  :varlevelsup 0 :varnoold 1 
:varoattno 2}} { TARGETENTRY :resdom { RESDOM :resno 3 :restype 1700 :restypmod 983047 
:resname open :reskey 0 :reskeyop 0 :ressortgroupref 0 :resjunk false } :expr { VAR 
:varno 1 :varattno 3 :vartype 1700 :vartypmod 983047  :varlevelsup 0 :varnoold 1 
:varoattno 3}} { TARGETENTRY :resdom { RESDOM :resno 4 :restype 1700 :restypmod 983047 
:resname high :reskey 0 :reskeyop 0 :ressortgroupref 0 :resjunk false } :expr { VAR 
:varno 1 :varattno 4 :vartype 1700 :vartypmod 983047  :varlevelsup 0 :varnoold 1 
:varoattno 4}} { TARGETENTRY :resdom { RESDOM :resno 5 :restype 1700 :restypmod 983047 
:resname low :reskey 0 :reskeyop 0 :ressortgroupref 0 :resjunk false } :expr { VAR 
:varno 1 :varattno 5 :vartype 1700 :vartypmod 983047  :varlevelsup 0 :varnoold 1 
:varoattno 5}} { TARGETENTRY :resdom { RESDOM :resno 6 :restype 1700 :restypmod 983047 
:resname d_close :reskey 0 :reskeyop 0 :ressortgroupref 0 :resjunk false } :expr { VAR 
:varno 1 :varattno 6 :vartype 1700 :vartypmod 983047  :varlevelsup 0 :varnoold 1 
:varoattno 6}} { TARGETENTRY :resdom { RESDOM :resno 7 :restype 1700 :restypmod 983044 
:resname volume :reskey 0 :reskeyop 0 :ressortgroupref 0 :resjunk false } :expr { VAR 
:varno 1 :varattno 7 :vartype 1700 :vartypmod 983044  :varlevelsup 0 :varnoold 1 
:varoattno 7}} { TARGETENTRY :resdom { RESDOM :resno 8 :restype 1700 :restypmod 983047 
:resname earnings :reskey 0 :reskeyop 0 :ressortgroupref 0 :resjunk false } :expr { 
VAR :varno 1 :varattno 8 :vartype 1700 :vartypmod 983047  :varlevelsup 0 :varnoold 1 
:varoattno 8}} { TARGETENTRY :resdom { RESDOM :resno 9 :restype 1700 :restypmod 983047 
:resname dividend :reskey 0 :reskeyop 0 :ressortgroupref 0 :resjunk false } :expr { 
VAR :varno 1 :varattno 9 :vartype 1700 :vartypmod 983047  :varlevelsup 0 :varnoold 1 
:varoattno 9}} { TARGETENTRY :resdom { RESDOM :resno 10 :restype 1043 :restypmod 54 
:resname source :reskey 0 :reskeyop 0 :ressortgroupref 0 :resjunk false } :expr { VAR 
:varno 1 :varattno 10 :vartype 1043 :vartypmod 54  :varlevelsup 0 :varnoold 1 
:varoattno 10}} { TARGETENTRY :resdom { RESDOM :resno 11 :restype 1042 :restypmod 7 
:resname daily_mp_12a_long :reskey 0 :reskeyop 0 :ressortgroupref 0 :resjunk false } 
:expr { VAR :varno 1 :varattno 11 :vartype 1042 :vartypmod 7  :varlevelsup 0 :varnoold 
1 :varoattno 11}} { TARGETENTRY :resdom { RESDOM :resno 12 :restype 1042 :restypmod 7 
:resname weekly_mp_12a_long :reskey 0 :reskeyop 0 :ressortgroupref 0 :resjunk false } 
:expr { VAR :varno 1 :varattno 12 :vartype 1042 :vartypmod 7  :varlevelsup 0 :varnoold 
1 :varoattno 12}} { TARGETENTRY :resdom { RESDOM :resno 13 :restype 1042 :restypmod 7 
:resname daily_mp_32a_long :reskey 0 :reskeyop 0 :ressortgroupref 0 :resjunk false } 
:expr { VAR :varno 1 :varattno 13 :vartype 1042 :vartypmod 7  :varlevelsup 0 :varnoold 
1 :varoattno 13}} { TARGETENTRY :resdom { RESDOM :resno 14 :restype 1042 :restypmod 7 
:resname weekly_mp_32a_long :reskey 0 :reskeyop 0 :ressortgroupref 0 :resjunk false } 
:expr { VAR :varno 1 :varattno 14 :vartype 1042 :vartypmod 7  :varlevelsup 0 :varnoold 
1 :varoattno 14}}) :qpqual <> :lefttree <> :righttree <> :extprm () :locprm () 
:initplan <> :nprm 0  :scanrelid 1 :indxid ( 32881560) :indxqual (({ EXPR :typeOid 16  
:opType op :oper { OPER :opno 1062 :opid 1070 :opresulttype 16 } :args ({ VAR :varno 1 
:varattno 1 :vartype 1043 :vartypmod 14  :varlevelsup 0 :varnoold 1 :varoattno 1} { 
CONST :consttype 1043 :constlen -1 :constbyval false :constisnull false :constvalue  8 
[ 8 0 0 0 69 76 84 69 ] })})) :indxqualorig (({ EXPR :typeOid 16  :opType op :oper { 
OPER :opno 1062 :opid 1070 :opresulttype 16 } :args ({ VAR :varno 1 :varattno 1 
:vartype 1043 :vartypmod 14  :varlevelsup 0 :varnoold 1 :varoattno 1} { CONST 
:consttype 1043 :constlen -1 :constbyval false :constisnull false :constvalue  8 [ 8 0 
0 0 69 76 84 69 ] })})) :indxorderdir 1 }
NOTICE:  QUERY PLAN:

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


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

{ SEQSCAN :startup_cost 0.00 :total_cost 707611.68 :rows 355958 :width 129 
:qptargetlist ({ TARGETENTRY :resdom { RESDOM :resno 1 :restype 1043 :restypmod 14 
:resname symbol_name :reskey 0 :reskeyop 0 :ressortgroupref 0 :resjunk false } :expr { 
VAR :varno 1 :varattno 1 :vartype 1043 :vartypmod 14  :varlevelsup 0 :varnoold 1 
:varoattno 1}} { TARGETENTRY :resdom { RESDOM :resno 2 :restype 1082 :restypmod -1 
:resname date :reskey 0 :reskeyop 0 :ressortgroupref 0 :resjunk false } :expr { VAR 
:varno 1 :varattno 2 :vartype 1082 :vartypmod -1  :varlevelsup 0 :varnoold 1 
:varoattno 2}} { TARGETENTRY :resdom { RESDOM :resno 3 :restype 1700 :restypmod 983047 
:resname open :reskey 0 :reskeyop 0 :ressortgroupref 0 :resjunk false } :expr { VAR 
:varno 1 :varattno 3 :vartype 1700 :vartypmod 983047  :varlevelsup 0 :varnoold 1 
:varoattno 3}} { TARGETENTRY :resdom { RESDOM :resno 4 :restype 1700 :restypmod 983047 
:resname high :reskey 0 :reskeyop 0 :ressortgroupref 0 :resjunk false } :expr { VAR 
:varno 1 :varattno 4 :vartype 1700 :vartypmod 983047  :varlevelsup 0 :varnoold 1 
:varoattno 4}} { TARGETENTRY :resdom { RESDOM :resno 5 :restype 1700 :restypmod 983047 
:resname low :reskey 0 :reskeyop 0 :ressortgroupref 0 :resjunk false } :expr { VAR 
:varno 1 :varattno 5 :vartype 1700 :vartypmod 983047  :varlevelsup 0 :varnoold 1 
:varoattno 5}} { TARGETENTRY :resdom { RESDOM :resno 6 :restype 1700 :restypmod 983047 
:resname d_close :reskey 0 :reskeyop 0 :ressortgroupref 0 :resjunk false } :expr { VAR 
:varno 1 :varattno 6 :vartype 1700 :vartypmod 983047  :varlevelsup 0 :varnoold 1 
:varoattno 6}} { TARGETENTRY :resdom { RESDOM :resno 7 :restype 1700 :restypmod 983044 
:resname volume :reskey 0 :reskeyop 0 :ressortgroupref 0 :resjunk false } :expr { VAR 
:varno 1 :varattno 7 :vartype 1700 :vartypmod 983044  :varlevelsup 0 :varnoold 1 
:varoattno 7}} { TARGETENTRY :resdom { RESDOM :resno 8 :restype 1700 :restypmod 983047 
:resname earnings :reskey 0 :reskeyop 0 :ressortgroupref 0 :resjunk false } :expr { 
VAR :varno 1 :varattno 8 :vartype 1700 :vartypmod 983047  :varlevelsup 0 :varnoold 1 
:varoattno 8}} { TARGETENTRY :resdom { RESDOM :resno 9 :restype 1700 :restypmod 983047 
:resname dividend :reskey 0 :reskeyop 0 :ressortgroupref 0 :resjunk false } :expr { 
VAR :varno 1 :varattno 9 :vartype 1700 :vartypmod 983047  :varlevelsup 0 :varnoold 1 
:varoattno 9}} { TARGETENTRY :resdom { RESDOM :resno 10 :restype 1043 :restypmod 54 
:resname source :reskey 0 :reskeyop 0 :ressortgroupref 0 :resjunk false } :expr { VAR 
:varno 1 :varattno 10 :vartype 1043 :vartypmod 54  :varlevelsup 0 :varnoold 1 
:varoattno 10}} { TARGETENTRY :resdom { RESDOM :resno 11 :restype 1042 :restypmod 7 
:resname daily_mp_12a_long :reskey 0 :reskeyop 0 :ressortgroupref 0 :resjunk false } 
:expr { VAR :varno 1 :varattno 11 :vartype 1042 :vartypmod 7  :varlevelsup 0 :varnoold 
1 :varoattno 11}} { TARGETENTRY :resdom { RESDOM :resno 12 :restype 1042 :restypmod 7 
:resname weekly_mp_12a_long :reskey 0 :reskeyop 0 :ressortgroupref 0 :resjunk false } 
:expr { VAR :varno 1 :varattno 12 :vartype 1042 :vartypmod 7  :varlevelsup 0 :varnoold 
1 :varoattno 12}} { TARGETENTRY :resdom { RESDOM :resno 13 :restype 1042 :restypmod 7 
:resname daily_mp_32a_long :reskey 0 :reskeyop 0 :ressortgroupref 0 :resjunk false } 
:expr { VAR :varno 1 :varattno 13 :vartype 1042 :vartypmod 7  :varlevelsup 0 :varnoold 
1 :varoattno 13}} { TARGETENTRY :resdom { RESDOM :resno 14 :restype 1042 :restypmod 7 
:resname weekly_mp_32a_long :reskey 0 :reskeyop 0 :ressortgroupref 0 :resjunk false } 
:expr { VAR :varno 1 :varattno 14 :vartype 1042 :vartypmod 7  :varlevelsup 0 :varnoold 
1 :varoattno 14}}) :qpqual ({ EXPR :typeOid 16  :opType op :oper { OPER :opno 1062 
:opid 1070 :opresulttype 16 } :args ({ VAR :varno 1 :varattno 1 :vartype 1043 
:vartypmod 14  :varlevelsup 0 :varnoold 1 :varoattno 1} { CONST :consttype 1043 
:constlen -1 :constbyval false :constisnull false :constvalue  8 [ 8 0 0 0 69 76 84 69 
] })})

NOTICE:  QUERY PLAN:

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




---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

Reply via email to