Sequências Biológicas do UniProt no PostgreSQL

Question

Mad Scientist

Asked: 2017-01-21 07:37:32 +0800 CST2017-01-21 07:37:32 +0800 CST 2017-01-21 07:37:32 +0800 CST

A coluna agregada causa uma verificação completa da tabela, mesmo que o índice correto esteja presente

772

Eu tenho uma consulta onde quero buscar as primeiras linhas dos conjuntos de dados da tabela ordenados pela coluna date_added. A coluna classificada por é indexada, portanto, a versão básica desta tabela é muito rápida:

SELECT datasets.id FROM datasets ORDER BY date_added LIMIT 25

"Limit  (cost=0.28..6.48 rows=25 width=12) (actual time=0.040..0.092 rows=25 loops=1)"
"  ->  Index Scan using datasets_date_added_idx2 on datasets  (cost=0.28..1244.19 rows=5016 width=12) (actual time=0.037..0.086 rows=25 loops=1)"
"Planning time: 0.484 ms"
"Execution time: 0.139 ms"

Mas eu tenho um problema quando faço a consulta um pouco mais complicada. Desejo juntar outra tabela representando um relacionamento muitos para muitos e agregar os resultados em uma coluna de matriz. Para fazer isso, preciso adicionar uma cláusula GROUP BY id:

SELECT datasets.id FROM datasets GROUP BY datasets.id ORDER BY date_added LIMIT 25

"Limit  (cost=551.41..551.47 rows=25 width=12) (actual time=9.926..9.931 rows=25 loops=1)"
"  ->  Sort  (cost=551.41..563.95 rows=5016 width=12) (actual time=9.924..9.926 rows=25 loops=1)"
"        Sort Key: date_added"
"        Sort Method: top-N heapsort  Memory: 26kB"
"        ->  HashAggregate  (cost=359.70..409.86 rows=5016 width=12) (actual time=7.016..8.604 rows=5016 loops=1)"
"              Group Key: datasets_id"
"              ->  Seq Scan on datasets  (cost=0.00..347.16 rows=5016 width=12) (actual time=0.009..1.574 rows=5016 loops=1)"
"Planning time: 0.502 ms"
"Execution time: 10.235 ms"

Apenas adicionando a cláusula GROUP BY, a consulta agora faz uma varredura completa da tabela de conjuntos de dados em vez de usar o índice na coluna date_added como anteriormente.

Uma versão simplificada da consulta real que quero fazer é a seguinte:

SELECT 
    datasets.id,
    array_remove(array_agg(other_table.some_column), NULL) AS other_table
FROM datasets 
LEFT JOIN other_table 
    ON other_table.id = datasets.id
GROUP BY datasets.id 
ORDER BY date_added 
LIMIT 25

Por que a cláusula GROUP BY faz com que o índice seja ignorado e força uma varredura completa da tabela? E existe uma maneira de reescrever essa consulta para que ela use o índice na coluna pela qual ela é classificada?

Estou usando o Postgres 9.5.4 no Windows, a tabela em questão tem atualmente 5000 linhas, mas poderia ter algumas centenas de milhares. Executei ANALYZE manualmente em ambas as tabelas antes do EXPLAIN ANALYZE.

Definições da tabela:

CREATE TABLE public.datasets
(
  id integer NOT NULL DEFAULT nextval('datasets_id_seq'::regclass),
  date_added timestamp with time zone,
  ...
  CONSTRAINT datasets_pkey PRIMARY KEY (id)
)

CREATE TABLE public.other_table
(
  id integer NOT NULL,
  some_column integer NOT NULL,
  CONSTRAINT other_table_pkey PRIMARY KEY (id, some_column)
)

A saída de \d datasetscom colunas irrelevantes anonimizadas:

                                                   Table "public.datasets"
             Column              |           Type           |                           Modifiers
---------------------------------+--------------------------+------------------------------------------------------
 id                              | integer                  | not null default nextval('datasets_id_seq'::regclass)
 key                             | text                     |
 date_added                      | timestamp with time zone |
 date_last_modified              | timestamp with time zone |
 *****                           | integer                  |
 ********                        | boolean                  | default false
 *****                           | boolean                  | default false
 ***************                 | integer                  |
 *********************           | integer                  |
 *********                       | boolean                  | default false
 ********                        | integer                  |
 ************                    | integer                  |
 ************                    | integer                  |
 ****************                | timestamp with time zone |
 ************                    | text                     | default ''::text
 *****                           | text                     |
 *******                         | integer                  |
 *********                       | integer                  |
 **********************          | text                     | default ''::text
 *******************             | text                     |
 ****************                | integer                  |
 **********************          | text                     | default ''::text
 *******************             | text                     | default ''::text
 **********                      | integer                  |
 ***********                     | text                     |
 ***********                     | text                     |
 **********************          | integer                  |
 ******************************* | text                     | default ''::text
 ************************        | text                     | default ''::text
 ***********                     | integer                  | default 0
 *************                   | text                     |
 *******************             | integer                  |
 ****************                | integer                  | default 0
 ***************                 | text                     |
 **************                  | text                     |
