How to utilize partition pruning with subqueries or joins?

I have a partition table...

CREATE TABLE erco.rtprices
(
    scedtime timestamp with time zone NOT NULL,
    node_id integer NOT NULL,
    lmp numeric(12,6),
    CONSTRAINT rtprices_pkey PRIMARY KEY (scedtime, node_id)
) PARTITION BY LIST (node_id);

where every node_id has its own partition.

If I do a direct query (first version) such as:

explain select scedtime, lmp 
from erco.rtprices
where node_id = 11111

then the plan does a Seq Scan on only the rtprices_11111 partition. This is what I want.

However, if I do a (second version) query like

explain select scedtime, lmp 
from erco.rtprices
inner join erco.nodes using (node_id)
where nodename = 'somename'

then the plan includes a Seq Scan on every single partition even though this query is just as limiting as the first.

I tried another form (third version) of the above query.

explain select scedtime, lmp 
from erco.rtprices
where node_id = (select node_id from erco.nodes where nodename='somename')

and it still does a Sec Scan on every partition.

If I run the queries (as opposed to just explaining them) then, no surprise, the latter two take about 5x as long as the first (~1 sec vs ~5 sec). ***I made a mistake here, see below.

Is there a syntax where I get the plan/performance of the first version without knowing what the node_id is in advance?

Edit: answering comments:

SHOW enable_partition_pruning;  -- on

Doing EXPLAIN ANALYZE resulted in the first one still only scanning the exact partition. The second one maintained scanning all the partitions. The third one did show (never executed) for all but the one partition in question.

But wait how did that take just as long as the second one if it's not actually executing all those scans? I absent-mindedly changed the query to include two nodes so the wheres became node_id in (11111, 11112), nodename in ('somenode', 'someothernode'), and `node_id in (select node_id from erco.nodes where nodename in

The 5x performance hit for query 3 turned out as mistake. That was the puzzling bit and is resolved.

All the rest makes sense, though it's currently not going your way. The PRIMARY KEY is the culprit:

PRIMARY KEY (scedtime, node_id)

The manual suggests:

Create an index on the key column(s), as well as any other indexes you might want, on the partitioned table. (The key index is not strictly necessary, but in most scenarios it is helpful.)

Bold emphasis mine.

Yours is such a scenario.

Your "key column" is node_id. You should have a B-tree index on (node_id). Or a multicolumn index with node_id as leading index expression. See:

• Is a composite index also good for queries on the first field?

Solution

Replace your PK on (scedtime, node_id) with one on (node_id, scedtime) and you should see partition pruning during execution ("runtime partition pruning") for below cases, too. So we see (never executed) for sub-plans on pruned partitions in EXPLAIN ANALYZE:

• using a column from a joined table

• using a node_id in (multiple node_id subquery)

At least, that's what I see in my tests on Postgres 13 and 14.
Compare these two fiddles:

db<>fiddle here -- with bad PK

db<>fiddle here -- with good PK

Also note how I configured SET random_page_cost = 1.1; to encourage the query planner to actually use the index. The point is keep cost estimate for index scan low, the "good" plans use index scans with node_id as Index Cond, not as Filter. There are various other settings you can tweak to favor indexes. Like effective_cache_size or even cpu_index_tuple_cost

If you don't get such a plan, I would experiment with:

SET enable_seqscan = off;

or similar to force a favorable plan - only for debugging - and then find out why Postgres doesn't expect it to be cheapest. Related:

• postgresql sticking "enable_seqscan=False" in a UDF sensible?

Currently (incl. pg 14), only certain types of query plans can make use of partition pruning. Here is what Amit Langote, one of the architects behind partition pruning, wrote about the feature when it was improved for Postgres 11:

• https://gist.github.com/amitlan/cd13271142bb2d26ae46b69afb675a31

StackExchange Database Administrators Q#305297, answer score: 2