Обсуждение: Expose options to explain? (track_io_timing)
Salut!
Fellow volunteers, I request assistance in understanding the following:
When I explain a query I can get the following information:
| "I/O Read Time": 0.000, | "I/O Write Time": 0.000
I know why it is 0. My question is this, can we expose it to explain
only? Specifically "explain (analyze,buffers)". Is there a technical
reason we must turn on track_io_timing for the whole system? If there
isn't, is this something the community would want?
JD
--
Command Prompt, Inc. - http://www.commandprompt.com/ 503-667-4564
PostgreSQL Support, Training, Professional Services and Development
High Availability, Oracle Conversion, @cmdpromptinc
"If we send our children to Caesar for their education, we should not be surprised when they come back as
Romans."
On Thu, Oct 9, 2014 at 10:17 AM, Joshua D. Drake <jd@commandprompt.com> wrote:
Salut!
Fellow volunteers, I request assistance in understanding the following:
When I explain a query I can get the following information:
| "I/O Read Time": 0.000,
| "I/O Write Time": 0.000
I know why it is 0. My question is this, can we expose it to explain only? Specifically "explain (analyze,buffers)". Is there a technical reason we must turn on track_io_timing for the whole system? If there isn't, is this something the community would want?
I think the theory for track_io_timing being PGC_SUSET is that if the superuser turned it on, no one should be able to turn it off.
But I don't see an argument for the other way around, that no one should be able to turn it on locally of the superuser left it at the default of off.
So I think the real behavior we would want is that anyone can turn it on in their session, and can also turn it off provided it was turned on by them in the first place. But there is no machinery in the GUC code to do that, which is probably why it wasn't done. I meant to work on that for this dev cycle, but I never dug into how to implement the "provided it was turned on by them in the first place" part of the requirement. And how would this be expressed generically? Some notion that the default value can be a floor or ceiling which the user can alter in one direction, and reverse that alteration. PGC_SUSET_FLOOR and PGC_SUSET_CEILING?
Anyway, if we are going to solve this for track_io_timing, I think it would be better so solve it in a way that it can also be used by pg_stat_statements, as well as EXPLAIN. But maybe that doesn't make sense, as you need to be a superuser to call pg_stat_statements_reset() anyway.
Cheers,
Jeff
On Thu, Oct 9, 2014 at 2:41 PM, Jeff Janes <jeff.janes@gmail.com> wrote: > I think the theory for track_io_timing being PGC_SUSET is that if the > superuser turned it on, no one should be able to turn it off. > > But I don't see an argument for the other way around, that no one should be > able to turn it on locally of the superuser left it at the default of off. > > So I think the real behavior we would want is that anyone can turn it on in > their session, and can also turn it off provided it was turned on by them in > the first place. But there is no machinery in the GUC code to do that, > which is probably why it wasn't done. I meant to work on that for this dev > cycle, but I never dug into how to implement the "provided it was turned on > by them in the first place" part of the requirement. And how would this be > expressed generically? Some notion that the default value can be a floor or > ceiling which the user can alter in one direction, and reverse that > alteration. PGC_SUSET_FLOOR and PGC_SUSET_CEILING? Hmm. IIRC, there are only two use cases for I/O timing at present: pg_stat_statements (which really only makes sense if it's turned on or off system-wide) and EXPLAIN. Rather than inventing more GUC machinery, I think we could just add an explain flag called "IO". So you could do: EXPLAIN (ANALYZE, IO) SELECT .... And that would gather I/O stats even if it's turned off system-wide. Or you could do: EXPLAIN (ANALYZE, IO false) SELECT .... That can't really be allowed to suppress gathering the I/O stats for this query if the sysadmin wants those stats for all queries. But it could suppress the print-out. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company
On 10/14/2014 10:01 AM, Robert Haas wrote: > Hmm. IIRC, there are only two use cases for I/O timing at present: > pg_stat_statements (which really only makes sense if it's turned on or > off system-wide) and EXPLAIN. Rather than inventing more GUC > machinery, I think we could just add an explain flag called "IO". So > you could do: > > EXPLAIN (ANALYZE, IO) SELECT .... > > And that would gather I/O stats even if it's turned off system-wide. > Or you could do: > > EXPLAIN (ANALYZE, IO false) SELECT .... > > That can't really be allowed to suppress gathering the I/O stats for > this query if the sysadmin wants those stats for all queries. But it > could suppress the print-out. I think the first one makes the most sense. JD -- Command Prompt, Inc. - http://www.commandprompt.com/ 503-667-4564 PostgreSQL Support, Training, Professional Services and Development High Availability, Oracle Conversion, @cmdpromptinc "If we send our children to Caesar for their education, we should not be surprised when they come back as Romans."
On Tue, Oct 14, 2014 at 1:04 PM, Joshua D. Drake <jd@commandprompt.com> wrote: > On 10/14/2014 10:01 AM, Robert Haas wrote: >> Hmm. IIRC, there are only two use cases for I/O timing at present: >> pg_stat_statements (which really only makes sense if it's turned on or >> off system-wide) and EXPLAIN. Rather than inventing more GUC >> machinery, I think we could just add an explain flag called "IO". So >> you could do: >> >> EXPLAIN (ANALYZE, IO) SELECT .... >> >> And that would gather I/O stats even if it's turned off system-wide. >> Or you could do: >> >> EXPLAIN (ANALYZE, IO false) SELECT .... >> >> That can't really be allowed to suppress gathering the I/O stats for >> this query if the sysadmin wants those stats for all queries. But it >> could suppress the print-out. > > I think the first one makes the most sense. It would be both or neither, not one or the other. All EXPLAIN options take true/false arguments; but "true" can be omitted for brevity. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company