5

When I run a query on my table, I have no clue how long the process will take. I am working with a table with 11 million rows so queries typically run for a few minutes (AWS RDS). Is there a way to get a progress bar or a better sense of how long queries will take? I typically execute a query to 1/10 of the table to get an idea of the time required.

EDIT: I added my specific situation in a notebook

The query I like to run calculates a moving average for several columns. A testing query is copied below (1 column only, only for annual scores, limited output):

SELECT year,month, ptotww_m_30spfaf06, temporal_resolution,
    SUM(ptotww_m_30spfaf06)
        OVER(ORDER BY year ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) as ptotwwma_m_30spfaf06 
FROM y2018m05d29_rh_total_demand_postgis_30spfaf06_v01_v01
WHERE temporal_resolution = 'year'
LIMIT 200

The sample already takes 52s to run and does not work with the monthly values. The monthly 10 year moving average of Jan 1969 should be the average of jan 1960, jan 1961 ... jan 1969.

RutgerH
  • 151
  • 4
  • 2
    You can try at least with EXPLAIN ANALYZE your query so you can understand better why that query take that time to acomplish – Imanol Y. Jun 01 '18 at 09:22
  • 1
    What sort of queries are you running? Please post examples. Do you have any indexes on your table? 11 million rows isn't a particularly large table. – Colin 't Hart Jun 01 '18 at 09:49

1 Answers1

6

No, currently there is no such feature in Postgres.

Some work has been done to prepare for that in future releases. One result of that is the view pg_stat_progress_vacuum introduced in 9.6 which makes use of that new infrastructure.

But currently (2018) nothing of that infrastructure is used for regular SQL queries (including the upcoming Postgres 11).

Postgres Pro Standard (a fork of PostgreSQL) provides the extension pg_query_state which can display the execution plan of a running statement and the information which step is currently executed. But you need to run Postgres Pro, the extension is not compatible with the "normal" PostgreSQL version.

  • This is right, but you can use sequences to monitor the progress for most of your queries: https://dba.stackexchange.com/questions/125084/estimate-end-time-for-a-long-update/218065#218065 – Luciano Andress Martini Oct 01 '18 at 19:39