Re: Batch insert heavily affecting query performance.

Alvaro Hernandez <aht@xxxxxxxxxx> · Wed, 27 Dec 2017 18:09:28 +0100



    On 27/12/17 18:02, Jean Baro wrote:

    
      Sorry guys,
        

        The performance problem is not caused by PG. 
        

          'Index Scan using idx_user_country on
            public.old_card  (cost=0.57..1854.66 rows=460 width=922)
            (actual time=3.442..76.606 rows=200 loops=1)'
          '  Output: id, user_id, user_country,
            user_channel, user_role, created_by_system_key,
            created_by_username, created_at, last_modified_at,
            date_start, date_end, payload, tags, menu, deleted,
            campaign, correlation_id'
          '  Index Cond: (((old_card.user_id)::text =
            '1234'::text) AND (old_card.user_country = 'BR'::bpchar))'
          '  Buffers: shared hit=11 read=138 written=35'
          'Planning time: 7.748 ms'
          'Execution time: 76.755 ms'
          

          77ms on an 8GB database with 167MM rows and
            almost 500GB in size is amazing!!
        
      
        gp2 disks are of *variable* performance. Once you exhaust the
    I/O credits, you are capped to a baseline IOPS that are proportional
    to the size. I guess you would experience low performance in this
    scenario since your disk is not big. And actually performance
    numbers with gp2 disks are unreliable as you don't know in which
    credit status you are.

    
        Benchmark with provisioned iops to get a right picture of the
    desired performance.

    
        Cheers,

    
        Álvaro

    
    -- 

Alvaro Hernandez


-----------
OnGres
    

          Now we are investigating other bottlenecks, is it
            the creation of a new connection to PG  (no connection
            poller at the moment, like PGBouncer), is it the Lambda
            start up time? Is it the network performance  between PG and
            Lambda?
          

          I am sorry for wasting your time guys, it helped
            us to find the problem though, even if it wasn't a PG
            problem. 
          

          BTW, what a performance! I am impressed. 
          

          Thanks PG community! 
        
      
        Em 27 de dez de 2017 14:34, "Jean Baro"
          <jfbaro@xxxxxxxxx>
          escreveu:

          
              Thanks Jeremy,
                

                We will provide a more complete EXPLAIN
                  as other people have suggested. 
                

                I am glad we might end up with a much
                  better performance (currently each query takes around
                  2 seconds!).
                

                Cheers
                

                  Em 27 de dez de 2017 14:02,
                    "Jeremy Finzel" <finzelj@xxxxxxxxx>
                    escreveu:

                    
                                    The EXPLAIN
                                    

                                      'Index Scan using
                                        idx_user_country on card 
                                        (cost=0.57..1854.66 rows=460
                                        width=922)'
                                      '  Index Cond:
                                        (((user_id)::text =
                                        '4684'::text) AND (user_country
                                        = 'BR'::bpchar))'
                                    
                                  
                        Show 3 runs of the full
                          explain analyze plan on given condition so
                          that we can also see cold vs warm cache
                          performance.
                        

                        There is definitely
                          something wrong as there is no way a query
                          like that should take 500ms.  Your instinct is
                          correct there.