Sequências Biológicas do UniProt no PostgreSQL

Question

voretaq7

Asked: 2012-04-27 14:22:15 +0800 CST2012-04-27 14:22:15 +0800 CST 2012-04-27 14:22:15 +0800 CST

Melhorando o desempenho de junções desagradáveis de visualização aninhada

772

Eu tenho um banco de dados de tamanho moderado espalhado por algumas tabelas, a arquitetura aproximada é:

Dados de entrada (ID de dados, ID de sessão e alguns campos de importância estatística)
Arquivo de entrada (ID de dados e um blob)
Arquivo de saída do estágio 1 (ID de dados e um blob)
Arquivo de saída do estágio 2 (ID de dados e um blob)
Resultados da categoria 1 (ID de dados e alguns booleanos)
Resultados da categoria 2 (ID de dados e alguns números inteiros)
Resultados da categoria 3 (ID de dados e alguns números inteiros)

Cada tabela tem aproximadamente 200.000 linhas.

Também tenho uma visualização que basicamente une tudo isso para que eu possa vários SELECTIDs (geralmente selecionando-os com base no ID da sessão) e visualize todos os dados relevantes em uma página.

A exibição funciona e a utilização do índice do plano de consulta parece razoável, mas os resultados são menos rápidos:

> EXPLAIN ANALYZE SELECT(*) FROM overlay WHERE test_session=12345;

                 QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Merge Right Join  (cost=7.19..74179.49 rows=10 width=305) (actual time=10680.129..10680.494 rows=4 loops=1)
   Merge Cond: (p.data_id = d.id)
   ->  Merge Join  (cost=7.19..75077.04 rows=183718 width=234) (actual time=0.192..10434.995 rows=173986 loops=1)
         Merge Cond: (p.data_id = input_file.data_id)
         ->  Merge Join  (cost=7.19..69917.74 rows=183718 width=222) (actual time=0.173..9255.653 rows=173986 loops=1)
               Merge Cond: (p.data_id = stage1_output_file.data_id)
               ->  Merge Join  (cost=5.50..62948.54 rows=183718 width=186) (actual time=0.153..8081.949 rows=173986 loops=1)
                     Merge Cond: (p.data_id = stage2_output_file.data_id)
                     ->  Merge Join  (cost=3.90..55217.36 rows=183723 width=150) (actual time=0.132..6918.814 rows=173986 loops=1)
                           Merge Cond: (p.data_id = stage3_output_file.data_id)
                           ->  Nested Loop  (cost=2.72..47004.01 rows=183723 width=114) (actual time=0.111..5753.105 rows=173986 loops=1)
                                 Join Filter: (p.impression = istr.id)
                                 ->  Merge Join  (cost=1.68..30467.90 rows=183723 width=102) (actual time=0.070..2675.733 rows=173986 loops=1)
                                       Merge Cond: (p.data_id = s.data_id)
                                       ->  Merge Join  (cost=1.68..19031.56 rows=183723 width=58) (actual time=0.049..1501.546 rows=173986 loops=1)
                                             Merge Cond: (p.data_id = t.data_id)
                                             ->  Index Scan using Category1_Results_pkey on Category1_Results p  (cost=0.00..7652.17 rows=183723 width=18) (actual time=0.025..315.531 rows=173986 loops=1)
                                             ->  Index Scan using Category3_Results_pkey on Category3_Results t  (cost=0.00..8624.43 rows=183787 width=40) (actual time=0.016..321.460 rows=173986 loops=1)
                                       ->  Index Scan using Category2_Results_pkey on Category2_Results s  (cost=0.00..8681.47 rows=183787 width=44) (actual time=0.014..320.794 rows=173986 loops=1)
                                 ->  Materialize  (cost=1.04..1.08 rows=4 width=20) (actual time=0.001..0.007 rows=4 loops=173986)
                                       ->  Seq Scan on Category1_impression_str istr  (cost=0.00..1.04 rows=4 width=20) (actual time=0.005..0.012 rows=4 loops=1)
                           ->  Index Scan using Stage3_Output_file_pkey on Stage3_Output_file stage3  (cost=0.00..8178.35 rows=183871 width=36) (actual time=0.015..317.698 rows=173986 loops=1)
                     ->  Index Scan using analysis_file_pkey on analysis_file Stage2_Output  (cost=0.00..8168.99 rows=183718 width=36) (actual time=0.014..317.776 rows=173986 loops=1)
               ->  Index Scan using Stage1_output_file_pkey on Stage1_output_file stg1  (cost=0.00..8199.07 rows=183856 width=36) (actual time=0.014..321.648 rows=173986 loops=1)
         ->  Index Scan using input_file_pkey on input_file input  (cost=0.00..8618.05 rows=183788 width=36) (actual time=0.014..328.968 rows=173986 loops=1)
   ->  Materialize  (cost=0.00..39.59 rows=10 width=75) (actual time=0.046..0.150 rows=4 loops=1)
         ->  Nested Loop Left Join  (cost=0.00..39.49 rows=10 width=75) (actual time=0.039..0.128 rows=4 loops=1)
               Join Filter: (t.id = d.input_quality)
               ->  Index Scan using input_data_exists_index on input_data d  (cost=0.00..28.59 rows=10 width=45) (actual time=0.013..0.025 rows=4 loops=1)
                     Index Cond: (test_session = 1040)
               ->  Seq Scan on quality_codes t  (cost=0.00..1.04 rows=4 width=38) (actual time=0.002..0.009 rows=4 loops=4)
 Total runtime: 10680.902 ms

