tomka's questions

Para representar uma matriz 2D esparsa de valores flutuantes, uso uma tabela de banco de dados Postgres 13 simples que armazena um único valor de célula de matriz por linha. Eu gostaria de melhorar uma consulta que calcula a versão densa de uma matriz como uma matriz 2D (por exemplo real[][], ), com células vazias padronizadas para zero.

O esquema da tabela matriz fica assim:

           Table "public.similarity_score"
      Column      |  Type   | Collation | Nullable | Default 
------------------+---------+-----------+----------+---------
 query_object_id  | bigint  |           | not null | 
 target_object_id | bigint  |           | not null | 
 score            | real    |           | not null | 
 similarity_id    | integer |           | not null | 

Várias matrizes podem ser armazenadas na mesma tabela ( similarity_id) e para cada linha ( query_object_id) e coluna ( target_object_id), um único valor pode ser armazenado ( score).

Além de muitos outros campos, a similarity(conforme referenciado pela tabela acima) contém uma lista de todas as opções de linha e coluna possíveis:

                                     Table "public.similarity"
         Column         |    Type   | Collation | Nullable |             Default              
------------------------+--------------------------+-----------+----------+-------------------
 id                     | integer   |           | not null | generated by default as identity
 query_objects          | bigint[]  |           |          | 
 target_objects         | bigint[]  |           |          | 
 …

Ao montar a matriz densa para um similarity, gostaria de ter as linhas e colunas na ordem dessas query_objectse target_objects, ou seja, as linhas de resultado podem ser mapeadas para esses arrays. Atualmente, faço isso usando a seguinte consulta:

WITH qt AS MATERIALIZED (                                       
    SELECT query.id as query_id, query.o as query_o,            
            target.id AS target_id, target.o AS target_o        
    FROM similarity s,                                   
    UNNEST(s.query_objects) WITH ORDINALITY AS query(id, o),    
    UNNEST(s.target_objects) WITH ORDINALITY AS target(id, o)   
    WHERE s.id = %(similarity_id)s                              
),                                                              
scores AS (                                                     
    SELECT qt.*, COALESCE(nss.score, 0) AS score                
    FROM qt                                                     
    LEFT JOIN similarity_score nss                       
    ON nss.query_object_id = qt.query_id                        
    AND nss.target_object_id = qt.target_id                     
    AND nss.similarity_id = %(similarity_id)s                   
),                                                              
score_rows AS (                                                 
    SELECT query_o, array_agg(score ORDER BY target_o) as scores
    FROM scores                                                 
    GROUP BY query_o                                            
)                                                               
SELECT array_agg(scores ORDER BY query_o)                       
FROM score_rows;

