Postgresql – How to get query output in addition to EXPLAIN ANALYZE output

postgresql

Is it possible to get EXPLAIN ANALYZE output (e.g. as a notice) in addition to the regular query output?

I am designing a postgresql client and I would like to add a feature that automatically turns all queries into EXPLAIN ANALYZE for the purpose of tracking query execution time, seq scan, etc. However, such feature would require that I am able to obtain the regular query output in addition to the EXPLAIN ANALYZE output.

How to get query output in addition to EXPLAIN ANALYZE output?

Note: I am aware of auto_explain. However, there appears to be no way to access logs from the client for the last executed statement.

Best Answer

Client can be configured to receive the auto_explain message by setting client_min_messages=log.

Example configuration:

-- Load the extension.
LOAD 'auto_explain';
-- or (if you are using AWS RDS):
LOAD '$libdir/plugins/auto_explain';

-- Enable logging of all queries.
SET auto_explain.log_analyze=true;
SET auto_explain.log_format=json;
SET auto_explain.log_min_duration=0;
SET auto_explain.log_timing=true;

-- Enables Slonik to capture auto_explain logs.
SET client_min_messages=log;

with this configuration, client will receive auto_explain log message after every query.

node.js client Slonik uses this configuration to associate auto_explain messages with the specific queries and parse the log messages.

Related Solutions

PostgreSQL 9.1 – Query on VIEWS taking a lot of time

It helps to format a query properly to see what's going on. I studied your query and found suspicious SQL:


CREATE VIEW "Swiss_client_wise_minutes_and_profit" AS
SELECT ROW_NUMBER() OVER (ORDER BY rp.country, rp.destination) AS id
     , (SELECT company_id
        FROM  "Corporate_companyalias" AS cc
        WHERE  cc."Alias_name" = client_name_id) AS client_name
     , (SELECT company_id
        FROM  "Corporate_companyalias" AS cc
        WHERE  cc."Alias_name" = vendor_name_id) AS vendor_name
     , cs.c_prefix_id AS c_prefix
     , cs.v_prefix_id AS v_prefix
     , rp.country
     , rp.destination
     , cs.c_total_calls
     , cs.v_total_calls
     , cs.successful_calls
     , cs.billed_duration
     , cs.v_billed_amount AS cost
     , cs.c_billed_amount AS revenue
     , cs.c_pdd AS pdd
     , cs.profit
     , cs.start_time
     , cs.end_time
     , cs.switch_name
FROM   "Swiss_buz_scdr" AS cs
LEFT   JOIN "Corporate_companyalias" AS cc ON cs.client_name_id = cc."Alias_name"
LEFT   JOIN "RateManagement_prefix_and_client_wise_destinationgroup" AS rp
         ON rp.client_name = cc.company_id AND rp.prefix = cs.c_prefix_id
WHERE  cs.customer_name = 'SSP Root';

Don't use the same table alias cc in the outer and inner SELECT. While that's not illegal, it helps to confuse you.
Without table qualification for the reference to the outer query I am not sure, where the columns client_name_id and vendor_name_id bind. Would need the table definitions to know, but I suspect it results in CROSS JOINs - which is probably not what you intended and the root of the problem.

I suspect the correlated subqueries can be rewritten as plain expressions. Maybe it needs another JOIN. Here is my ...

Educated guess what you actually want:

CREATE VIEW "Swiss_client_wise_minutes_and_profit" AS
SELECT ROW_NUMBER() OVER (ORDER BY r.country, r.destination) AS id
     , c.company_id AS client_name
     , v.company_id AS vendor_name
     , s.c_prefix_id AS c_prefix
     , s.v_prefix_id AS v_prefix
     , r.country
     , r.destination
     , s.c_total_calls
     , s.v_total_calls
     , s.successful_calls
     , s.billed_duration
     , s.v_billed_amount AS cost
     , s.c_billed_amount AS revenue
     , s.c_pdd AS pdd
     , s.profit
     , s.start_time
     , s.end_time
     , s.switch_name
FROM   "Swiss_buz_scdr" s
LEFT   JOIN "Corporate_companyalias" c ON c."Alias_name" = s.client_name_id
LEFT   JOIN "RateManagement_prefix_and_client_wise_destinationgroup" r
         ON r.client_name = c.company_id AND r.prefix = s.c_prefix_id
LEFT   JOIN "Corporate_companyalias" v ON v."Alias_name" = s.vendor_name_id
WHERE  s.customer_name = 'SSP Root';

Aside: I would aim for shorter names than "RateManagement_prefix_and_client_wise_destinationgroup". And preferably legal, lower-case names that don't need double-quotes.

Postgresql – Why does PostgreSQL aggregate result in the Index or Seq scan outputting all columns of the table

"Seq scans" means full table scan. It's always going to read all of the columns on your table. It's a full table scan. PostgreSQL uses row-based storage, so it can't just read one column at a time.

The reason your query takes so long is that you're reading 7.2 million rows in the table and aggregating them. Apparently, on a fairly slow computer.

Best Answer

Related Solutions

PostgreSQL 9.1 – Query on VIEWS taking a lot of time

Educated guess what you actually want:

Postgresql – Why does PostgreSQL aggregate result in the Index or Seq scan outputting all columns of the table

Related Question