0

I have table partitioned by range of dates (monthly).

Having a list of dates, I want to select all the partitions, where values from the list of dates are present. That is to avoid scanning the partitions.

What would be the query to do that?

Say, I have:

partitions: calendars_y2021_m01, calendars_y2021_m02, calendars_y2021_m03
list of dates: 2021-01-01, 2021-02-01, 2021-02-02

I want to:

select partitions from calendars
where dates in (2021-01-01, 2021-02-01, 2021-02-02)

That should give me all the rows from partitions: calendars_y2021_m01, calendars_y2021_m02

The only solution I got myself is to identify the target partitions and fetch them one-by-one with python loop, but that is slow.

I am using RANGE partitioning. Selecting from calendars will scan the partitions for the specific dates. I just want to get all the data from the selected partitions into memory to avoid that.

If I select * from calendars where dates in (list of dates) PostgreSQL will first (1) define target partitions, then (2) SeqScan the partitions for the values in list of dates. I want to skip the second operation and get the entire partitions.

Once I have the partitions, I will process them in memory with python pandas. That is way faster, then seq scan. Also, I am just biased towards pythonic tools.

1
  • At least with Postgres 13 (and I think with 12 as well), the optimizer is smart enough to eliminate the unnecessary partitions. With a test table containing 15 million rows and monthly partitions from 2000 to 2022, I get this execution plan with a query using three different date values for the IN clause matching two months. When the values hit three months I get this execution plan. So if you are using a recent version, your "optimization" is not needed
    – user1822
    Commented Oct 23, 2020 at 9:49

1 Answer 1

1
SELECT DISTINCT
       format(
          'calendars_y%s_m%s',
          lpad(CAST(extract (year FROM q) AS text), 4, '0'),
          lpad(CAST(extract (month FROM q) AS text), 2, '0'))
FROM (VALUES (DATE '2021-01-01'),
             (DATE '2021-02-01'),
             (DATE '2021-02-02')) AS q(q);

       format        
---------------------
 calendars_y2021_m01
 calendars_y2021_m02
(2 rows)

But I seriously doubt that it is a good idea to do that on foot. You will be better off letting the PostgreSQL optimizer figure out which partitions to scan.

2
  • this felp figure out what partitions contain the values, right? But how to fetch this multiple partitions at once? Commented Oct 23, 2020 at 9:29
  • You run SELECT on them. That will result in two sequential scans. You can run them concurrently if you have two database connections. There is no other way to get the data from a database than to SELECT them. You will end up reading way more data than you have to. It will be much more efficient just to run a simple select on the partitioned table. Commented Oct 23, 2020 at 10:06

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.