Hi Ljeposava,

from the parameters you mention (work_mem, max_parallel_workers_per_gather) I'll assume we are talking PostgreSQL, correct me if it's EDB or something else wearing Postgres clothes.

First, a reframe that will save you time: the knobs you listed are almost never the fix for what you describe. work_mem changes sort and hash behavior, maintenance_work_mem only affects VACUUM and index builds, not SELECT plans at all. If a sequential scan on orders is defeating your partitioning, the real question is why partition pruning did not happen, and that has a short list of causes. I would chase that list before touching any GUC.

Step zero: make sure pruning is actually failing and not just invisible. Partitions pruned at plan time do not appear in EXPLAIN output at all, and partitions pruned at executor startup show up only as a "Subplans Removed" line. The official docs describe both stages here:
https://www.postgresql.org/docs/current/ddl-partitioning.html
So if your plan shows an Append node with a subset of partitions, or a Subplans Removed count, pruning works and the problem is the scan choice inside the surviving partitions, which is a different investigation (row estimates, random_page_cost, parallel seq scan looking cheaper than the index).

If pruning genuinely is not happening, the usual killers, in the order I meet them:

1.⁠ ⁠The WHERE clause does not hit the partition key directly. Any function, expression or cast wrapped around the key column disables pruning: date_trunc(key), key::date, key + interval, all of it. The key must be compared raw, with the arithmetic moved to the other side of the operator.

2.⁠ ⁠Type mismatches between the literal or bind parameter and the key column. Subtle and very common with timestamps versus timestamptz and with drivers that bind text.

3.⁠ ⁠Prepared statements switching to a generic plan after a handful of executions. The generic plan cannot prune at plan time because the parameter is unknown. As a test, set plan_cache_mode = force_custom_plan in a session and see if the plan changes; if it does, look at your driver and pooler behavior. Runtime pruning can still save you here, which is exactly why the Subplans Removed check in step zero matters.

4.⁠ ⁠The partition key value comes from a join rather than a constant. Pruning across joins only happens at execution time for parameterized nested loops; a hash join will happily scan everything.

Then there is the statistics gotcha that bites partitioned tables specifically, and it fits your "recent regression" wording suspiciously well: autovacuum does not process the partitioned parent table at all, so the hierarchy statistics on orders itself only exist if someone runs a manual ANALYZE on it. Official reference:
https://www.postgresql.org/docs/current/sql-analyze.html
Check pg_stat_all_tables.last_analyze for the parent and whether pg_stats has rows for it. And if the regression followed a pg_upgrade or a restore, know that there is a recent thread on the PostgreSQL lists showing that vacuumdb --analyze-only skips partitioned parents, so even a dutiful analyze-in-stages run can leave the parent with zero statistics:
PH0PR04MB8294E796D6297D4E3BE4EFB6C049A@PH0PR04MB8294.namprd04.prod.outlook.com">https://www.postgresql.org/message-id/PH0PR04MB8294E796D6297D4E3BE4EFB6C049A@PH0PR04MB8294.namprd04.prod.outlook.com
A plain ANALYZE orders; costs you minutes and rules this whole family out.

For diagnostics worth collecting: EXPLAIN (ANALYZE, BUFFERS, SETTINGS) of the bad plan, and of the good one if you can still reproduce it. BUFFERS shows the real I/O, SETTINGS prints any non-default planner GUCs so nobody wastes a day on a forgotten session setting. Compare estimated versus actual rows node by node, the spot where they diverge by orders of magnitude is where the story is. In production, auto_explain with a sensible log_min_duration is the tool that catches the regressed plan in the act.

And on your specific knobs: if anything, a generous max_parallel_workers_per_gather can make a parallel seq scan look cheaper than the index path and cause exactly the symptom you see; try max_parallel_workers_per_gather = 0 in one session purely as a diagnostic, never as the fix. On SSD storage, random_page_cost around 1.1 is the honest lever for seq-versus-index decisions, but only after pruning is sorted.

If you post the PostgreSQL version, the partitioning scheme (range on which column, how many partitions), the query with the full EXPLAIN (ANALYZE, BUFFERS) output, and what changed recently (upgrade, bulk load, failover, driver change), happy to dig into the actual plan with you.

Cheers

Roberto