From: | Fabio Pardi <f(dot)pardi(at)portavita(dot)eu> |
---|---|
To: | pgsql-novice(at)lists(dot)postgresql(dot)org |
Subject: | Re: An Inesrt on a partitioned table become very slow |
Date: | 2018-07-09 09:16:24 |
Message-ID: | 2e83674d-ef20-c6a7-d6d1-a903d569dc28@portavita.eu |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-novice |
Hi Tomer,
On Postgres, partitioning does not scale well when partitions are in the order of 4+ figures.
https://www.postgresql.org/docs/10/static/ddl-partitioning.html
It is very likely that most of the time is spent by the query planner.
regards,
fabio pardi
On 09/07/18 07:35, Tomer Praizler wrote:
> Hey,
>
> I use Postgres 10.1, and I have the following structure:
> Parent table with columns:
> range_value: double, timestamp: timestamp, device_id: integer, count:integer
>
> which is partitioned with Partition key: LIST (range_value) which has a value of 1 - 26.
> So basically it has 26 child partitions.
>
> Each one of those partitions it partitioned by range. essentially a daily partition.
>
> This is how the perant_table_with_range_value_1 is conofigured:
>
> Partition of: perant_table FOR VALUES IN ('1')
> Partition constraint: ((range_value IS NOT NULL) AND (range_value = ANY (ARRAY['0'::double precision])))
> Partition key: RANGE ("timestamp")
>
> At the moment I have 128 days of partitions in each one of the ranged partitioned.
> Which means a total of 26 * 128 = 3328 total partitions.
>
> My problem is that inserts became very slow.
> I used to bulk insert 10000 rows in 6 seconds, now it takes 45 seconds.
>
> Is this an expected behavior? is there something I missing?
> Thank you!
>
>
>
From | Date | Subject | |
---|---|---|---|
Next Message | David Rowley | 2018-07-09 10:31:02 | Re: An Inesrt on a partitioned table become very slow |
Previous Message | Tomer Praizler | 2018-07-09 05:35:31 | An Inesrt on a partitioned table become very slow |