Re: Changing the result set to contain the cost of the optimizer's chosen plan

Hello,

I am Srinivas and have been working inside PostgreSQL (mostly in the
optimizer module and few times in the executor module). I would like to
change PostgreSQL code such that it also returns the cost of the optimizer
chosen plan to the Java program (through JDBC) as part of the result set.

Specifically, I have a Java program which calls

ResultSet rs = statement.executeQuery("explain select * from table");

I would like to change PostgreSQL such that ResultSet rs should contain a
field that contains also the cost of the optimizer chosen plan.

If the above is not possible, also please let me know if there is some
other way I could get the cost of the plan in a single call to engine
through Java program.

Regards,
Srinivas Karthik

Srinivas Karthik V <skarthikv(dot)iitb(at)gmail(dot)com> writes:
> Specifically, I have a Java program which calls
> ResultSet rs = statement.executeQuery("explain select * from table");
> I would like to change PostgreSQL such that ResultSet rs should contain a
> field that contains also the cost of the optimizer chosen plan.

Why do you need to change anything? The cost is right there in the
first line of the result text. It might be easier to parse out if
you use one of EXPLAIN's intended-to-be-machine-readable output
formats, though.

regards, tom lane

On 11 July 2016 at 23:29, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> Srinivas Karthik V <skarthikv(dot)iitb(at)gmail(dot)com> writes:
> > Specifically, I have a Java program which calls
> > ResultSet rs = statement.executeQuery("explain select * from table");
> > I would like to change PostgreSQL such that ResultSet rs should contain a
> > field that contains also the cost of the optimizer chosen plan.
>
> Why do you need to change anything? The cost is right there in the
> first line of the result text. It might be easier to parse out if
> you use one of EXPLAIN's intended-to-be-machine-readable output
> formats, though. <http://www.postgresql.org/mailpref/pgsql-hackers>

Yeah - if we were going to do this at all, it'd want to be output that
decomposes _all_ the explain output into columns. But since we can emit
json, xml, etc, I don't really see the point.

--
Craig Ringer http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services

Hi Tom,

Yes, we indeed get the cost of the plan at the first line itself. Somehow,
I missed this point. We just in fact implemented this functionality and its
working. Thanks again.

Regards,
Srinivas Karthik

On Tue, Jul 12, 2016 at 6:43 AM, Craig Ringer <craig(at)2ndquadrant(dot)com> wrote:

> On 11 July 2016 at 23:29, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>
>> Srinivas Karthik V <skarthikv(dot)iitb(at)gmail(dot)com> writes:
>> > Specifically, I have a Java program which calls
>> > ResultSet rs = statement.executeQuery("explain select * from table");
>> > I would like to change PostgreSQL such that ResultSet rs should contain
>> a
>> > field that contains also the cost of the optimizer chosen plan.
>>
>> Why do you need to change anything? The cost is right there in the
>> first line of the result text. It might be easier to parse out if
>> you use one of EXPLAIN's intended-to-be-machine-readable output
>> formats, though. <http://www.postgresql.org/mailpref/pgsql-hackers>
>
>
> Yeah - if we were going to do this at all, it'd want to be output that
> decomposes _all_ the explain output into columns. But since we can emit
> json, xml, etc, I don't really see the point.
>
> --
> Craig Ringer http://www.2ndQuadrant.com/
> PostgreSQL Development, 24x7 Support, Training & Services
>