Indexes:
    "datasets_pkey" PRIMARY KEY, btree (id)
    "datasets_date_added_idx" btree (date_added)
    "datasets_*_idx" btree (*)
    "datasets_*_idx" btree (*)
    "datasets_*_idx" btree (*)
    "datasets_*_idx" btree (*)
    "datasets_*_idx" btree (*)
    "datasets_*_idx1" btree (*)
    "datasets_*_idx" btree (*)

1 respostas

Voted

ypercubeᵀᴹ · Answer 1 · 2017-01-21T09:51:17+08:00

O problema é que sua segunda consulta:

SELECT datasets.id 
FROM datasets 
GROUP BY datasets.id 
ORDER BY date_added 
LIMIT 25 ;

não significa o que você espera. Ele fornece as primeiras 25 linhas ordenadas por date_addedapenas porque idé a chave primária da tabela, portanto, GROUP BYpode ser removida sem alterar o resultado.

Parece, no entanto, que o otimizador nem sempre remove o redundante GROUP BYe, portanto, produz um plano diferente. Não sei por que - os vários recursos do otimizador que fazem essas simplificações estão longe de cobrir todos os casos.

Você pode obter um plano melhor se alterar a consulta para ter correspondência GROUP BYe ORDER BYcláusulas:

SELECT d.id 
FROM datasets AS d 
GROUP BY d.date_added, d.id 
ORDER BY d.date_added, d.id 
LIMIT 25 ;

Mas em qualquer caso, meu conselho seria "não use sintaxe redundante / complicada quando houver uma mais simples".

Agora para a 3ª consulta, com a junção, enquanto o GROUP BYmétodo está funcionando, você pode reescrevê-la usando funções de janela SQL padrão ( ROW_NUMBER()) ou Postgres DISTINCT ONou juntando-se a uma tabela derivada (que usa sua primeira consulta!, com pequenos detalhes alterados ):

SELECT  
    d.id,
    array_remove(array_agg(o.some_column), NULL) AS other_table
FROM 
  ( SELECT d.id, d.date_added
    FROM datasets AS d 
    ORDER BY d.date_added 
    LIMIT 25 
  ) AS d
LEFT JOIN other_table AS o
    ON o.id = d.id
GROUP BY d.date_added, d.id
ORDER BY d.date_added
LIMIT 25 ;

Também poderíamos evitar GROUP BYcompletamente (bem, está oculto na subconsulta inline):

SELECT  
    d.id,
    ( SELECT array_remove(array_agg(o.some_column), NULL)
      FROM other_table AS o
      WHERE o.id = d.id
    ) AS other_table
FROM  datasets AS d 
ORDER BY d.date_added 
LIMIT 25 ;

Ambas as consultas são escritas para que o plano produzido faça primeiro a subconsulta de limite (rápida) e depois a junção, evitando uma varredura completa de qualquer tabela.

Se você precisar agregar de mais colunas, um terceiro método combina os dois acima, usando uma LATERALsubconsulta correlacionada ( ) na FROMcláusula:

SELECT  
    d.id,
    o.other_table
    -- more aggregates
FROM 
    ( SELECT d.id, d.date_added
      FROM datasets AS d 
      ORDER BY d.date_added 
      LIMIT 25 
    ) AS d
  LEFT JOIN LATERAL
    ( SELECT array_remove(array_agg(o.some_column), NULL) AS other_table
             -- more aggregates
      FROM other_table AS o
      WHERE o.id = d.id
    ) AS o
    ON TRUE
ORDER BY d.date_added
LIMIT 25 ;

A coluna agregada causa uma verificação completa da tabela, mesmo que o índice correto esteja presente

conectar ao servidor PostgreSQL: FATAL: nenhuma entrada pg_hba.conf para o host

Como fazer a saída do sqlplus aparecer em uma linha?

Selecione qual tem data máxima ou data mais recente

Como faço para listar todos os esquemas no PostgreSQL?

Listar todas as colunas de uma tabela especificada

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)?

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?

A coluna agregada causa uma verificação completa da tabela, mesmo que o índice correto esteja presente

1 respostas

relate perguntas