O plano para uma matriz de elementos de 1025*9097 é assim:

 Aggregate  (cost=485.99..486.01 rows=1 width=32) (actual time=53124.869..53124.874 rows=1 loops=1)
   Buffers: shared hit=26513337
   CTE qt
     ->  Nested Loop  (cost=0.29..6.91 rows=100 width=32) (actual time=0.781..2812.748 rows=9324425 loops=1)
           Buffers: shared hit=9232
           ->  Nested Loop  (cost=0.28..2.91 rows=10 width=36) (actual time=0.085..1.624 rows=1025 loops=1)
                 Buffers: shared hit=7
                 ->  Index Scan using nblast_similarity_pkey on nblast_similarity s  (cost=0.28..2.51 rows=1 width=51) (actual time=0.010..0.012 rows=1 loops=1)
                       Index Cond: (id = 2282)
                       Buffers: shared hit=3
                 ->  Function Scan on unnest query  (cost=0.00..0.20 rows=10 width=16) (actual time=0.075..0.935 rows=1025 loops=1)
                       Buffers: shared hit=4
           ->  Function Scan on unnest target  (cost=0.00..0.20 rows=10 width=16) (actual time=0.662..1.803 rows=9097 loops=1025)
                 Buffers: shared hit=9225
   ->  GroupAggregate  (cost=473.82..476.82 rows=100 width=40) (actual time=50217.415..53062.468 rows=1025 loops=1)
         Group Key: qt.query_o
         Buffers: shared hit=26513337
         ->  Sort  (cost=473.82..474.07 rows=100 width=20) (actual time=50214.748..50650.740 rows=9324425 loops=1)
               Sort Key: qt.query_o
               Sort Method: quicksort  Memory: 832749kB
               Buffers: shared hit=26513337
               ->  Nested Loop Left Join  (cost=3.52..470.50 rows=100 width=20) (actual time=0.790..48459.027 rows=9324425 loops=1)
                     Buffers: shared hit=26513337
                     ->  CTE Scan on qt  (cost=0.00..4.00 rows=100 width=32) (actual time=0.782..5483.806 rows=9324425 loops=1)
                           Buffers: shared hit=9232
                     ->  Bitmap Heap Scan on nblast_similarity_score nss  (cost=3.52..4.65 rows=1 width=20) (actual time=0.004..0.004 rows=0 loops=9324425)
                           Recheck Cond: ((target_object_id = qt.target_id) AND (query_object_id = qt.query_id))
                           Filter: (similarity_id = 2282)
                           Heap Blocks: exact=78412
                           Buffers: shared hit=26504105
                           ->  BitmapAnd  (cost=3.52..3.52 rows=1 width=0) (actual time=0.003..0.003 rows=0 loops=9324425)
                                 Buffers: shared hit=26425693
                                 ->  Bitmap Index Scan on nblast_similarity_score_target_object_id  (cost=0.00..1.43 rows=21 width=0) (actual time=0.002..0.002 rows=9 loops=9324425)
                                       Index Cond: (target_object_id = qt.target_id)
                                       Buffers: shared hit=18648850
                                 ->  Bitmap Index Scan on nblast_similarity_score_query_object_id  (cost=0.00..1.84 rows=77 width=0) (actual time=0.003..0.003 rows=76 loops=3871425)
                                       Index Cond: (query_object_id = qt.query_id)
                                       Buffers: shared hit=7776843
 Planning:
   Buffers: shared hit=2
 Planning Time: 0.474 ms
 Execution Time: 53362.434 ms
(42 rows)

Time: 53363.526 ms (00:53.364)

Seria ótimo se esse tempo pudesse ser reduzido dos atuais 53 segundos. Eu o comparo com alternativas de armazenar a matriz densa explicitamente como real[][](6 segundos) ou large object(4,5 segundos), que são obviamente mais rápidas ao ler os dados inteiramente (vem com desvantagens como limitações de tamanho e acesso SQL lento a entradas individuais). O plano acima sugere que algumas estimativas de tamanho de resultados são muito baixas, talvez levando a planos ruins (sim, é ANALYZEDe a meta de estatísticas padrão é 5000)?

Por contexto: esta consulta é realizada a partir de um back-end Django (Python) e retornada a um front-end como resposta a uma solicitação HTTP.

