Dear All,
Apologies the way i am asking question as i am more a SQL Server person and a new postgre man..
I have used a query store in SQL server. it provides me option to load statistics data to temp table and get below important information.
1. Last run duration
2. Average time for execution.
3. Filter statistics for a specific function(stored procedure)
4. Filter for specific texts.
5 top queries
6. query plans...
I have used the below query to get this and it lets me see different plans for each proc and even let me force it.
Do we have similar options in postgres? pg_stat_statements extension is the answer ? will it gets cleared over restart? i think its disabled by default in RDS.
DROP table if exists #results;
GO
select
object_name(object_id) as "object name"
, pl.[query_id]
, pl.[plan_id]
, qt.[query_text_id]
, execution_type_desc
, rts.execution_type
, avg_rowcount
, CONVERT(smalldatetime, SWITCHOFFSET(rtsi.[start_time] , DATEPART(tz,SYSDATETIMEOFFSET()))) as "interval_start_time"
, CONVERT(smalldatetime, SWITCHOFFSET(rtsi.[end_time] , DATEPART(tz,SYSDATETIMEOFFSET()))) as "interval_end_time"
, rts.[last_duration]/1000 as "last_duration_ms"
, rts.[min_duration]/1000 as "min_duration_ms"
, rts.[max_duration]/1000 as "max_duration_ms"
, ROUND(rts.[avg_duration]/1000,2) as "avg_duration_ms"
, rts.[count_executions]
, ((rts.[avg_logical_io_reads] + rts.[avg_physical_io_reads])/128)/1024 as "avg_reads_GB"
, ((rts.[avg_logical_io_writes])/1024) as "avg_writes_GB"
, qt.[query_sql_text]
into #results
from
sys.query_store_runtime_stats rts
join
sys.query_store_runtime_stats_interval rtsi
on (rts.[runtime_stats_interval_id] = rtsi.[runtime_stats_interval_id])
join
sys.query_store_plan pl
on (rts.plan_id = pl.plan_id)
join
sys.query_store_query q
on (pl.query_id = q.[query_id])
join
sys.query_store_query_text qt
on (q.query_text_id = qt.query_text_id)
GO
***************************Run the above for fetching data****************************
select top 10 * from #results order by avg_reads_GB desc
select * from #results where query_sql_text like '%text to search%' order by interval_start_time DESC
select * from #results where [object name] = 'Look' order by interval_start_time des
