I have a database on PostgreSQL with some very large tables (with events and event measurements). For a date range on the measurement and one event type I want to compute the average duration with Invantive SQL.
events (simplified, approximately 5 GB):
event_measurements, approximately 50 GB:
When I run a query like
select avg(ended - started)
from events
join event_measurements on events.id = event_measurements.event_id
where events.type_code = '...'
it runs for hours consuming GBs of internal memory.
Necessary indexes are all in place, including foreign key indexes.
From the session I/Os, I've seen that Invantive SQL first downloads the details and performs the grouping on the client. That is fine with small volumes, but in this case I would like to have more native performance like 5 minutes for such a query.
The surrounding queries require Invantive SQL, so "force native SQL" switch on the database is not an option.
How I can improve the performance of this query, without switching to "force native SQL"?
In addition to creating a view as suggested by @GordonLinoff, you can also use:
local log on to switch connection between individual statements, and insert into nativeplatformrequests(payload_text) values ( 'select avg...') to bypass Invantive SQL. It is like EXECUTE IMMEDIATE on Oracle.