[check_postgres] Bloat checking tripped up by data types with no = operator
Mark Kirkwood
mark.kirkwood at catalyst.net.nz
Thu May 27 00:47:12 UTC 2010
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
More information about the Check_postgres
mailing list