Tune slow grouping functions Invantive SQL

Question

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):

• id
• type_code

event_measurements, approximately 50 GB:

• event_id
• started
• ended

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"?

Answer 1

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.