Will partitioning be effective in PostgreSQL if the principle of partitioning a table into partitions and the selection criteria do not match?

Question

PostgreSQL

Hello!
When working with a medium-sized table (tens of millions of records), it turned out that some queries take a long time to complete. To speed up the selection, I decided to enable partitioning in this table. But there was a following problem. The fact is that the criterion for selecting records from the table is such that it does not coincide with the principle of splitting the table into sections. Partitioning by primary key was chosen as such a principle, the entire table was divided into sections of 100,000 records each. As far as I understand, partitioning will be useful only if the partitioning principle and selection criterion mentioned are the same. For example, the table is divided into sections, where each section is one day, and in the WHERE clause we have something like: "select from date such and such to date such and such". Then the DBMS is able to determine which physical subtable to access. Otherwise - no, which is what happens in the case I described - after enabling partitioning, the fetch rate did not increase. Therefore, I would like to know if I am right in my assumptions about partitioning and whether it can be made effective in this case. Postgres Pro is used as a DBMS, partitioning is enabled using the pg_pathman plugin.
Thank you!

Answer 1

As far as I understand, partitioning will be useful only if the partitioning principle and selection criterion mentioned are the same.

Absolutely correct understanding.
One of the solutions to such problems is the introduction of redundancy: to speed up the selection, you duplicate information in several tables and partition the table for a specific query.
Or you carry out some additional aggregation or reduction of data and store it in a separate table. Again, on request.
Either you make a partitioning function for several fields, but you need to look at queries. Will only work if query filters are subsets of each other

Answer 2

Have you checked slow queries with explain analyze? Maybe it would be enough just to create the necessary indexes - and queries would be executed much faster? It's just that, as far as I know, partitioning is usually done on much larger volumes of records.