Thread: pg_stat_all_tables vs NULLs

I've noticed that pg_stat_all_tables returns NULL for idx_scan and
idx_tup_fetch if there are no indexes present on a table.

Is this actually intended, or is that something that should be fixed?

//Magnus

Magnus Hagander <magnus@hagander.net> writes:
> I've noticed that pg_stat_all_tables returns NULL for idx_scan and
> idx_tup_fetch if there are no indexes present on a table.

> Is this actually intended, or is that something that should be fixed?

Hmm.  I suspect it's an implementation artifact rather than something
that was consciously chosen, but on reflection it doesn't seem like a
bad thing.  If we just COALESCE'd it to zero (which I assume is what
you have in mind) then there would be no distinction in the view
between "you have no indexes" and "there are indexes but they aren't
being used".

I'd vote to leave it alone, I think.
        regards, tom lane

Tom Lane wrote:
> Magnus Hagander <magnus@hagander.net> writes:
>> I've noticed that pg_stat_all_tables returns NULL for idx_scan and
>> idx_tup_fetch if there are no indexes present on a table.
> 
>> Is this actually intended, or is that something that should be fixed?
> 
> Hmm.  I suspect it's an implementation artifact rather than something
> that was consciously chosen, but on reflection it doesn't seem like a
> bad thing.  If we just COALESCE'd it to zero (which I assume is what
> you have in mind) then there would be no distinction in the view
> between "you have no indexes" and "there are indexes but they aren't
> being used".

But does it make sense to look for that information in pg_stat_*_tables,
really? If you want to know if an index exists for a table, you'd
normally go look in the system tables, not the statistics views, I think.


> I'd vote to leave it alone, I think.

I can go for that as well though. I'd say "Let's document it instead
then", but it seems the stats views documentation is very short on what
actually goes in the fields. But I guess we could just add a "(NULL if
no indexes are present)" to that?

In the long term it might be worthwhile to rewrite that section of the
docs to focus more on the stats views (giving each it's own section with
more information bout it than just a list of fields) and less on the
underlying implementation functions. But that's a different day ;-)


//Magnus

Magnus Hagander wrote:
> Tom Lane wrote:
> > Magnus Hagander <magnus@hagander.net> writes:
> >> I've noticed that pg_stat_all_tables returns NULL for idx_scan and
> >> idx_tup_fetch if there are no indexes present on a table.
> > 
> >> Is this actually intended, or is that something that should be fixed?
> > 
> > Hmm.  I suspect it's an implementation artifact rather than something
> > that was consciously chosen, but on reflection it doesn't seem like a
> > bad thing.  If we just COALESCE'd it to zero (which I assume is what
> > you have in mind) then there would be no distinction in the view
> > between "you have no indexes" and "there are indexes but they aren't
> > being used".
> 
> But does it make sense to look for that information in pg_stat_*_tables,
> really? If you want to know if an index exists for a table, you'd
> normally go look in the system tables, not the statistics views, I think.
> 
> 
> > I'd vote to leave it alone, I think.
> 
> I can go for that as well though. I'd say "Let's document it instead
> then", but it seems the stats views documentation is very short on what
> actually goes in the fields. But I guess we could just add a "(NULL if
> no indexes are present)" to that?
> 
> In the long term it might be worthwhile to rewrite that section of the
> docs to focus more on the stats views (giving each it's own section with
> more information bout it than just a list of fields) and less on the
> underlying implementation functions. But that's a different day ;-)

I don't see any clean place to put this information in our
documentation, and since this is the first report of confusion, I don't
think we can easily document this.

--  Bruce Momjian  <bruce@momjian.us>        http://momjian.us EnterpriseDB
http://enterprisedb.com
 + If your life is a hard drive, Christ can be your backup. +