Forums PostgreSQL.fr

J'ai fais désactiver kapersky et malgré cela j'ai toujours ces temps ?

explain (analyse,buffers)
SELECT c1, c2
  FROM foo;

"Seq Scan on foo  (cost=0.00..18334.00 rows=1000000 width=37) (actual time=0.040..66.897 rows=1000000 loops=1)"
"  Buffers: shared hit=32 read=8302"
"Total runtime: 85.730 ms"

Mais 21 secondes pour retourner toutes les lignes après reboot

Bonjour,

Je reviens vers vous car j'essaie d'optimiser la configuration de mon instance et les temps de réponse de mes requêtes en utilisant le tuto "Comprendre EXPLAIN" de Dalibo.
J'ai crée la table foo:
CREATE TABLE foo (c1 integer, c2 text);
INSERT INTO foo
SELECT i, md5(random()::text)
FROM generate_series(1, 1000000) AS i;

explain SELECT
  foo.c1,
  foo.c2
FROM
  public.foo;

"Seq Scan on foo  (cost=0.00..18334.00 rows=1000000 width=37)"

Les stats sont bonnes mais il faut + de 27 secondes à postgresql pour retourner les lignes de cette requête,
ça me semble beaucoup !

J'ai utilisé pgtune pour configurer mon instance:

# DB Version: 9.2
# OS Type: windows
# DB Type: desktop
# Total Memory (RAM): 3 GB
# Number of Connections: 5

max_connections = 5
shared_buffers = 192MB
effective_cache_size = 768MB
work_mem = 32MB
maintenance_work_mem = 192MB
checkpoint_segments = 3
checkpoint_completion_target = 0.5
wal_buffers = 5898kB
default_statistics_target = 100

Oui, c'est bien comme ça que le trigger est implémenté.

Pour l'insertion en une seule fois, comme il s'agit d'un insert dynamique, le texte de la requête va sans doute être trop long. (255 appareils * 16 voies comme contraintes)
Je préférerai éviter un trigger. De plus, certains appareils (enregistreurs autonomes) ne sont pas déchargés toutes les secondes et ne remontent pas leurs valeurs en temps réel.

Bonjour Julien, je vous donne plus d'informations

I- Ma config postgresql.conf:

#------------------------------------------------------------------------------
# AUTOVACUUM PARAMETERS
#------------------------------------------------------------------------------

#autovacuum = on            # Enable autovacuum subprocess?  'on'
                    # requires track_counts to also be on.
#log_autovacuum_min_duration = -1    # -1 disables, 0 logs all actions and
                    # their durations, > 0 logs only
                    # actions running at least this number
                    # of milliseconds.
#autovacuum_max_workers = 3        # max number of autovacuum subprocesses
                    # (change requires restart)
#autovacuum_naptime = 1min        # time between autovacuum runs
#autovacuum_vacuum_threshold = 50    # min number of row updates before
                    # vacuum
#autovacuum_analyze_threshold = 50    # min number of row updates before
                    # analyze
#autovacuum_vacuum_scale_factor = 0.2    # fraction of table size before vacuum
#autovacuum_analyze_scale_factor = 0.1    # fraction of table size before analyze
#autovacuum_freeze_max_age = 200000000    # maximum XID age before forced vacuum
                    # (change requires restart)
#autovacuum_vacuum_cost_delay = 20ms    # default vacuum cost delay for
                    # autovacuum, in milliseconds;
                    # -1 means use vacuum_cost_delay
#autovacuum_vacuum_cost_limit = -1    # default vacuum cost limit for
                    # autovacuum, -1 means use
                    # vacuum_cost_limit

