[check_postgres] Bloat checking tripped up by data types with = operator

[Mark Kirkwood]

Got caught by this the other day:

test =# \d bug
       Table "public.bug"
  Column |  Type   | Modifiers
--------+---------+-----------
  id     | integer |
  val    | xml     |

running the query from the bloat check by hand gives ( note: I've added 
some columns in):

  schemaname |    tablename     | reltuples | relpages | otta  | mbytes 
| wastedmbytes
------------+------------------+-----------+----------+-------+--------+--------------
  public     | bug              |   1000278 |    48125 |  3917 |    376 
|          345


So the table appears to be mostly wasted space. This is not the case:

test=#  SELECT  dead_tuple_percent, free_percent FROM pgstattuple('bug');
  dead_tuple_percent | free_percent
--------------------+--------------
                   0 |         2.48

So what is going on? This reveals all:

test=# select tablename,attname, null_frac, avg_width from pg_stats 
where tablename like 'bug';
  tablename | attname | null_frac | avg_width
-----------+---------+-----------+-----------
  bug       | id      |         0 |         4


No pg_statistic row for the val attribute - because the xml datatype has 
no "=" operator - yuck! This trips up the estimated best space usage 
calculation for 'otta' in the bloat query, because the val column is 
omitted completely - and of course it is the 'big' column. Not sure how 
we can work around this if at all, but figured it would be useful to 
mention in case anyone else bumps into it.

I might post to pgsql-bugs about this, as there are other potential 
implications for query planning I would think - e.g note the width in 
the plan below (actual width is about 350):

test=# explain select val from bug;
                           QUERY PLAN
--------------------------------------------------------------
  Seq Scan on bug  (cost=0.00..58127.78 rows=1000278 width=32)


regards

Mark
-------------- next part --------------
An HTML attachment was scrubbed...
URL: https://mail.endcrypt.com/pipermail/check_postgres/attachments/20100525/c031eb14/attachment.html