Howdy,
I spent a majority of today playing around with pg_partman (awesome tool
btw!). I am mainly using the time-static method with an interval of one
month.
I wanted to see what performance improvements I could get with some
common queries that are used by our analytics team. A lot of these
queries summarize data by day or by month. Our largest database gets 10+
million rows a day to several different tables (each). I played around
with a subset of the data spread across about 6 months (about 10 million
rows total).
I noticed that I could get very nice partition elimination using
constant values in the where clause.
Ex:
select * from <table> where <constraint_col> between '2015-01-01' and
'2015-02-15'
However, I could not get any partition elimination for queries that did
not have constant values in the where clause.
Ex:
select * from <table> where <constraint_col> >= (select max(date) from
<other_table>)
Unfortunately all of our queries on the analytics team need to be
dynamic like this and summarize data based around certain recorded
events and dates from other tables. I saw the note in the docs about not
being able to use current_timestamp in the where clause but I really
need to be able to use a sub select or CTE in the where clause for the
needed dates.
I tried about 10 different ways (on both 9.1 and 9.4) to dynamically get
the data (sub selects, cte, joins) for my constraint column but all of
them resulted in a full scan of all partitions.
I am kind of bummed out by this as dropping in partitioning in this
method will just hurt performance and not improve it. The only good
thing I see is the ability to delete (drop) older data from the table,
but this is not a functionality we need right now.
I am going to try a few other ways tomorrow, I am hoping I am doing
something wrong, or is this just typical?
Thanks,
Sam
