Let's say you have one partitioned table, "tbl_p", partitioned according to
the PK "p_pk". I have made something similar with triggers, basing myself on
the manual for making partitioned tables.
According to the manual, optimizer searches the CHECKs of the partitions to
determine which table(s) to use (if applicable).
So if one has CHECKs of kind "p_pk = some number", queries like "SELECT *
from tbl_p where p_pk = 1" will only be searched in the appropriate table.
One can check this with EXPLAIN. So far so good.
Now, if one takes a subquery for "1", the optimizer evaluates it first
(let's say to "1"), but then searches for it (sequentially) in every
partition, which, for large partitions, can be very time-consuming and goes
beyond the point of partitioning.
Is this normal, or am I missing something?
Kind regards,
Davor