I have a statement which is inserting a bunch of rows (doesn't really matter what or where for this question) into a Postgres DB and it isn't as fast as I'd like. I can run an explain query to see what it's doing and I get something like this:

                                                     QUERY PLAN                                                     
 Insert on datapoints  (cost=0.00..6837.74 rows=44916 width=1774) (actual time=24718.425..24718.425 rows=0 loops=1)
   ->  Seq Scan on fred  (cost=0.00..6837.74 rows=44916 width=1774) (actual time=0.024..369.642 rows=44916 loops=1)
 Planning time: 0.081 ms
 Trigger for constraint datapoints_tag_source_fkey: time=191.508 calls=44916
 Trigger for constraint datapoints_sheet_type_fkey: time=524.057 calls=44916
 Trigger for constraint datapoints_subcontext_fkey: time=142.782 calls=44916
 Trigger for constraint datapoints_source_type_fkey: time=437.829 calls=44916
 Trigger for constraint datapoints_doc_type_fkey: time=270.409 calls=44916
 Trigger for constraint datapoints_comment_type_fkey: time=87.718 calls=44916
 Trigger for constraint datapoints_preferred_dpid_fkey: time=29.739 calls=44916
 Execution time: 26425.926 ms
(11 rows)

This is great in that I see the cost of the various triggers and the select part of the query but if I add up all those parts it's about 2s (less than 10% of the total time). It doesn't tell me much about the actual insert other than it took about 24 seconds. I'm guessing that a fair part of the cost is in updating indexes but I don't know this for sure. How I can get a breakdown of which indexes are being updated and the relative costs of each? Is this even possible?

  • "11 rows". Could it be that each trigger, each foreign key is checked 11 times. Looks probable. I think you have all time spent in your explain. – Gerard H. Pille Sep 22 '20 at 12:05
  • No. The 11 rows is just the length of the explain plan. – Richard Wheeldon Sep 22 '20 at 13:40

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.