Edit: Conforme sugerido por Laurenz Albe, algumas melhorias são possíveis com melhores estimativas. Eu tentei tanto uma large_unnestfunção personalizada quanto desabilitar junções de loop aninhadas para a consulta. Ambos são sugestões úteis e levam a planos melhores. A large_unsetfunção customizada leva a um tempo de execução de 19 segundos :

 Aggregate  (cost=1348212.99..1348213.01 rows=1 width=32) (actual time=18804.368..18804.374 rows=1 loops=1)
   Buffers: shared hit=9732
   CTE qt
     ->  Nested Loop  (cost=0.78..160083.01 rows=4000000 width=32) (actual time=2.180..3113.809 rows=9324425 loops=1)
           Buffers: shared hit=9232
           ->  Nested Loop  (cost=0.53..82.76 rows=2000 width=36) (actual time=0.391..2.654 rows=1025 loops=1)
                 Buffers: shared hit=7
                 ->  Index Scan using nblast_similarity_pkey on nblast_similarity s  (cost=0.28..2.51 rows=1 width=51) (actual time=0.018..0.021 rows=1 loops=1)
                       Index Cond: (id = 2282)
                       Buffers: shared hit=3
                 ->  Function Scan on large_unnest query  (cost=0.25..40.25 rows=2000 width=16) (actual time=0.365..1.633 rows=1025 loops=1)
                       Buffers: shared hit=4
           ->  Function Scan on large_unnest target  (cost=0.25..40.25 rows=2000 width=16) (actual time=1.834..2.252 rows=9097 loops=1025)
                 Buffers: shared hit=9225
   ->  GroupAggregate  (cost=1158120.98..1188125.48 rows=200 width=40) (actual time=15175.754..18745.797 rows=1025 loops=1)
         Group Key: scores.query_o
         Buffers: shared hit=9732
         ->  Sort  (cost=1158120.98..1168120.98 rows=4000000 width=20) (actual time=15172.036..15584.416 rows=9324425 loops=1)
               Sort Key: scores.query_o
               Sort Method: quicksort  Memory: 1121687kB
               Buffers: shared hit=9732
               ->  Subquery Scan on scores  (cost=607270.06..719489.61 rows=4000000 width=20) (actual time=10514.543..13821.419 rows=9324425 loops=1)
                     Buffers: shared hit=9732
                     ->  Limit  (cost=607270.06..639489.61 rows=4000000 width=36) (actual time=10514.536..13183.841 rows=9324425 loops=1)
                           Buffers: shared hit=9732
                           ->  Merge Right Join  (cost=607270.06..639489.61 rows=4000000 width=36) (actual time=10261.048..12382.913 rows=9324425 loops=1)
                                 Merge Cond: ((nss.query_object_id = qt.query_id) AND (nss.target_object_id = qt.target_id))
                                 Buffers: shared hit=9732
                                 ->  Sort  (cost=8638.69..8834.72 rows=78412 width=20) (actual time=33.814..43.757 rows=78412 loops=1)
                                       Sort Key: nss.query_object_id, nss.target_object_id
                                       Sort Method: quicksort  Memory: 9198kB
                                       Buffers: shared hit=500
                                       ->  Seq Scan on nblast_similarity_score nss  (cost=0.00..2264.27 rows=78412 width=20) (actual time=0.019..6.504 rows=78412 loops=1)
                                             Filter: (similarity_id = 2282)
                                             Buffers: shared hit=500
                                 ->  Sort  (cost=598631.37..608631.37 rows=4000000 width=32) (actual time=10227.173..11103.484 rows=9324425 loops=1)
                                       Sort Key: qt.query_id, qt.target_id
                                       Sort Method: quicksort  Memory: 1121687kB
                                       Buffers: shared hit=9232
                                       ->  CTE Scan on qt  (cost=0.00..160000.00 rows=4000000 width=32) (actual time=2.185..5524.025 rows=9324425 loops=1)
                                             Buffers: shared hit=9232
 Planning:
   Buffers: shared hit=2
 Planning Time: 0.680 ms
 JIT:
   Functions: 36
   Options: Inlining true, Optimization true, Expressions true, Deforming true
   Timing: Generation 1.907 ms, Inlining 6.672 ms, Optimization 151.239 ms, Emission 95.707 ms, Total 255.524 ms
 Execution Time: 19135.939 ms
(49 rows)

Time: 19137.368 ms (00:19.137)

