PostgreSQL Índice De Análisis De Uso

Hay una herramienta o método para analizar Postgres, y determinar lo que falta índices deben ser creado, y que sin usar los índices deben ser eliminados? Tengo un poco de experiencia haciendo esto con el «analizador» herramienta para SQLServer, pero no estoy al tanto de una herramienta similar incluido con Postgres.

  • Así es. No he visto esto en un rato. Actualizado mi aceptado respuesta.
InformationsquelleAutor Cerin | 2010-07-23

10 Kommentare

  1. 154

    Me gusta este para encontrar índices que faltan:

    SELECT
      relname                                               AS TableName,
      to_char(seq_scan, '999,999,999,999')                  AS TotalSeqScan,
      to_char(idx_scan, '999,999,999,999')                  AS TotalIndexScan,
      to_char(n_live_tup, '999,999,999,999')                AS TableRows,
      pg_size_pretty(pg_relation_size(relname :: regclass)) AS TableSize
    FROM pg_stat_all_tables
    WHERE schemaname = 'public'
          AND 50 * seq_scan > idx_scan -- more then 2%
          AND n_live_tup > 10000
          AND pg_relation_size(relname :: regclass) > 5000000
    ORDER BY relname ASC;

    Este comprueba si hay más de secuencia de exploraciones, a continuación, el análisis de índice. Si la mesa es pequeña, se ignora, ya que Postgres parece preferir la secuencia de exploraciones para ellos.

    Consulta anterior pone de manifiesto la ausencia de índices.

    Sería el siguiente paso para detectar la falta de índices de síntesis. Supongo que esto no es fácil, pero factible. Tal vez el análisis de las consultas lentas … escuché pg_stat_statements podría ayudar…

    • Para realizar este trabajo con los identificadores entre comillas cambiar la consulta para: SELECT relname, seq_scan-idx_scan AS too_much_seq, case when seq_scan-idx_scan>0 THEN 'Missing Index?' ELSE 'OK' END, pg_relation_size(relid::regclass) AS rel_size, seq_scan, idx_scan FROM pg_stat_all_tables WHERE schemaname='public' AND pg_relation_size(relid::regclass)>80000 ORDER BY too_much_seq DESC;
    • El resultado de esta consulta debe ser explicado para que la respuesta más útil
    • A @cen ‘s punto, cuando too_much_seq es positivo y grande, usted debe estar preocupado.
    • He creado unos índices en una de las mesas, pero aún así, esta consulta muestra los índices que faltan en la tabla.
    • Supongo que las estadísticas en postgres todavía contienen las consultas que fueron ejecutados antes de actualizar el índice. Dependiendo de su tráfico de las estadísticas va a estar bien de nuevo después de algunas horas.
  2. 8

    Sobre la falta determinar los índices de enfoque….Nope. Pero hay algunos planes para hacer esto más fácil en el futuro de liberación, como pseudo-índices y legible por máquina EXPLICAR.

    Actualmente, usted necesitará EXPLAIN ANALYZE buenos para la realización de consultas y, a continuación, determinar manualmente la mejor ruta. Algunos analizadores de registro como pgFouine puede ayudar a determinar las consultas.

    Tan lejos como un índice sin utilizar, usted puede usar algo como lo siguiente para ayudar a identificarlos:

    select * from pg_stat_all_indexes where schemaname <> 'pg_catalog';

    Esto ayudará a identificar las tuplas de leer, escanear, tirado de los pelos.

    • Frank Heikens también señala algunos buenos lugares a otros lugares para consulta en el índice actual de uso.
  3. 7

    Otra nueva e interesante herramienta para el análisis de PostgreSQL es PgHero. Es más centrado en la optimización de la base de datos y realiza numerosos análisis y sugerencias.

    PostgreSQL Índice De Análisis De Uso

  4. 3

    Hay varios enlaces a los scripts que le ayudarán a encontrar sin usar los índices en el PostgreSQL wiki. La técnica básica es mirar pg_stat_user_indexes y mira por donde idx_scan, el recuento del número de veces que el índice ha sido usada para responder las consultas, es cero, o al menos muy baja. Si la aplicación ha cambiado y antes un índice utilizado probablemente no ahora, a veces se tiene que ejecutar pg_stat_reset() para obtener todas las estadísticas de nuevo a 0 y, a continuación, recoger nuevos datos, usted puede guardar los valores actuales de todo y calcular un delta en lugar de darse cuenta de eso.

    No hay buenas herramientas disponibles todavía para sugerir la ausencia de índices. Un método de registro de las consultas que se está ejecutando y analizar cuáles son tomar un largo tiempo para que se ejecute con un registro de consulta de la herramienta de análisis como pgFouine o pqa. Consulte «El Registro De Difícil Consultas» para más información.

    El otro enfoque es mirar pg_stat_user_tables y buscar mesas que tienen un gran número de exploraciones secuenciales en contra de ellos, donde seq_tup_fetch es grande. Cuando se utiliza un índice de la idx_fetch_tup cuenta es el aumento de lugar. Que puede pista en cuando una tabla no está bien indexado suficiente para responder a las consultas en contra de ella.

    Realidad averiguar qué columnas debe, a continuación, índice? Que por lo general conduce de nuevo a la consulta de análisis de registro de material de nuevo.

  5. 3

    Puede utilizar la siguiente consulta para encontrar el Índice de uso y el tamaño de Índice:

    Referencia es tomada de este blog.

    SELECT
        pt.tablename AS TableName
        ,t.indexname AS IndexName
        ,to_char(pc.reltuples, '999,999,999,999') AS TotalRows
        ,pg_size_pretty(pg_relation_size(quote_ident(pt.tablename)::text)) AS TableSize
        ,pg_size_pretty(pg_relation_size(quote_ident(t.indexrelname)::text)) AS IndexSize
        ,to_char(t.idx_scan, '999,999,999,999') AS TotalNumberOfScan
        ,to_char(t.idx_tup_read, '999,999,999,999') AS TotalTupleRead
        ,to_char(t.idx_tup_fetch, '999,999,999,999') AS TotalTupleFetched
    FROM pg_tables AS pt
    LEFT OUTER JOIN pg_class AS pc 
        ON pt.tablename=pc.relname
    LEFT OUTER JOIN
    ( 
        SELECT 
            pc.relname AS TableName
            ,pc2.relname AS IndexName
            ,psai.idx_scan
            ,psai.idx_tup_read
            ,psai.idx_tup_fetch
            ,psai.indexrelname 
        FROM pg_index AS pi
        JOIN pg_class AS pc 
            ON pc.oid = pi.indrelid
        JOIN pg_class AS pc2 
            ON pc2.oid = pi.indexrelid
        JOIN pg_stat_all_indexes AS psai 
            ON pi.indexrelid = psai.indexrelid 
    )AS T
        ON pt.tablename = T.TableName
    WHERE pt.schemaname='public'
    ORDER BY 1;
  6. 1

    PoWA parece una herramienta interesante para PostgreSQL 9.4+. Recopila estadísticas, se visualiza ellos, y sugiere índices. Utiliza el pg_stat_statements extensión.

    PoWA es PostgreSQL Carga de trabajo del Analizador que reúne las estadísticas de rendimiento y proporciona en tiempo real, tablas y gráficos para ayudar a monitorear y ajustar tus servidores PostgreSQL. Es similar a la de Oracle AWR o SQL Server MDW.

  7. 0
    CREATE EXTENSION pgstattuple; 
    CREATE TABLE test(t INT); 
    INSERT INTO test VALUES(generate_series(1, 100000)); 
    SELECT * FROM pgstatindex('test_idx'); 
    
    version            | 2 
    tree_level         | 2 
    index_size         | 105332736 
    root_block_no      | 412 
    internal_pages     | 40 
    leaf_pages         | 12804 
    empty_pages        | 0 
    deleted_pages      | 13 
    avg_leaf_density   | 9.84 
    leaf_fragmentation | 21.42 

Kommentieren Sie den Artikel

Bitte geben Sie Ihren Kommentar ein!
Bitte geben Sie hier Ihren Namen ein

Pruebas en línea