As visualizações subjacentes para isso são nossa visualização de "resultados completos", definida como:

 SELECT p.data_id, p.x2, istr.str AS impression, input.h, p.x3, p.x3, p.x4, s.x5,
        s.x6, s.x7, s.x8, s.x9, s.x10, s.x11, s.x12, s.x13, s.x14, t.x15,
        t.x16, t.x17, t.x18, t.x19, t.x20, t.x21, t.x22, t.x23,
        input.data AS input, stage1_output_file.data AS stage1, 
        stage2_output_file.data AS stage2, stage3_output_file.data AS stage3
FROM category1_results p, category1_impression_str istr, input_file input,
     stage1_output_file, stage2_output_file, stage3_output_file, 
     category2_results s, category3_results t
 WHERE p.impression = istr.id AND p.data_id = input.data_id AND p.data_id = stage1_output_file.data_id
       AND p.data_id = stage2_output_file.data_id AND p.data_id = stage3_output_file.data_id AND p.data_id = s.data_id AND p.data_id = t.data_id;

e a exibição de sobreposição a partir da qual o plano de consulta acima foi gerado, definido como:

 SELECT d.data_id, d.test_session, d.a, d.b, t.c, d.d, d.e, d.f, r.*
 FROM input_data d LEFT JOIN quality_codes t ON t.id = d.input_quality
      LEFT JOIN full_results r ON r.data_id = d.data_id  
 WHERE NOT d.deleted;

Parece que estamos juntando todo o nosso conjunto de dados na maior parte da cadeia e estou bastante convencido de que esse é o nosso problema de desempenho - Alguém tem uma sugestão sobre maneiras de otimizar esse porco?

2 respostas

Voted

Erwin Brandstetter · Answer 1 · 2012-04-27T18:11:39+08:00

Estou especulando aqui, mas acho que o fato de você LEFT JOINacessar a view faz com que o planejador calcule os resultados da view como um todo antes de ingressar na primeira parte da consulta.

Tente embutir a consulta da exibição e torná-la um JOINem vez de LEFT JOIN, apenas para ver se o planejador encontra uma maneira mais rápida agora:

SELECT d.data_id, d.test_session, d.a, d.b, t.c, d.d, d.e, d.f
     , p.data_id AS p_data_id, p.x2, c.str AS impression, i.h
     , p.x3, p.x3, p.x4
     , s.x5, s.x6, s.x7, s.x8, s.x9, s.x10, s.x11, s.x12, s.x13, s.x14
     , t.x15, t.x16, t.x17, t.x18, t.x19, t.x20, t.x21, t.x22, t.x23
     , i.data AS input
     , s1.data AS stage1, s2.data AS stage2, s3.data AS stage3
FROM   input_data d
JOIN   category1_results        p ON p.data_id = d.data_id
JOIN   input_file               i USING (data_id)
JOIN   stage1_output_file      s1 USING (data_id)
JOIN   stage2_output_file      s2 USING (data_id)
JOIN   stage3_output_file      s3 USING (data_id)
JOIN   category2_results        s USING (data_id)
JOIN   category3_results        t USING (data_id)
JOIN   category1_impression_str c ON p.impression = c.id 
LEFT   JOIN quality_codes       t ON t.id = d.input_quality
WHERE  NOT d.deleted;

Limpei sua sintaxe para torná-la mais gerenciável. Adicionado um alias para a segunda data_idcoluna, para que ela possa ser executada.

Se isso levar a um tempo de execução consideravelmente mais rápido, você pode tentar adicionar linhas ausentes devido a INNER JOIN:

SELECT DISTINCT ON (1,2,3,4,5,6,7,8) *
FROM (
    <<query>>
    ) x
UNION ALL
SELECT d.data_id, d.test_session, d.a, d.b, t.c, d.d, d.e, d.f
      ,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL
      ,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL
      ,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL
FROM   input_data d
LEFT   JOIN quality_codes t ON t.id = d.input_quality
WHERE  NOT d.deleted;
ORDER  BY 1,2,3,4,5,6,7,8, 9 NULLS LAST; -- p.data_id is otherwise not null

voretaq7 · Answer 2 · 2012-04-27T15:06:58+08:00

voretaq7

2012-04-27T15:06:58+08:002012-04-27T15:06:58+08:00

Depois de olhar para isso por alguns dias, tenho certeza de que uma solução possível é desnormalizar as tabelas e colocar IDs de sessão em todas elas. Isso deve permitir que o planejador de consulta reduza os JOINs a um subconjunto menor de linhas muito mais rapidamente.

A grande desvantagem aqui é desnormalizar os bancos de dados - provavelmente não é um problema, mas algo que eu evitaria se possível ...

0

Melhorando o desempenho de junções desagradáveis de visualização aninhada

Como ver a lista de bancos de dados no Oracle?

Quão grande deve ser o mysql innodb_buffer_pool_size?

Listar todas as colunas de uma tabela especificada

restaurar a tabela do arquivo .frm e .ibd?

Como usar o sqlplus para se conectar a um banco de dados Oracle localizado em outro host sem modificar meu próprio tnsnames.ora

Como você mysqldump tabela (s) específica (s)?

Como selecionar a primeira linha de cada grupo?

Listar os privilégios do banco de dados usando o psql

Como inserir valores em uma tabela de uma consulta de seleção no PostgreSQL?

Como faço para listar todos os bancos de dados e tabelas usando o psql?

Melhorando o desempenho de junções desagradáveis ​​de visualização aninhada

2 respostas

relate perguntas

Melhorando o desempenho de junções desagradáveis de visualização aninhada