Curiosamente, desabilitar completamente a junção de loop aninhado para a consulta e remover MATERIALIZEDdo primeiro CTE leva a um plano ainda melhor com um tempo de execução de 8 segundos , mas somente se o original UNNESTfor usado:

 Aggregate  (cost=20000004304.55..20000004304.57 rows=1 width=32) (actual time=8460.589..8460.594 rows=1 loops=1)
   Buffers: shared hit=9732
   ->  GroupAggregate  (cost=20000004303.34..20000004304.32 rows=10 width=40) (actual time=5628.507..8402.370 rows=1025 loops=1)
         Group Key: query.o
         Buffers: shared hit=9732
         ->  Sort  (cost=20000004303.34..20000004303.59 rows=100 width=20) (actual time=5625.952..6067.150 rows=9324425 loops=1)
               Sort Key: query.o
               Sort Method: quicksort  Memory: 832749kB
               Buffers: shared hit=9732
               ->  Hash Left Join  (cost=20000004224.85..20000004300.02 rows=100 width=20) (actual time=201.473..4356.689 rows=9324425 loops=1)
                     Hash Cond: ((query.id = nss.query_object_id) AND (target.id = nss.target_object_id))
                     Buffers: shared hit=9732
                     ->  Nested Loop  (cost=20000000000.28..20000000006.91 rows=100 width=32) (actual time=181.110..2089.076 rows=9324425 loops=1)
                           Buffers: shared hit=9232
                           ->  Nested Loop  (cost=10000000000.28..10000000002.91 rows=10 width=36) (actual time=180.056..181.406 rows=1025 loops=1)
                                 Buffers: shared hit=7
                                 ->  Index Scan using nblast_similarity_pkey on nblast_similarity s  (cost=0.28..2.51 rows=1 width=51) (actual time=179.859..179.861 rows=1 loops=1)
                                       Index Cond: (id = 2282)
                                       Buffers: shared hit=3
                                 ->  Function Scan on unnest query  (cost=0.00..0.20 rows=10 width=16) (actual time=0.186..0.939 rows=1025 loops=1)
                                       Buffers: shared hit=4
                           ->  Function Scan on unnest target  (cost=0.00..0.20 rows=10 width=16) (actual time=0.596..1.192 rows=9097 loops=1025)
                                 Buffers: shared hit=9225
                     ->  Hash  (cost=2264.27..2264.27 rows=78412 width=20) (actual time=20.286..20.287 rows=78412 loops=1)
                           Buckets: 131072  Batches: 1  Memory Usage: 5313kB
                           Buffers: shared hit=500
                           ->  Seq Scan on nblast_similarity_score nss  (cost=0.00..2264.27 rows=78412 width=20) (actual time=0.015..6.740 rows=78412 loops=1)
                                 Filter: (similarity_id = 2282)
                                 Buffers: shared hit=500
 Planning:
   Buffers: shared hit=2
 Planning Time: 0.650 ms
 JIT:
   Functions: 29
   Options: Inlining true, Optimization true, Expressions true, Deforming true
   Timing: Generation 1.608 ms, Inlining 6.629 ms, Optimization 102.178 ms, Emission 71.173 ms, Total 181.588 ms
 Execution Time: 8519.436 ms
(37 rows)

Time: 8520.675 ms (00:08.521)

Eu suponho que não há nenhuma maneira real de contornar a classificação embora.

Em uma função de gatilho, quero fazer pg_notify()chamadas condicionalmente, dependendo de uma opção de configuração do aplicativo. Para fazer isso, criei um wrapper notify_conditionally(). Se o aplicativo for iniciado com as notificações desabilitadas, uma segunda função SQL criará esse wrapper que não faz nada e é definido como IMMUTABLE:

CREATE OR REPLACE FUNCTION notify_conditionally(channel text, payload text) RETURNS int
LANGUAGE plpgsql IMMUTABLE AS
$inner$
BEGIN
    PERFORM 1 WHERE 1 = 0;
    RETURN 0;
END;
$inner$;

Se as notificações estiverem habilitadas, esta função é definida como uma chamada para pg_notify()com os parâmetros da função:

CREATE OR REPLACE FUNCTION notify_conditionally(channel text, payload text) RETURNS int
LANGUAGE plpgsql STABLE AS
$inner$
BEGIN
    PERFORM pg_notify(channel, payload);
    RETURN 0;
END;
$inner$;

Como os parâmetros para conditionally_notify()(e, portanto, pg_notify()) mudam a cada chamada, suponho que a palavra- IMMUTABLEchave não ajude muito em termos de cache de valor de resultado (na verdade, pode até ser contraproducente?). Portanto, estou querendo saber se existe uma maneira de definir essa função como "constante" e fazer com que o planejador seja inteligente o suficiente para não chamar essa função ou apenas chamá-la uma vez e reutilizar o primeiro resultado para todas as outras chamadas? Isso seria basicamente como um IMMUTABLEque ignora todos os parâmetros. Isso seria útil para evitar chamar essa função se as notificações estiverem desabilitadas. Em consultas maiores, isso faz uma diferença de ~ 500ms no meu caso e, idealmente, eu poderia diminuir isso no caso de notificações desabilitadas.

