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);
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
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
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.
using a direct
node_id in (xxxx,yyyy)will avoid scanning non-relevant partitions
using a column from a joined table always scans all partitions
node_id=(1 node_id subquery)will avoid scanning non-relevant partitions
node_id in (multiple node_id subquery)will scan all partitions.
If you're a glutton for verbosity ...
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.