How to utilize partition pruning with subqueries or joins?

Question

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 ('somenode', 'someothernode')).
When I do EXPLAIN ANALYZE on the third version inclusive of 2 nodes then it no longer is doing (never executed) and is scanning all the partitions.
If I make it node_id = (select node_id from erco.nodes where nodename = 'somenode') or node_id = (select node_id from erco.nodes where nodename = 'someothernode'), partitions are pruned at runtime again.

To summarize

• using a direct node_id=xxxx or node_id in (xxxx,yyyy) will avoid scanning non-relevant partitions

• using a column from a joined table always scans all partitions

• using node_id=(1 node_id subquery) will avoid scanning non-relevant partitions

• using node_id in (multiple node_id subquery) will scan all partitions.

If you're a glutton for verbosity ...
https://textbin.net/dwwgy7rwx4

Edit #2: I removed my existing (scedtime, node_id) pkey and then added a (node_id, scedtime) pkey and I got the same results. I set random_page_cost to 1.1 and tried my queries again but still getting the same result.

Using WHERE node_id IN (subquery), for me, is still scanning every partition. Even if the subquery only has one result, it's still scanning all the partitions. In other words doing...

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

works well but changing it to

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

causes it to scan all the partitions.

I tried doing all the toy example's in Erwin's dbfiddle on my DB and they all behaved as dbfiddle does.

I tried setting random_page_cost to lower and lower values even down to 0.00001 and, still, using IN (subquery) had it scanning all the partitions.

I tried making a new nodes table with just the 2 nodes in question.

I tried to full vacuum analyze the tables thinking maybe it needed new statistics.

I tried recreating the rtprices table, thinking the index needed to be correct when it was created.

None of that made a difference.

Answer 1

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