Embora seja uma possibilidade também atualizar o código que usa a notify_conditionally()função dinamicamente com base em se as notificações estão habilitadas e no caso de não serem apenas não use notify_conditionally(), eu preferiria não fazer isso. A razão é que isso acontece em uma função de gatilho bastante grande que atualmente só deve ser alterada por meio de uma migração completa.

Eu gostaria de entender melhor quando a cobertura de índices pode ser útil para possibilitar varreduras somente de índice no Postgres 11+. Como diz a documentação , dado o índice de cobertura

CREATE INDEX tab_x_y ON tab(x) INCLUDE (y);

consultas como esta podem usá-lo para varreduras somente de índice:

SELECT y FROM tab WHERE x = 'key';

Agora estou me perguntando se esse índice de cobertura também poderia permitir varreduras somente de índice quando as colunas de cobertura aparecerem como condições. Por exemplo, suponha um índice de cobertura:

CREATE INDEX tab_x_y_z ON tab(x) INCLUDE (y, z);

Isso permitiria verificações somente de índice para as seguintes consultas?

SELECT z FROM tab WHERE x = 'key' AND y = 1;

SELECT x, y, z FROM (VALUES ('key1'),('key2'),('key3')) sub(id)
JOIN tab ON tab.x = sub.id WHERE y = 1;

Além de suas colunas regulares, as tabelas do Postgres também possuem várias colunas de sistema disponíveis. Um deles, xmin, armazena o ID da transação usado para criar uma linha. Seu tipo de dados é xid, um inteiro de quatro bytes que envolve em algum ponto (ou seja, não necessariamente único). A função txid_current(), por sua vez, retorna o ID da transação atual, mas como bigint, porque "é estendido com um contador de 'época' para que não seja interrompido durante a vida útil de uma instalação" (para citar o manual ).

Se o wraparound das transações ainda não ocorreu, ambos os valores parecem corresponder:

# CREATE TABLE test (label text);
CREATE TABLE
# INSERT INTO test VALUES ('test') RETURNING txid_current();
 txid_current 
--------------
   674500
(1 row)
INSERT 0 1
# SELECT xmin FROM test;
  xmin  
--------
 674500
(1 row)

Mas eu me pergunto: esses dois valores são sempre comparáveis? Pelo que entendi, txid_current()continuará a fornecer valores exclusivos após a conclusão do ID da transação (no máximo 2 ^ 32 transações) e xmincomeçará do zero. Isso significa que ambos começam a retornar valores diferentes nesse ponto?

E se isso for verdade, existe uma maneira de extrair regular xidde um txid_current()resultado para que corresponda às xminentradas em uma tabela (por exemplo, conversão txid_current()para número inteiro)?

Editar : Deixe claro que me preocupo com o que acontece após um wraparound de ID de transação, o que provavelmente acontece muito antes de 2 ^ 32 transações. Obrigado a Daniel Vérité por notar isso nos comentários.

Desejo criar um novo nome de tabela com base em um existente anexando um sufixo a ele. Uma função PL/pgSQL do Postgres 9.5 obtém o nome da tabela existente passado como regclasstipo e retorna o novo nome como uma string. Estou usando format()para construir o novo nome e normalmente usaria o %Iespaço reservado. Isso funciona desde que o nome da tabela passado não corresponda a nenhuma palavra-chave PL/pgSQL. Nesse caso, independentemente do tipo de componente que eu escolher ( %Iou %s), a citação está errada.

Considere a seguinte função:

CREATE OR REPLACE FUNCTION make_name(old_name regclass)
RETURNS text                                           
LANGUAGE plpgsql AS                                    
$$                                                     
BEGIN                                                  
    RETURN format('%I_new', old_name);                 
END;                                                   
$$;                                                    

Além disso, suponha que haja duas tabelas: treenodee overlay. Chamar esta função para ambos resulta nos seguintes novos nomes:

