reox's questions

Eu tenho essas tabelas:

create table data (
    id int identity not null,
    ref int not null
);

create table reference (
    refid int not null
);

com aproximadamente 2 milhões de linhas em dados e aproximadamente 200 mil linhas em referência. Eu adiciono cerca de 5.000 linhas aos dados todos os dias, resultando em 500-5.000 linhas adicionais em referência por dia.

Na maioria dos casos, preciso salvar apenas uma referência para uma coluna de dados (várias entradas de dados podem ter a mesma referência), mas em alguns casos (atualmente cerca de 0,1%) preciso salvar mais de uma referência.

Eu provavelmente poderia usar uma terceira tabela como:

create table data_reference (
     dataid int not null,
     refid int not null
);

criar um mapeamento an:m mas para a maioria dos valores não vai ter nenhum ganho aqui, vai apenas criar outro join...

Eu também poderia criar uma segunda coluna em cada linha de dados, porque na maioria dos casos eu tenho apenas uma referência adicional (mas em teoria poderia haver mais) e salvar null se nenhuma outra referência estiver lá - o que me parece muito ruim porque pode haver tuplas com 3 ou mais referências.

Existe uma maneira eficiente e fácil de manter para salvar esses dados?

Tenho uma tabela que é constantemente atualizada por alguns scripts. Os scripts estão adicionando novos valores e alterando tuplas, mas nunca excluindo nenhuma tupla.

Preciso executar algumas consultas, que usam todas as linhas da tabela (cerca de 2 milhões no momento) para junção. O problema é que os scripts bloqueiam a tabela de uma forma, que minhas instruções select são bloqueadas e executadas muito lentamente (compare 16 minutos versus alguns segundos se estiver executando sem nenhum script em segundo plano).

É possível bloquear a tabela de forma que eu leia um instantâneo atual e não me importe com as alterações? Para a maioria dos aplicativos, é suficiente permitir que entradas fantasmas estejam presentes (os dados são alterados/adicionados enquanto a consulta selecionada estava em execução). Não é necessário na maioria dos casos bloquear a tabela inteira, mas sim usar um bloqueio de coluna (que eu acho que não funcionará no meu MS SQL Server 2005)

Eu tenho uma tabela com 7,2 milhões de tuplas que se parece com isso:

                               table public.methods
 column |          type         |                      attributes
--------+-----------------------+----------------------------------------------------
 id     | integer               | not null DEFAULT nextval('methodkey'::regclass)
 hash   | character varying(32) | not null
 string | character varying     | not null
 method | character varying     | not null
 file   | character varying     | not null
 type   | character varying     | not null
Indexes:
    "methods_pkey" PRIMARY KEY, btree (id)
    "methodhash" btree (hash)

Agora quero selecionar alguns valores, mas a consulta é incrivelmente lenta:

db=# explain 
    select hash, string, count(method) 
    from methods 
    where hash not in 
          (select hash from nostring) 
    group by hash, string 
    order by count(method) desc;
                                            QUERY PLAN
----------------------------------------------------------------------------------------
 Sort  (cost=160245190041.10..160245190962.07 rows=368391 width=182)
   Sort Key: (count(methods.method))
   ->  GroupAggregate  (cost=160245017241.77..160245057764.73 rows=368391 width=182)
       ->  Sort  (cost=160245017241.77..160245026451.53 rows=3683905 width=182)
             Sort Key: methods.hash, methods.string
             ->  Seq Scan on methods  (cost=0.00..160243305942.27 rows=3683905 width=182)
                   Filter: (NOT (SubPlan 1))
                   SubPlan 1
                   ->  Materialize  (cost=0.00..41071.54 rows=970636 width=33)
                     ->  Seq Scan on nostring  (cost=0.00..28634.36 rows=970636 width=33)

A hashcoluna é o hash md5 stringe tem um índice. Então, acho que meu problema é que a tabela inteira é classificada por id e não por hash, então demora um pouco para classificá-la primeiro e depois agrupá-la?

A tabela nostringcontém apenas uma lista de hashes que não quero ter. Mas eu preciso que ambas as tabelas tenham todos os valores. Portanto, não é uma opção para excluí-los.

informações adicionais: nenhuma das colunas pode ser nula (corrigido isso na definição da tabela) e estou usando o postgresql 9.2.