Re: Are statistics gathered on function indexes? - Mailing list pgsql-admin

> Ray Ontko <rayo@ontko.com> writes:
> >> It appears that "vacuum analyze verbose actor" causes the problem.
> >> It appears that I have to say "vacuum analyze actor" in order to
> >> clear out the ill effects of having said "vacuum analyze verbose actor".
>
> I really, really doubt that "verbose" has anything to do with it.
>
> What do you get from
>     select * from pg_stats where tablename = 'actor' and
>         attname = 'actor_full_name';
>
> Do the results change significantly between the "good" state and the
> "bad" state?  How about the results of
>     select relpages, reltuples from pg_class where relname = 'actor';
>
> It would seem that one or another of these statistical items is getting
> set weirdly by something you are doing, but I have no idea what exactly
> is going wrong...

Hmm.

1) here's the "bad" stats.
2) here's the "good" stats.

Note that the information really is different.

3) here's the results of the relpages,reltuples query.

Same whether good or bad stats.

Ray

**********
1) here's the "bad" stats.
**********

develop=# explain select * from actor where actor_full_name like 'WI%' ;
NOTICE:  QUERY PLAN:

Index Scan using actor_full_name on actor  (cost=0.00..6.01 rows=1 width=570)

EXPLAIN
develop=# select * from pg_stats where tablename = 'actor' and
develop-#                 attname = 'actor_full_name';
 tablename |     attname     |  null_frac  | avg_width | n_distinct |

                        most_common_vals
                                                                           |
                                    most_common_freqs
             |
                                          histogram_bounds

      | correlation
-----------+-----------------+-------------+-----------+------------+-----------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
---------------------------------------------------------------------------+----
--------------------------------------------------------------------------------
-------------+------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
------+-------------
 actor     | actor_full_name | 0.000333333 |        22 |      14657 | {"INDIANA
DEPARTMENT OF REVENUE","AEGIS WOMENS HEALTHCARE","BLOOMINGTON HOSPITAL","MONROE
COUNTY TREASURER","PEOPLES STATE BANK","RICHLAND BEAN BLOSSOM CSC","SMITHVILLE T
ELEPHONE","STATE OF INDIANA","PETTAY, LEE","WOODINGTON COURTS MANAGEMENT"} | {0.
0813333,0.00366667,0.003,0.00266667,0.00266667,0.00266667,0.00233333,0.00233333,
0.002,0.002} | {"(ABEL) CONDER, CRYSTAL","BLOOMINGTON HOUSING AUTHORITY","CORBIN
, MARK J","FLEETWOOD, JAMES WILBUR","HAZEL, JEFF W","KIDD, PATTY","MEADOW PARK A
PARTMENTS","PETERSON, CATHY L","SHADLE, MARY","THRASHER, CHRISTOPHER B","ZYNNCO
LLC"} |    0.025242
(1 row)


**********
2)
**********

develop=# explain select * from actor where actor_full_name like 'WI%' ;
NOTICE:  QUERY PLAN:

Index Scan using actor_full_name on actor  (cost=0.00..6.01 rows=1 width=571)

EXPLAIN
develop=# analyze actor ;
ANALYZE
develop=# explain select * from actor where actor_full_name like 'WI%' ;
NOTICE:  QUERY PLAN:

Index Scan using actor_full_name on actor  (cost=0.00..433.52 rows=108 width=571
)

EXPLAIN
develop=# select * from pg_stats where tablename = 'actor' and
develop-#                 attname = 'actor_full_name';
 tablename |     attname     | null_frac | avg_width | n_distinct |

                 most_common_vals
                                                                |
                         most_common_freqs
  |
                               histogram_bounds
                                                                           | cor
relation
-----------+-----------------+-----------+-----------+------------+-------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
----------------------------------------------------------------+---------------
--------------------------------------------------------------------------------
--+-----------------------------------------------------------------------------
--------------------------------------------------------------------------------
---------------------------------------------------------------------------+----
---------
 actor     | actor_full_name |         0 |        22 |      14541 | {"INDIANA DE
PARTMENT OF REVENUE","RICHLAND BEAN BLOSSOM CSC","PETTAY, LEE","STATE OF INDIANA
","BAKER DDS, DONALD","BLOOMINGTON HOSPITAL","SMITHVILLE TELEPHONE","AEGIS WOMEN
S HEALTHCARE","BAKER DDS, LISA","BLOOMINGTON ACCOUNTS SERVICE"} | {0.0856667,0.0
0333333,0.00233333,0.00233333,0.002,0.002,0.002,0.00166667,0.00166667,0.00166667
} | {"(FITZPATRICK) STOUT, LISA","BLOOMINGTON HOUSING AUTHORITY","CONKLIN, TONIA
 A","EWING, CRAIG","HARTENFELD, KATHLEEN A","KELLEY, KIMBERLEY","MDF BUILDERS","
PENNINGTON, ADA M","SCISCOE, R L ETAL","THOMPSON, JEANA J","ZOOK, ALISON"} |   0
.0127368
(1 row)

**********
3) results of the replage,reltuples query
**********

develop=#       select relpages, reltuples from pg_class where relname = 'actor'
;
 relpages | reltuples
----------+-----------
     7106 |    436871
(1 row)


------------------------------------------------------------------------
Ray Ontko   rayo@ontko.com   Phone 1.765.935.4283   Fax 1.765.962.9788
Ray Ontko & Co.   Software Consulting Services   http://www.ontko.com/