SELECT make_name('overlay'::regclass);
     make_name     
-------------------
 """overlay"""_new
(1 row)

SELECT make_name('treenode'::regclass);
  make_name   
--------------
 treenode_new
(1 row)

Acontece que overlaytambém é uma função PL/pgSQL (assim como format, mas treenodenão é), que parece alterar o comportamento de citação. Se %sfor usado com format(), o resultado seria "overlay"_new. O mesmo acontece quando utilizo a ||operadora. Se eu usar textcomo tipo de parâmetro de entrada, tudo funcionará conforme o esperado, mas prefiro usar regclass.

Existe uma maneira de formatar uma string com um regclassnome de tabela de correspondência de palavra-chave (por exemplo overlay) sem aspas, assim como é o caso de um regclassnome de tabela de correspondência de palavra-chave (por exemplo treenode)?

Em outra questão aprendi que devo otimizar o layout de uma das minhas mesas para economizar espaço e ter melhor desempenho. Fiz isso, mas acabei com uma mesa maior do que antes e o desempenho não mudou. Claro que fiz um VACUUM ANALYZE. Como assim?

(Vejo que os tamanhos de índice não serão alterados se eu indexar apenas colunas únicas.)

Esta é a tabela de onde eu vim (adicionei tamanhos + preenchimento):

                               Table "public.treenode"
    Column     |           Type           | Size |          Modifiers
