Re: Encontrar una manera menos intensiva de hacer una consulta

Alvaro Herrera dijo [Tue, Mar 14, 2017 at 02:35:26PM -0300]:
> > OK, interesante consejo, y tiene algo de sentido. Sin embargo... Se ve
> > que tengo que hacer algo de trabajo antes de implementarlo, porque,
> > con 8.5GB libres (y una BD que ocupa 6.6G),
>
> Hmm, 6.6 GB para unos cuantos keyring? Suena excesivo ...

Realmente suena excesivo. :-( Sobre todo si no me estoy yendo sobre la
más gorda de mis tablas (signatures, 37 millones), sino sobre una
"sencillita" - Pero como sea, está haciendo un join sobre sí misma de
más de un millón de registros.

> Noté recién que tienes ORDER BY en las definiciones de algunas vistas, los
> cuales yo también quitaría, a menos que el orden tenga importancia (que
> no me lo parece).

Claro. Bueno, yo le sugería a Víctor (que hizo parte interesante de
las uniones entre estas vistas) evitar hacer composiciones de vistas;
no se me había ocurrido este corolario... Pero, sí, saqué los ORDER BY
también.

> Creo que gran parte del problema son está en esta vista y
> people_metadata:
> (...)
> Acá te recomendaría quitar el ORDER BY, que no te está haciendo ningún
> favor. Es mejor aplicar ORDER BY al resultado final, al consultar la
> vista.

Leí hasta acá tu correo. Volví a generar las vistas sacando el ORDER
BY. Mandé de vuelta el EXPLAIN ANALIZE. Y... Mi corazón sufrió una
triste desazón por casi media hora, para encontrarme con esto:

QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------------------------
Merge Join (cost=561450.65..13000017.57 rows=710016061 width=104) (actual time=23286.968..1162335.210 rows=471044420 loops=1)
Merge Cond: (u.email = u_1.email)
Join Filter: (k.keyid <> k_1.keyid)
Rows Removed by Join Filter: 172082068
-> Sort (cost=230941.32..234107.60 rows=1266510 width=28) (actual time=14177.487..15791.991 rows=1266332 loops=1)
Sort Key: u.email
Sort Method: external merge Disk: 46904kB
-> Hash Join (cost=507.53..72262.16 rows=1266510 width=28) (actual time=111.209..9700.968 rows=1266332 loops=1)
Hash Cond: (ptu.userid_id = u.id)
-> Hash Join (cost=184.65..40276.53 rows=1266510 width=12) (actual time=41.664..7400.240 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.006..5487.610 rows=1266332 loops=1)
-> Hash (cost=143.62..143.62 rows=3282 width=12) (actual time=41.635..41.635 rows=3293 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 142kB
-> Hash Join (cost=37.67..143.62 rows=3282 width=12) (actual time=0.434..39.120 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=16) (actual time=0.004..34.232 rows=3293 loops=1)
-> Hash (cost=22.30..22.30 rows=1230 width=4) (actual time=0.403..0.403 rows=21 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 1kB
-> Seq Scan on pk_algorithm a (cost=0.00..22.30 rows=1230 width=4) (actual time=0.351..0.377 rows=21 loops=1)
-> Hash (cost=196.28..196.28 rows=10128 width=24) (actual time=69.515..69.515 rows=10141 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 557kB
-> Seq Scan on userid u (cost=0.00..196.28 rows=10128 width=24) (actual time=0.005..61.607 rows=10141 loops=1)
-> Materialize (cost=330509.32..336841.87 rows=1266510 width=96) (actual time=9109.461..382175.721 rows=643126484 loops=1)
-> Sort (cost=330509.32..333675.60 rows=1266510 width=96) (actual time=9109.450..10711.784 rows=1266332 loops=1)
Sort Key: u_1.email
Sort Method: external merge Disk: 118560kB
-> Hash Join (cost=507.53..72262.16 rows=1266510 width=96) (actual time=25.016..5006.797 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.596..2797.348 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..872.220 rows=1266332 loops=1)
-> Hash (cost=143.62..143.62 rows=3282 width=60) (actual time=9.550..9.550 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.068..6.769 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.006..2.053 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.004..0.018 rows=21 loops=1)
-> Hash (cost=196.28..196.28 rows=10128 width=44) (actual time=15.394..15.394 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.048 rows=10141 loops=1)
Planning time: 179.358 ms
Execution time: 1424295.601 ms
(45 rows)

Pero bueno, dejemos de lado a esa mounstrosidad... Porque a
continuación Álvaro demuestra que puede resolverse un problema
teniendo conocimiento de la materia incluso sin conocer la naturaleza
de la información en cuestión :-]

> De todas formas, estás ignorando name, algorithm, created_at,
> expires, comment de la original_keyid, ¿no? Me parece que esta vista
> está "perdiendo datos", y más bien lo que quisieras es el conjunto de
> todos los keyid de un mismo email:
>
> SELECT array_agg(keyid) FROM pubkey_metadata GROUP BY email;
>
> Y a continuación puedes extraer toda la info de cada llave en cada uno
> de esos arrays; (te construiría un ejemplo pero seguro que me equivoco,
> sin tener datos de prueba a mano).

¡Genial! No se nos había ocurrido utilizar arreglos. Esto nos facilita
fuertemente el análisis, y nos reduce la consulta de los treintaipico
segundos originales a quince segundos - Pero haciendo pequeños
filtrados, baja rápida y sensiblemente \o/

Creo que con esto podemos seguir trabajando. ¡A estudiar funciones
sobre arreglos!

-
Enviado a la lista de correo pgsql-es-ayuda (pgsql-es-ayuda(at)postgresql(dot)org)
Para cambiar tu suscripcin:
http://www.postgresql.org/mailpref/pgsql-es-ayuda