Postgres Pro
Downloads
Home   >   mailing lists

Thread: Range types do not display in pg_stats

Range types do not display in pg_stats

From
Josh Berkus
Date:
create table tstztest( trange tstzrange );

postgres=# insert into tstztest select tstzrange(t, t + interval '1 month')
from generate_series('2012-01-01'::timestamptz,'2018-01-01','1 month')
as gs(t);
INSERT 0 73
postgres=# analyze tstztest;
ANALYZE
postgres=# select * from pg_stats where tablename = 'tstztest';schemaname | tablename | attname | inherited | null_frac
|avg_width |
 
n_distinct | most_common_vals | most_common_freqs | histogram_bounds
| correlation | most_common_elems | most_common_elem_freqs |
elem_count_histogram

------------+-----------+---------+-----------+-----------+-----------+------------+------------------+-------------------+------------------
+-------------+-------------------+------------------------+----------------------public     | tstztest  | trange  | f
      |         0 |        22 |       -1 |                  |                   |
 
|             |                   |                        |

Now, there actually *is* a histogram for the column, which you can find
via pg_statistic.  But is shows up as NULL in pg_stats view.

If this is a known issue, we ought to at least add it to the docs.

-- 
Josh Berkus
PostgreSQL Experts Inc.
http://pgexperts.com

Re: Range types do not display in pg_stats

From
Robert Haas
Date:
On Thu, Sep 19, 2013 at 4:54 PM, Josh Berkus <josh@agliodbs.com> wrote:
>
> create table tstztest( trange tstzrange );
>
> postgres=# insert into tstztest select tstzrange(t, t + interval '1 month')
> from generate_series('2012-01-01'::timestamptz,'2018-01-01','1 month')
> as gs(t);
> INSERT 0 73
> postgres=# analyze tstztest;
> ANALYZE
> postgres=# select * from pg_stats where tablename = 'tstztest';
>  schemaname | tablename | attname | inherited | null_frac | avg_width |
> n_distinct | most_common_vals | most_common_freqs | histogram_bounds
> | correlation | most_common_elems | most_common_elem_freqs |
> elem_count_histogram
>
------------+-----------+---------+-----------+-----------+-----------+------------+------------------+-------------------+------------------
> +-------------+-------------------+------------------------+----------------------
>  public     | tstztest  | trange  | f         |         0 |        22 |
>         -1 |                  |                   |
> |             |                   |                        |
>
> Now, there actually *is* a histogram for the column, which you can find
> via pg_statistic.  But is shows up as NULL in pg_stats view.
>
> If this is a known issue, we ought to at least add it to the docs.

It probably has to do with the CASE stakind stuff in the definition of
the pg_stats view.  Try \d+ pg_stats to see what I mean.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

Re: Range types do not display in pg_stats

From
Josh Berkus
Date:
On 09/19/2013 02:55 PM, Mike Blackwell wrote:
> Interesting.  Is this a 9.3 issue?  I ran the above against my 9.2.4 server
> and got no rows in pg_stats.  Did I miss something?

Yeah, that was on 9.3.  I think the issue on 9.2 is the same, it just
expresses differently.

-- 
Josh Berkus
PostgreSQL Experts Inc.
http://pgexperts.com

Re: Range types do not display in pg_stats

From
Josh Berkus
Date:
Robert,

> It probably has to do with the CASE stakind stuff in the definition of
> the pg_stats view.  Try \d+ pg_stats to see what I mean.

Ok, if this is not a known bug, I'll see if I can work up a fix.  No
promises, given the hairyness ...

-- 
Josh Berkus
PostgreSQL Experts Inc.
http://pgexperts.com