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

[Mark Kirkwood]

On 25/05/10 15:32, Mark Kirkwood wrote:
> 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.

Adding a minimal = operator (see attached) appears to solve the issue:

  schemaname |    tablename     | reltuples | relpages |  otta  | mbytes 
| wastedmbytes
------------+------------------+-----------+----------+--------+--------+--------------
  public     | bug              |   1000278 |    48125 |  46717 |    376 
|          11

The stats query is now:

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
  bug       | val     |         0 |       385

However this potentially messing with the xml datatype (it is not 
*supposed* to have an = operator it seems), so probably not for 
production use.

regards

Mark
-------------- next part --------------
An HTML attachment was scrubbed...
URL: https://mail.endcrypt.com/pipermail/check_postgres/attachments/20100527/525e75b5/attachment-0001.html 
-------------- next part --------------
A non-text attachment was scrubbed...
Name: xmleq.sql
Type: text/x-sql
Size: 409 bytes
Desc: not available
Url : https://mail.endcrypt.com/pipermail/check_postgres/attachments/20100527/525e75b5/attachment-0001.bin