---------------+--------------------------+------+-------------------------------
 id            | bigint                   | 8    | not null default nextval( ...
 user_id       | integer                  | 4+4  | not null
 creation_time | timestamp with time zone | 8    | not null default now()
 edition_time  | timestamp with time zone | 8    | not null default now()
 project_id    | integer                  | 4    | not null
 location      | real3d                   | 36   | not null
 editor_id     | integer                  | 4+4  |
 parent_id     | bigint                   | 8    |
 radius        | real                     | 4    | not null default 0
 confidence    | smallint                 | 2    | not null default 5
 skeleton_id   | integer                  | 4    | not null

Sendo real3ddefinido como

CREATE TYPE real3d AS (
  x real,
  y real,
  z real);

Alterei este layout para o seguinte:

                                Table "public.treenode_new"
    Column     |           Type           | Size |            Modifiers
---------------+--------------------------+------+--------------------------------
 id            | bigint                   | 8    | not null default nextval(' ...
 project_id    | integer                  | 4    | not null
 location_x    | real                     | 4    | not null
 location_y    | real                     | 4    | not null
 location_z    | real                     | 4    | not null
 editor_id     | integer                  | 4    | not null
 user_id       | integer                  | 4    | not null
 creation_time | timestamp with time zone | 8    | not null default now()
 edition_time  | timestamp with time zone | 8    | not null default now()
 skeleton_id   | integer                  | 4    | not null
 radius        | real                     | 4    | not null default 0
 confidence    | real                     | 4+4  | not null default 5
 parent_id     | bigint                   | 8    |

Se não me engano, devo salvar 66 bytes por linha (138 é uma linha original, 72 é uma nova linha). Isso, porém, não está acontecendo: Com 7604913 nessas tabelas, a tabela original tinha um tamanho de 1020 MB. A nova tabela tem um tamanho de 1159 MB. Eu costumava pg_size_pretty(pg_relation_size('<tablename>'))medir os tamanhos. Então, o que estou perdendo?

Uma observação: todas, exceto as últimas quatro colunas, são herdadas de outra tabela (da qual, é claro, também tive que alterar o layout).

Atualização: Depois de executar VACUUM FULLconforme sugerido por Erwin Brandstetter, a nova tabela precisa de apenas 734 MB.

Como mostrado em outra pergunta, eu lido com muitas (> 10.000.000) entradas de pontos em um espaço 3D. Esses pontos são definidos assim:

CREATE TYPE float3d AS (
  x real,
  y real,
  z real);

Se não me engano são necessários 3*8 bytes + preenchimento de 8 bytes ( MAXALIGNé 8) para armazenar um desses pontos. Existe uma maneira melhor de armazenar esse tipo de dados? Na pergunta mencionada acima, foi afirmado que os tipos compostos envolvem bastante sobrecarga.

Muitas vezes faço consultas espaciais como esta:

  SELECT t1.id, t1.parent_id, (t1.location).x, (t1.location).y, (t1.location).z,
         t1.confidence, t1.radius, t1.skeleton_id, t1.user_id,
         t2.id, t2.parent_id, (t2.location).x, (t2.location).y, (t2.location).z,
         t2.confidence, t2.radius, t2.skeleton_id, t2.user_id
  FROM treenode t1
       INNER JOIN treenode t2 ON
         (   (t1.id = t2.parent_id OR t1.parent_id = t2.id)
          OR (t1.parent_id IS NULL AND t1.id = t2.id))
        WHERE (t1.LOCATION).z = 41000.0
          AND (t1.LOCATION).x > 2822.6
          AND (t1.LOCATION).x < 62680.2
          AND (t1.LOCATION).y > 33629.8
          AND (t1.LOCATION).y < 65458.6
          AND t1.project_id = 1 LIMIT 5000;

Uma consulta como essa leva cerca de 160 ms, mas gostaria de saber se isso poderia ser reduzido.

Este é o layout da tabela em que a estrutura é usada:

    Column     |           Type           |                       Modifiers                    
---------------+--------------------------+-------------------------------------------------------
 id            | bigint                   | not null default nextval('location_id_seq'::regclass)
 user_id       | integer                  | not null
 creation_time | timestamp with time zone | not null default now()
 edition_time  | timestamp with time zone | not null default now()
 project_id    | integer                  | not null
 location      | float3d                  | not null
 editor_id     | integer                  |
 parent_id     | bigint                   |
 radius        | real                     | not null default 0
 confidence    | smallint                 | not null default 5
 skeleton_id   | integer                  | not null

Indexes:
    "treenode_pkey" PRIMARY KEY, btree (id)
    "treenode_parent_id" btree (parent_id)
    "treenode_project_id_location_x_index" btree (project_id, ((location).x))
    "treenode_project_id_location_y_index" btree (project_id, ((location).y))
    "treenode_project_id_location_z_index" btree (project_id, ((location).z))
    "treenode_project_id_skeleton_id_index" btree (project_id, skeleton_id)
    "treenode_project_id_user_id_index" btree (project_id, user_id)
    "treenode_skeleton_id_index" btree (skeleton_id)

Em nosso banco de dados, usamos um tipo personalizado para armazenar localizações 3D:

CREATE TYPE float3d AS (
  x real,
  y real,
  z real);

Em muitos exemplos vejo INTERNALLENGTHusado como parâmetro com a definição de tipos personalizados. Além disso, a documentação me diz que "[a] suposição padrão é que é de comprimento variável", se esse parâmetro for omitido. Isso também é verdade para tipos como o acima, onde o tamanho pode ser calculado (3 * 4 bytes + preenchimento de 4 bytes)? Então, devo adicionar 16 como comprimento interno à definição?

Além disso, existem outros parâmetros que poderiam melhorar o desempenho da manipulação de muitas entradas (~ 10.000.000) usando esse tipo?

Estou executando o PostgresSQL 9.2 e tenho uma relação de 12 colunas com cerca de 6.700.000 linhas. Ele contém nós em um espaço 3D, cada um referenciando um usuário (que o criou). Para consultar qual usuário criou quantos nós, faço o seguinte (adicionado explain analyzepara obter mais informações):

EXPLAIN ANALYZE SELECT user_id, count(user_id) FROM treenode WHERE project_id=1 GROUP BY user_id;
                                                    QUERY PLAN                                                         
---------------------------------------------------------------------------------------------------------------------------
 HashAggregate  (cost=253668.70..253669.07 rows=37 width=8) (actual time=1747.620..1747.623 rows=38 loops=1)
   ->  Seq Scan on treenode  (cost=0.00..220278.79 rows=6677983 width=8) (actual time=0.019..886.803 rows=6677983 loops=1)
         Filter: (project_id = 1)
 Total runtime: 1747.653 ms

Como você pode ver, isso leva cerca de 1,7 segundos. Isso não é tão ruim considerando a quantidade de dados, mas eu me pergunto se isso pode ser melhorado. Tentei adicionar um índice BTree na coluna do usuário, mas isso não ajudou em nada.

Você tem sugestões alternativas?

Por uma questão de completude, esta é a definição completa da tabela com todos os seus índices (sem restrições de chave estrangeira, referências e gatilhos):

    Column     |           Type           |                      Modifiers                    
---------------+--------------------------+------------------------------------------------------
 id            | bigint                   | not null default nextval('concept_id_seq'::regclass)
 user_id       | bigint                   | not null
 creation_time | timestamp with time zone | not null default now()
 edition_time  | timestamp with time zone | not null default now()
 project_id    | bigint                   | not null
 location      | double3d                 | not null
 reviewer_id   | integer                  | not null default (-1)
 review_time   | timestamp with time zone |
 editor_id     | integer                  |
 parent_id     | bigint                   |
 radius        | double precision         | not null default 0
 confidence    | integer                  | not null default 5
 skeleton_id   | bigint                   |
Indexes:
    "treenode_pkey" PRIMARY KEY, btree (id)
    "treenode_id_key" UNIQUE CONSTRAINT, btree (id)
    "skeleton_id_treenode_index" btree (skeleton_id)
    "treenode_editor_index" btree (editor_id)
    "treenode_location_x_index" btree (((location).x))
    "treenode_location_y_index" btree (((location).y))
    "treenode_location_z_index" btree (((location).z))
    "treenode_parent_id" btree (parent_id)
    "treenode_user_index" btree (user_id)

Edit: Este é o resultado, quando uso a consulta (e índice) proposta por @ypercube (a consulta leva cerca de 5,3 segundos sem EXPLAIN ANALYZE):

EXPLAIN ANALYZE SELECT u.id, ( SELECT COUNT(*) FROM treenode AS t WHERE t.project_id=1 AND t.user_id = u.id ) AS number_of_nodes FROM auth_user As u;
                                                                        QUERY PLAN                                                                     
----------------------------------------------------------------------------------------------------------------------------------------------------------
 Seq Scan on auth_user u  (cost=0.00..6987937.85 rows=46 width=4) (actual time=29.934..5556.147 rows=46 loops=1)
   SubPlan 1
     ->  Aggregate  (cost=151911.65..151911.66 rows=1 width=0) (actual time=120.780..120.780 rows=1 loops=46)
           ->  Bitmap Heap Scan on treenode t  (cost=4634.41..151460.44 rows=180486 width=0) (actual time=13.785..114.021 rows=145174 loops=46)
                 Recheck Cond: ((project_id = 1) AND (user_id = u.id))
                 Rows Removed by Index Recheck: 461076
                 ->  Bitmap Index Scan on treenode_user_index  (cost=0.00..4589.29 rows=180486 width=0) (actual time=13.082..13.082 rows=145174 loops=46)
                       Index Cond: ((project_id = 1) AND (user_id = u.id))
 Total runtime: 5556.190 ms
(9 rows)

Time: 5556.804 ms

Edit 2: Este é o resultado, quando eu uso um indexon project_id, user_id(mas sem otimização de esquema, ainda) como sugerido por @erwin-brandstetter (a consulta é executada com 1,5 segundos na mesma velocidade que minha consulta original):

EXPLAIN ANALYZE SELECT user_id, count(user_id) as ct FROM treenode WHERE project_id=1 GROUP BY user_id;
                                                        QUERY PLAN                                                      
---------------------------------------------------------------------------------------------------------------------------
 HashAggregate  (cost=253670.88..253671.24 rows=37 width=8) (actual time=1807.334..1807.339 rows=38 loops=1)
   ->  Seq Scan on treenode  (cost=0.00..220280.62 rows=6678050 width=8) (actual time=0.183..893.491 rows=6678050 loops=1)
         Filter: (project_id = 1)
 Total runtime: 1807.368 ms
(4 rows)