True, that's the same I feel, I will be looking to translate the trigger to C if I can find good examples, that should
accelerate.
Using rules would be totally bad as I'm partitioning daily and after one year having 365 lines of IF won't be fun to
maintain.
----------------------------------------
> Date: Fri, 21 Dec 2012 09:50:49 +1100
> Subject: Re: [PERFORM] Performance on Bulk Insert to Partitioned Table
> From: ondrej.ivanic@gmail.com
> To: charlesrg@outlook.com
> CC: pgsql-performance@postgresql.org
>
> Hi,
>
> On 21 December 2012 04:29, Charles Gomes <charlesrg@outlook.com> wrote:
> > When I target the MASTER table on all the inserts and let
> > the trigger decide what partition to choose from it takes 4 hours.
> >
> > If I target the partitioned table directly during the
> > insert I can get 4 times better performance. It takes 1 hour.
>
> Yes, that's my experience as well. Triggers are the slowest.
> Performance of "DO INSTEAD" rule is close to direct inserts but rule
> setup is complex (each partition needs one):
>
> create or replace rule <master_table>_insert_<partition_name> as on
> insert to <master_table>
> where new.<part_column> >= ... and
> new.<part_column> < ....
> do instead
> insert into <master_table>_<partition_name>
> values (new.*)
>
> The best is used to direct inserts (into partition) if you can.
>
> --
> Ondrej Ivanic
> (http://www.linkedin.com/in/ondrejivanic)
>
>
> --
> Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-performance