Projet

Général

Profil

0001-Add-and-use-indexes-on-concerned_roles_array-and-act.patch

Pierre Ducroquet, 10 janvier 2022 18:02

Télécharger (2,9 ko)

Voir les différences: 

Subject: [PATCH] Add and use indexes on concerned_roles_array and
 actions_roles_array (#60469)

After reading some slow queries log in lille-prod, I noticed these two columns
had no usable index (and their queries were not able to use them anyway).
This patch fixes it.

 wcs/sql.py | 13 ++++++++++---
 1 file changed, 10 insertions(+), 3 deletions(-)
wcs/sql.py
751 751 
                '%(create_index)s %(table_name)s_%(attr)s_idx ON %(table_name)s (%(attr)s)'
752 752 
                % {'create_index': create_index, 'table_name': table_name, 'attr': attr}
753 753 
            )
754 
    for attr in ('concerned_roles_array', 'actions_roles_array'):
755 
        if table_name + '_' + attr + '_idx' not in existing_indexes:
756 
            cur.execute(
757 
                '%(create_index)s %(table_name)s_%(attr)s_idx ON %(table_name)s USING gin (%(attr)s)'
758 
                % {'create_index': create_index, 'table_name': table_name, 'attr': attr}
759 
            )
754 760 

  		




  755
  761
  
    

  		




  756
  762
  
    
@guard_postgres

  		




  ......




  2336
  2342
  
    
            value = str(value)

  		




  2337
  2343
  
    

  		




  2338
  2344
  
    
        if '%s_array' % index in [x[0] for x in cls._table_static_fields]:

  		




  2339
  
  
    
            sql_statement = '''SELECT id FROM %s WHERE %%(value)s = ANY (%s_array)''' % (

  		




  
  2345
  
    
            sql_statement = '''SELECT id FROM %s WHERE %s_array @> ARRAY[%%(value)s]''' % (

  		




  2340
  2346
  
    
                cls._table_name,

  		




  2341
  2347
  
    
                index,

  		




  2342
  2348
  
    
            )

  		




  ......




  3499
  3505
  
    
# latest migration, number + description (description is not used

  		




  3500
  3506
  
    
# programmaticaly but will make sure git conflicts if two migrations are

  		




  3501
  3507
  
    
# separately added with the same number)

  		




  3502
  
  
    
SQL_LEVEL = (55, 'update full text normalisation (switch to unidecode)')

  		




  
  3508
  
    
SQL_LEVEL = (56, 'add gin indexes to concerned_roles_array and actions_roles_array')

  		




  3503
  3509
  
    

  		




  3504
  3510
  
    

  		




  3505
  3511
  
    
def migrate_global_views(conn, cur):

  		




  ......




  3638
  3644
  
    
        # 51: add index on formdata blockdef fields

  		




  3639
  3645
  
    
        # 55: update full text normalisation (switch to unidecode)

  		




  3640
  3646
  
    
        set_reindex('formdata', 'needed', conn=conn, cur=cur)

  		




  3641
  
  
    
    if sql_level < 46:

  		




  
  3647
  
    
    if sql_level < 56:

  		




  3642
  3648
  
    
        from wcs.carddef import CardDef

  		




  3643
  3649
  
    
        from wcs.formdef import FormDef

  		




  3644
  3650
  
    

  		




  ......




  3646
  3652
  
    
        # 35: add indexes on formdata(receipt_time) and formdata(anonymised)

  		




  3647
  3653
  
    
        # 36: add index on formdata(user_id)

  		




  3648
  3654
  
    
        # 45 & 46: add index on formdata(status)

  		




  
  3655
  
    
        # 56: add GIN indexes to concerned_roles_array and actions_roles_array

  		




  3649
  3656
  
    
        for formdef in FormDef.select() + CardDef.select():

  		




  3650
  3657
  
    
            do_formdef_indexes(formdef, created=False, conn=conn, cur=cur)

  		




  3651
  3658
  
    
    if sql_level < 32:

  		




  3652
  
  
    
-