Re: Postgres for a "data warehouse", 5-10 TB

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

 



Hi,

On 12 September 2011 12:28, Stephen Frost <sfrost@xxxxxxxxxxx> wrote:
> Once those are done, you can query against the 'parent' table with
> something like:
> select * from parent where date = '2010-01-01';
>
> And PG will realize it only has to look at table2 to get the results for
> that query.  This means the partitioning can be more-or-less any check
> constraint that will be satisfied by the data in the table (and PG will
> check/enforce this) and that PG can figure out will eliminate a partition
> from possibly having the data that matches the request.

Theory is nice but there are few gotchas (in 8.4) :

- planner can use constant expressions only. You will get scans across
all partitions when you use function (like now(), immutable function
with constant arguments), sub query (like part_col = (select x from
...) .. ) or anything which can't be evaluated to constat during query
planning.

- partitions constraints are not "pushed to joins" (assuming tables
partitioned by primary key):
select ... from X left join Y on X.primary_key = Y.primary_key where
part_col >= ... and X.primary_key >= .,, and X.primary_key < ...
must be rewritten like
select ... from X
left join Y on X.primary_key = Y.primary_key and X.primary_key >= .,,
and Y.primary_key < ...
where X.primary_key >= .,, and X.primary_key < ...
in order to avoid scan entire Y table (not only relevant partitions)

- ORDER BY / LIMIT X issue fixed in 9.1 (Allow inheritance table scans
to return meaningfully-sorted results.

Moreover all queries should have 'WHERE' on column which is used for
partitioning otherwise partitioning is not very useful (yes, it could
simplify data management -- drop partition vs delete from X where
part_col between A and B)

-- 
Ondrej Ivanic
(ondrej.ivanic@xxxxxxxxx)

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



[Postgresql General]     [Postgresql PHP]     [PHP Users]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Yosemite]

  Powered by Linux