Postgresql – View the final query executed by PostgreSQL Function

I see a couple of issues.

The biggest one is that PG is using a sequence scan on A when filtering A. I think you need a composite index on A.flag AND A.strvalue. If there is already an index available, PostgreSQL is choosing not to use it for some reason. This seems to be eating up 92% of your cost estimate and is likely what's making it run for so long.

As for the ILIKE, PostgreSQL cannot natively (but see below for a module that can) use an index as long as your wildcard is the first character. That's simply a restriction on the ILIKE operator. For that reason you are getting a sequence scan which means every single row is being loaded and the C.name column is being scanned for characters. But one thing that's weird is that the ILIKE sequence scan doesn't seem to be eating up much of the cost estimate in this query plan. Anyway, if it is the ILIKE operator causing the slowdown, I would consider rewriting your query so that it somehow looks like this: ILIKE 'value%' or else consider using PostgreSQL's full text search.

UPDATED

The ILIKE operator can use a trigram index. Superb!

I'm not sure exactly what is causing your "gap", but I think your biggest performance bottleneck is:

Join Filter: ((acs.start_ts < ac.audit_ts) AND (acs.start_ts >= (ac.audit_ts - '1 day'::interval)))

leading to:

Rows Removed by Join Filter: 1953618923

Iterating over 2 billion rows is bound to take some time. That particular join is killing your query because it is joining everything based on:

Hash Cond: (acs.account_id = lobby_user.account_id)

regardless of date range, then excluding everything outside the date range in the join filter.

Since you mentioned that it was faster at some point in the past, it might also be worth checking for bloated indexes, if you haven't already; however I suspect this is mostly a side-effect of ever-increasing amounts of data in account_session.

Also, the fact that your 'distinct' is taking 14M rows down to 35K rows makes me think there might be a better way to get at the information you want (can any of your window functions be replaced by a group by with an aggregate function?).