Encontrar una manera menos intensiva de hacer una consulta

Hola,

Me veo obligado a pedir ayuda para obtener información de una base de
datos que estamos trabajando; el problema puede estar desde el diseño
de la misma, o en que no sabemos cómo realizar una consulta. Adjunto
el esquema de creación de nuestra base, y les platico un poco de qué
va, las magnitudes y... bueno, donde estamos dándonos de topes :-|

Estamos representando de la evolución en el tiempo del llavero
criptográfico de confianza del proyecto Debian; tenemos ya un par de
presentaciones al respecto, y estamos trabajando en encontrar datos
adicionales. Un primer vistazo a de qué se trata pueden encontrarlo en
este poster que presenté en 2015:

http://ru.iiec.unam.mx/2767/

Un llavero criptográfico es, esencialmente, una matriz de
adyacencia. Dado que lo que queremos es analizar su progresión en el
tiempo, estamos tomando como puntos muestrales los tags de un depósito
Git a lo largo desde el 2008 (desde que se guarda en un VCS). Claro,
el punto complicado viene de que la información está almacenada
sencillamente como llaves PGP - No como datos analizables. El primer
paso del trabajo, y del cual salió el esquema que les adjunto, es
obtener la información de cada una de las llaves PGP, y acomodarla en
su lugar en una base de datos.

