How should I connect to query_texts in query_store?

Question

I have setpg_qs.query_capture_mode to TOP and pgms_wait_sampling_query_capture_mode to ALL

Now, I was trying
SELECT * FROM azure_sys.query_store.qs_view;

but I get this error

SQL Error [0A000]: ERROR: cross-database references are not implemented: "azure_sys.query_store.qs_view"

Answer 1

Hi @bmarrelli you can try with below query
SELECT runtime_stats_entry_id,
query_id,
substring(query_sql_text, 1, 2000) AS query_text,
start_time,
end_time,
calls,
rows,
round(total_time::numeric, 2) AS total_time,
min_time,
max_time,
round(mean_time::numeric, 2) AS mean,
stddev_time,
round((100 * total_time /
sum(total_time::numeric) OVER ())::numeric, 2) AS percentage_cpu
FROM query_store.qs_view
WHERE start_time >= '2021-02-22 07:00:00+00' and end_time <= '2021-02-22 09:00:00+00'
order by total_time DESC;

make sure you modify start_time/end_time

Please let me know if this helps

Regards
Geetha