From: | Samuel Smith <pgsql(at)net153(dot)net> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | Partitioning and constraint exclusion |
Date: | 2015-03-04 05:22:48 |
Message-ID: | 54F696A8.3040406@net153.net |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Roxanne Reid-Bennett | 2015-03-04 05:52:12 | Re: autovacuum worker running amok - and me too ;) |
Previous Message | Bruce Momjian | 2015-03-04 02:11:24 | Re: Sequences not moved to new tablespace |