(c'est celle de base, il me semble)

II- Les tables:

CREATE TABLE public.mesures (
                mes_id BIGINT NOT NULL DEFAULT nextval('public.mesures_mes_id_seq'),
                mes_date TIMESTAMP,
                mes_acquire TIMESTAMP,
                mes_valeur REAL,
                mes_on_alarme BIT,
                mes_numslave INTEGER,
                mes_numchannel INTEGER,
                mes_hashcode VARCHAR(255),
                mes_type INTEGER,
                mes_unite VARCHAR(20),
                mes_ap_code VARCHAR(50),
                mes_valeurorigine REAL ,
                mes_supprime BIT NOT NULL,
                mes_refsonde INTEGER,
                mes_precision INTEGER,
                mes_unitecodes VARCHAR(255) NOT NULL,
                mes_persist BIT NOT NULL,
                mes_numfunction BIGINT,
                alarmes_al_id BIGINT,
                acquisitions_acq_id BIGINT NOT NULL,
                CONSTRAINT mesures_pk PRIMARY KEY (mes_id)
);

CREATE TABLE mesure_summary
(
  mes_acquire         timestamp NOT NULL,
  mes_persist          bit NOT NULL        ,
  mes_hashcode          varchar   
);

ALTER TABLE mesure_summary
    SET (autovacuum_vacuum_scale_factor = 0.0);
    
    ALTER TABLE mesure_summary
    SET (autovacuum_vacuum_threshold = 50);
    
    ALTER TABLE mesure_summary
    SET (autovacuum_analyze_scale_factor = 0.0);
    
    ALTER TABLE mesure_summary
    SET (autovacuum_analyze_threshold = 50); 

car je souhaiterai réaliser des mises à jour de statistiques toutes les 50 lignes ajoutées.

|||- Les ajouts de mesures :

A chaque seconde, j' ajoute les mesures de 255 appareils dans la table principale mesures via un trigger en INSERT sur la table mesures

Ce trigger appelle la fonction maint_mesure_summary():

                                           
CREATE OR REPLACE FUNCTION maint_mesure_summary()
  RETURNS trigger AS
$BODY$
/*--------------------------------------------------------------------------------------------------
            Fonction pour la maintenance de la table mesure_summary
           
            SELECT     * from public.fc_mesure_insertmulti    (array[10524,10524]::bigint[]
            ,(E'2017-08-16 14:07:47.877452')::timestamp
            ,(array[E'KIMO_CP30',E'KIMO_CP30'])::character varying[]
            ,(array[E'-200',E'0'])::real[]
            ,(array[E'-200',E'0'])::real[]
            ,(array[0,0])::bit[]
            ,(array[11,11])::integer[]
            ,(array[1,2])::integer[]
            ,(array[E'',E'']):: character varying[]
            ,500::integer
            ,(array[E'°C',E'Pa'])::character varying[]
            ,(array[E'2017-08-16 14:07:44.518185',E'2017-08-16 14:07:44.518185'])::timestamp[]
            ,(B'0')::bit
            ,(array[0,0])::integer[]
            ,(array[-1,-1])::bigint[]
            ,(array[1,0])::integer[]
            ,(array[E'U7;°C',E'U11;PA']):: character varying[]
            ,(B'1')::bit
            ,(array[0,0])::bigint[]);

           
----------------------------------------------------------------------------------------------------*/

DECLARE
    schemaname  varchar ='';    -- Nom schema
    tablename    varchar ='mesure_summary';    -- Nom de la table resume mesure
    colname     varchar ='';            -- Nom de la colonne
    isfxchannel    bit=B'0';                -- Indicateur voie calculee
    schemacolname     varchar ='';            -- Nom de la colonne lu dans la bdd
    insert_sql    varchar ='';            -- Texte querie d'insertion
    delete_sql    varchar ='';            -- Texte querie d'insertion
    update_sql    varchar ='';            -- Texte querie de mise à jour de la table
    alter_sql    varchar ='';            -- Texte querie ajout colonne de la table
    coltest_sql    varchar ='';            -- Texte querie test presence colonne
    rowcount    int=0;                -- Nombre de ligne modifie
   
   
BEGIN

    --raise notice 'Start Trigger maint_mesure_summary';
   
   
    --  Ajout d'1 mesure
    IF (TG_OP = 'INSERT') THEN
   
       
        schemaname = TG_TABLE_SCHEMA;   
       
    --    raise notice 'Schema:%',schemaname;
        --raise notice 'TG_OP:%',TG_OP;

        -- Test valeur et date non null
        IF NEW.mes_valeur IS NULL OR  NEW.mes_acquire IS NULL THEN
            RETURN NEW;
        END IF;
       
        -- Determine le nom de la colonne
        IF NEW.mes_numfunction=0 THEN
            -- Format Voie : N°esclave
            colname := CONCAT ('D',NEW.mes_numslave)  ;
            isfxchannel :=B'0';
        ELSE
            -- Format Voie calculee : N°fonction
            isfxchannel :=B'1';
            colname := CONCAT('F',NEW.mes_numfunction) ;
        END IF;
       
        --raise notice 'INSERTION esclave:%,%',NEW.mes_numslave,NEW.mes_numchannel;
        -- CONSTITUTION QUERY INSERT
        insert_sql:='INSERT INTO ' || schemaname || '.'|| tablename || ' (mes_acquire,mes_persist,mes_hashcode, "' || colname
                             || '") SELECT ' || quote_literal(NEW.mes_acquire)
                             || ',' || quote_literal(NEW.mes_persist)
                             || ',' || quote_literal(NEW.mes_hashcode)        
                             || ',' || CONCAT('ROW(', NEW.mes_numslave,',',quote_literal(NEW.mes_ap_code),
                            -- GESTION VOIE CLASSIQUE/VOIE CALCULEE SUR LA PREMIERE VOIE (POUR LES VOIES CALCULEE ON A 1 SEULE VOIE ET LA VOIE FOURNIE PAR LE SERVICE EST A ZERO)
                            CASE WHEN isfxchannel=B'0' THEN
                                ',row(1,'|| COALESCE(CASE WHEN NEW.mes_numchannel=1 THEN quote_literal(NEW.mes_valeur) END,'NULL')||')'
                            ELSE
                                ',row(1,'|| COALESCE(CASE WHEN NEW.mes_numchannel=0 THEN quote_literal(NEW.mes_valeur) END,'NULL')||')'
                            END     ,',',
                                        'row(2,'|| COALESCE(CASE WHEN NEW.mes_numchannel=2 THEN quote_literal(NEW.mes_valeur) END,'NULL') ||')',',',
                                        'row(3,'|| COALESCE(CASE WHEN NEW.mes_numchannel=3 THEN quote_literal(NEW.mes_valeur) END,'NULL') ||')',',',
                                        'row(4,'|| COALESCE(CASE WHEN NEW.mes_numchannel=4 THEN quote_literal(NEW.mes_valeur) END,'NULL') ||')',',',
                                        'row(5,'|| COALESCE(CASE WHEN NEW.mes_numchannel=5 THEN quote_literal(NEW.mes_valeur) END,'NULL') ||')',',',
                                        'row(6,'|| COALESCE(CASE WHEN NEW.mes_numchannel=6 THEN quote_literal(NEW.mes_valeur) END,'NULL')||')',',',
                                        'row(7,'|| COALESCE(CASE WHEN NEW.mes_numchannel=7 THEN quote_literal(NEW.mes_valeur) END,'NULL') ||')',',',
                                        'row(8,'|| COALESCE(CASE WHEN NEW.mes_numchannel=8 THEN quote_literal(NEW.mes_valeur) END,'NULL') ||')',',',
                                        'row(9,'|| COALESCE(CASE WHEN NEW.mes_numchannel=9 THEN quote_literal(NEW.mes_valeur) END,'NULL') ||')',',',
                                        'row(10,'|| COALESCE(CASE WHEN NEW.mes_numchannel=10 THEN quote_literal(NEW.mes_valeur) END,'NULL') ||')',',',
                                        'row(11,'|| COALESCE(CASE WHEN NEW.mes_numchannel=11 THEN quote_literal(NEW.mes_valeur) END,'NULL') ||')',',',
                                        'row(12,'|| COALESCE(CASE WHEN NEW.mes_numchannel=12 THEN quote_literal(NEW.mes_valeur) END,'NULL') ||')',',',
                                        'row(13,'|| COALESCE(CASE WHEN NEW.mes_numchannel=13 THEN quote_literal(NEW.mes_valeur) END,'NULL') ||')',',',
                                        'row(14,'|| COALESCE(CASE WHEN NEW.mes_numchannel=14 THEN quote_literal(NEW.mes_valeur) END,'NULL') ||')',',',
                                        'row(15,'|| COALESCE(CASE WHEN NEW.mes_numchannel=15 THEN quote_literal(NEW.mes_valeur) END,'NULL') ||')',',',
                                        'row(16,'|| COALESCE(CASE WHEN NEW.mes_numchannel=16 THEN quote_literal(NEW.mes_valeur) END,'NULL') ||')',',',
                                        'row(17,'|| COALESCE(CASE WHEN NEW.mes_numchannel=17 THEN quote_literal(NEW.mes_valeur) END,'NULL') ||')',',',
                                        'row(18,'|| COALESCE(CASE WHEN NEW.mes_numchannel=18 THEN quote_literal(NEW.mes_valeur) END,'NULL') ||')',',',
                                        'row(19,'|| COALESCE(CASE WHEN NEW.mes_numchannel=19 THEN quote_literal(NEW.mes_valeur) END,'NULL') ||')',',',
                                        'row(20,'|| COALESCE(CASE WHEN NEW.mes_numchannel=20 THEN quote_literal(NEW.mes_valeur) END,'NULL'),
                                        '))::public.device_Propertie;');
                       
        --raise notice 'insert_sql:%',insert_sql;
       
        update_sql:= 'UPDATE '|| schemaname  || '.' || tablename
                        ||  ' SET ' || CONCAT('"',colname,'".V',
                                                    CASE WHEN isfxchannel=B'0' THEN
                                                        NEW.mes_numchannel
                                                    ELSE
                                                        '1'
                                                    END
                                                ,'.mes_valeur' ) || ' =  ' || NEW.mes_valeur
                        ||  ' WHERE MES_ACQUIRE=''' || NEW.mes_acquire  || '''';--|| ' AND  "' || colname || '" IS NULL;';

        --raise notice 'update_sql:%',update_sql;

        EXECUTE update_sql;
       
        GET DIAGNOSTICS rowcount = ROW_COUNT;
       
        raise notice 'update rowcount:%',rowcount;
       
        IF rowcount = 0 THEN
            raise notice 'insert_sql:%',insert_sql;
            EXECUTE insert_sql;
        END IF;
    
  END IF;

 
  RETURN NEW;

EXCEPTION
    WHEN SQLSTATE '42703'  THEN
    BEGIN
        RAISE NOTICE 'ERROR';

         -- TEST EXISTENCE COLONNE
         coltest_sql = 'SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS '
                  ||' WHERE TABLE_SCHEMA = ' || quote_literal(TG_TABLE_SCHEMA)
                  ||' AND TABLE_NAME=' || quote_literal(tablename)
                  ||' AND COLUMN_NAME='|| quote_literal(colname)   ;
                  
        --raise NOTICE 'coltest_sql:%' ,coltest_sql;

        EXECUTE  coltest_sql INTO schemacolname;

        --RAISE NOTICE 'AVANT AJOUT COLONNE';
       
        IF(schemacolname IS NULL ) THEN
       
            -- AJOUT COLONNE SI NON EXISTENCE
            --RAISE NOTICE 'AJOUT COLONNE';
           
            alter_sql = 'ALTER TABLE ' ||schemaname|| '.'|| tablename || ' ADD COLUMN "'|| colname || '"  public.device_Propertie ' ;

            --RAISE NOTICE 'alter_sql:%',alter_sql;
           
            EXECUTE alter_sql ;

            --raise notice 'insert_sql:%',coalesce (insert_sql,'<NULL>');
            --raise notice 'insert_sql error:%',coalesce (insert_sql,'<NULL>');
           
            EXECUTE insert_sql;

            RETURN NEW;
           
        END IF;

        RETURN NEW;
       
    END;
   
END;
$BODY$
  LANGUAGE plpgsql VOLATILE

  Explication de la fonction :
 
Quant j'ajoute dans la table mesures un enregistrement, j' ajoute (ou modifie) un enregistrement dans la table mesure_summary.

Si la colonne d'un appareil n'existe pas dans la table mesure_summary, je crée la colonne pour cet appareil.

Une fois la colonne existante,
    SI la date n'existe pas dans la table mesure_summary
        j' ajoute un enregistrement pour l' appareil (ajouté dans la table mesures)
    SINON
        je mets à jour l'enregistrement pour un autre appareil (ajouté dans la table mesures)

IV- Nouveau SELECT avec son plan d'execution:

explain analyse SELECT mes_id
  FROM w201747.mesure_summary;

"Bitmap Heap Scan on mesure_summary  (cost=20435.72..25845.45 rows=1384 width=8) (actual time=1150.544..39824.118 rows=2071 loops=1)"
"  ->  Bitmap Index Scan on mesure_summary_pk  (cost=0.00..20435.37 rows=1384 width=0) (actual time=1147.198..1147.198 rows=948368 loops=1)"
"Total runtime: 39824.949 ms"

La table de resume : mesure_summary comporte 443 colonnes pour 2082 lignes.
La table principal mesures comporte 3653952 lignes.

Merci pour votre aide

il s'agissait d'une simple requête :
SELECT mes_id
--,ROW_NUMBER ()  OVER ( ) ROWID
FROM w201747.mesure_summary
--ORDER BY MES_id desc
limit 3000

Non, je n'ai pas essayer de faire comme ça mais je vais y réfléchir.
Merci pour la réponse