Skip to content

Time spent on partition range insert #12

@thomasboussekey

Description

@thomasboussekey

Hello,

I'm comparing insertion time between non-partitionned tables and partitionned tables.
In my tests, the insert ratio is 10 times slower with partitionned tables. I tried to identify the cause of this time spent.

Time spend on the trigger is greater than 98%.

So I modified the function used by the insert trigger on my test table tbl_pathman, to identify on which part of the function the most of time is spent.

CREATE OR REPLACE FUNCTION tbl_pathman_insert_trigger_func() RETURNS trigger AS $$
DECLARE                                                                                     
        v_part_relid OID;
        v_tmstp_begin TIMESTAMP;
        v_tmstp_end TIMESTAMP;                                                                   
BEGIN                                                                                       
        SELECT clock_timestamp() INTO v_tmstp_begin;
        IF TG_OP = 'INSERT' THEN
                SELECT clock_timestamp() INTO v_tmstp_end;
                INSERT INTO log_pathman_trig SELECT 1, v_tmstp_begin, v_tmstp_end, 1000.0 * extract(epoch FROM (v_tmstp_end - v_tmstp_begin));
                select v_tmstp_end INTO v_tmstp_begin;

                IF NEW.id IS NULL THEN                                                      
                        RAISE EXCEPTION 'ERROR: NULL value in partitioning key';            
                END IF;                                                                     

                SELECT clock_timestamp() INTO v_tmstp_end;
                INSERT INTO log_pathman_trig SELECT 2, v_tmstp_begin, v_tmstp_end, 1000.0 * extract(epoch FROM (v_tmstp_end - v_tmstp_begin));
                select v_tmstp_end INTO v_tmstp_begin;

                v_part_relid := public.find_or_create_range_partition(TG_RELID, NEW.id);    

                SELECT clock_timestamp() INTO v_tmstp_end;
                INSERT INTO log_pathman_trig SELECT 3, v_tmstp_begin, v_tmstp_end, 1000.0 * extract(epoch FROM (v_tmstp_end - v_tmstp_begin));
                --SELECT 3, v_tmstp_begin, v_tmstp_end, 1000.0 * extract(epoch FROM (v_tmstp_end - v_tmstp_begin));
                select v_tmstp_end INTO v_tmstp_begin;

                IF NOT v_part_relid IS NULL THEN                                            
                        EXECUTE format('INSERT INTO %s SELECT $1.*', v_part_relid::regclass)
                        USING NEW;                                                          
                ELSE                                                                        
                        RAISE EXCEPTION 'ERROR: Cannot find partition';                     
                END IF;                              
                SELECT clock_timestamp() INTO v_tmstp_end;
                INSERT INTO log_pathman_trig SELECT 4, v_tmstp_begin, v_tmstp_end, 1000.0 * extract(epoch FROM (v_tmstp_end - v_tmstp_begin));

        END IF;                                                                             
        RETURN NULL;                                                                        
END;                                                                                         
$$ LANGUAGE plpgsql;

Adding the log table:

CREATE TABLE log_pathman_trig 
(trig_id INTEGER, tmstp_begin timestamp, tmstp_end timestamp, ms_elapsed numeric(18,3));

Here are the results

trig_id Statement Percent
1 TG_OP=INSERT 4.41
2 Check ID is NULL 18.84
3 find_or_create_range_partition 18.96
4 EXECUTE format 57.78

So the statement EXECUTE format is the longest statement of the trigger.

Do you know if there is a way to improve this?
Can I help you on this point?

Regards,
Thomas

Metadata

Metadata

Assignees

Type

No type

Projects

No projects

Milestone

No milestone

Relationships

None yet

Development

No branches or pull requests

Issue actions