Hemos hecho algunos avances, y vamos a presentarlos en el OSS2017 en
Buenos Aires (https://oss2017.org); con gusto comparto con los
interesados el trabajo (creo que no es "lícito" aún distribuirlo
públicamente, si bien el licenciamiento será de acceso abierto una vez
esté publicado).

Bueno, como decimos en México: "A lo que te truje".

Hemos logrado hacer el seguimiento y pronóstico estadístico de cada
llave PGP. Sin embargo, y en buena medida gracias a una migración
forzada que hicimos, esto nos presenta una importante distorsión
respecto a una medida mucho más útil en el proyecto: Queremos
proyectar la permanencia de cada _persona_ en el proyecto. A lo largo
de los años, cada persona puede tener diferentes llaves, e ir
transitando entre ellas. Puede también migrar entre los tres llaveros
(las tres categorías de desarrolladores/mantenedores).

No he actualizado con el último par de meses; hasta donde tenemos en
la BD, hay 136 puntos muestrales (tags). Para cada tag, tenemos un
universo cercano a las 1000-1400 llaves.

Dado que nos interesa explotar información de los distintos aspectos
de las identidades PGP, separamos la información de la llave misma de
la de sus identidades. La lista de identidades puede ir variando con
el tiempo. Y la llave de una persona puede cambiar: Mi llave fue la
0xD80EF35A8BB527AF hasta que la cambié por la 0x673A03E4C1DB921F.

Ahora bien... El "objeto de estudio" fundamental no debería ser la
llave, sino que la persona. Determinamos que una persona está definida
por una o más llaves con la misma dirección de correo.

No se si el problema sea la cantidad de datos o nuestra inexperiencia
desarrollando consultas medianamente complejas... Pero este «EXPLAIN
ANALYZE SELECT * FROM people_metadata» me suena a
grosería. Obviamente, no es algo que quiero lanzar a cada consulta.

-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Unique (cost=3636070005.85..3816382987.84 rows=2533020 width=104) (actual time=31861.424..32001.154 rows=35037 loops=1)
-> Unique (cost=3636070005.85..3815623081.84 rows=25330200 width=104) (actual time=31861.422..31951.979 rows=35037 loops=1)
-> Sort (cost=3636070005.85..3656020347.62 rows=7980136711 width=104) (actual time=31861.419..31895.440 rows=45138 loops=1)
Sort Key: pkm1.keyid, k_1.keyid, u_1.username, u_1.email, a_1.name, k_1.created_at, k_1.expires, u_1.comment
Sort Method: external merge Disk: 3984kB
-> Merge Join (cost=1153935.56..141517597.64 rows=7980136711 width=104) (actual time=31620.497..31763.454 rows=45138 loops=1)
Merge Cond: (pkm1.email = u_1.email)
Join Filter: (pkm1.keyid <> k_1.keyid)
Rows Removed by Join Filter: 13763
-> Sort (cost=527183.78..530350.06 rows=1266510 width=28) (actual time=15887.892..15895.740 rows=12291 loops=1)
Sort Key: pkm1.email
Sort Method: quicksort Memory: 1341kB
-> Subquery Scan on pkm1 (cost=330509.32..368504.62 rows=1266510 width=28) (actual time=11148.082..15867.615 rows=12291 loops=1)
-> Unique (cost=330509.32..355839.52 rows=1266510 width=96) (actual time=11148.079..15852.019 rows=12291 loops=1)
-> Sort (cost=330509.32..333675.60 rows=1266510 width=96) (actual time=11148.077..14728.903 rows=1266332 loops=1)
Sort Key: k.keyid, k.created_at, u.username, u.email, a.name, k.expires, u.comment
Sort Method: external merge Disk: 122928kB
-> Hash Join (cost=507.53..72262.16 rows=1266510 width=96) (actual time=24.812..5044.779 rows=1266332 loops=1)
Hash Cond: (ptu.userid_id = u.id)
-> Hash Join (cost=184.65..40276.53 rows=1266510 width=60) (actual time=9.451..2837.590 rows=1266332 loops=1)
Hash Cond: (ptu.pubkey_id = k.id)
-> Seq Scan on pubkey_tag_userid ptu (cost=0.00..19511.10 rows=1266510 width=8) (actual time=0.007..858.127 rows=1266332 loops=1)
-> Hash (cost=143.62..143.62 rows=3282 width=60) (actual time=9.432..9.432 rows=3293 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 257kB
-> Hash Join (cost=37.67..143.62 rows=3282 width=60) (actual time=0.048..6.695 rows=3293 loops=1)
Hash Cond: (k.pk_algorithm_id = a.id)
-> Seq Scan on pubkey k (cost=0.00..60.82 rows=3282 width=32) (actual time=0.004..2.006 rows=3293 loops=1)
-> Hash (cost=22.30..22.30 rows=1230 width=36) (actual time=0.035..0.035 rows=21 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 2kB
-> Seq Scan on pk_algorithm a (cost=0.00..22.30 rows=1230 width=36) (actual time=0.002..0.018 rows=21 loops=1)
-> Hash (cost=196.28..196.28 rows=10128 width=44) (actual time=15.340..15.340 rows=10141 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 773kB
-> Seq Scan on userid u (cost=0.00..196.28 rows=10128 width=44) (actual time=0.006..7.023 rows=10141 loops=1)
-> Materialize (cost=626751.78..633084.33 rows=1266510 width=96) (actual time=15732.583..15782.492 rows=58901 loops=1)
-> Sort (cost=626751.78..629918.06 rows=1266510 width=96) (actual time=15732.577..15740.278 rows=12291 loops=1)
Sort Key: u_1.email
Sort Method: quicksort Memory: 2167kB
-> Unique (cost=330509.32..355839.52 rows=1266510 width=96) (actual time=11075.380..15712.305 rows=12291 loops=1)
-> Sort (cost=330509.32..333675.60 rows=1266510 width=96) (actual time=11075.378..14601.906 rows=1266332 loops=1)
Sort Key: k_1.keyid, k_1.created_at, u_1.username, u_1.email, a_1.name, k_1.expires, u_1.comment
Sort Method: external merge Disk: 122928kB
-> Hash Join (cost=507.53..72262.16 rows=1266510 width=96) (actual time=24.525..5021.566 rows=1266332 loops=1)
Hash Cond: (ptu_1.userid_id = u_1.id)
-> Hash Join (cost=184.65..40276.53 rows=1266510 width=60) (actual time=9.152..2826.009 rows=1266332 loops=1)
Hash Cond: (ptu_1.pubkey_id = k_1.id)
-> Seq Scan on pubkey_tag_userid ptu_1 (cost=0.00..19511.10 rows=1266510 width=8) (actual time=0.013..851.098 rows=1266332 loops=1)
-> Hash (cost=143.62..143.62 rows=3282 width=60) (actual time=9.127..9.127 rows=3293 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 257kB
-> Hash Join (cost=37.67..143.62 rows=3282 width=60) (actual time=0.059..6.591 rows=3293 loops=1)
Hash Cond: (k_1.pk_algorithm_id = a_1.id)
-> Seq Scan on pubkey k_1 (cost=0.00..60.82 rows=3282 width=32) (actual time=0.007..2.025 rows=3293 loops=1)
-> Hash (cost=22.30..22.30 rows=1230 width=36) (actual time=0.039..0.039 rows=21 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 2kB
-> Seq Scan on pk_algorithm a_1 (cost=0.00..22.30 rows=1230 width=36) (actual time=0.003..0.018 rows=21 loops=1)
-> Hash (cost=196.28..196.28 rows=10128 width=44) (actual time=15.359..15.359 rows=10141 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 773kB
-> Seq Scan on userid u_1 (cost=0.00..196.28 rows=10128 width=44) (actual time=0.006..7.088 rows=10141 loops=1)
Planning time: 1.131 ms
Execution time: 32041.530 ms
(59 rows)

Queremos seguir jalando información de este conjunto de datos. Creo
que la información que tenemos representada en el esquema mismo es
estable, pero francamente, las vistas las hemos ido desarrollando
medio "a trompicones" dando pequeños pasos. En este análisis veo ocho
casos de "seq scan" , muchos de ellos sobre tablas bastante pesaditas
(particularmente la tabla pivote pubkey_tag_userid - Dos veces, por si
fuera poco).

Les agradeceré cualquier comentario que puedan hacer a mi
(ridículamente extenso) correo.

Saludos,