Search Postgresql Archives

Partitioning and constraint exclusion

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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


--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general




[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux