Re: 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,

Deja te platico:

para un proyecto de facturación electrónica, tenía que analizar unos 26 millones de CFDI’s en formato XML y buscar información que pudiera representar en grafiquitas bonitas y de manera “instantanea” por lo cual en lugar de hacer super querys hice lo siguiente:

1) Crear un conjunto de tablas que son un cache de la información de manera dinámica, no confundir con las “materialized views” de postgres, estas son esquemas de corte para usar de manera dinámica

2) definiendo el punto 1, hice un algoritmo sencillo que le puse el nombre mamón de “cache diferencia”, con lo cual hago cortes de pocas tuplas, para ir propagando la información.

El proceso es el siguiente:

a) se toma una tabla base con un id secuencial (vg chingo de facturas) tomo muestras de 1000 en mil registros y agrupo mu query
b) si la información no existe la inserto
c) Si la información existe, leo, sumo y actualizo

Mucho del truco radica en los timestamps y cortes de tiempo que se requieran

3) para representar gráficas bonitas use la librería de charjs.org <http://charjs.org/> y propago los resultados de los caches a las estructuras que requiere la librería gráfica

4) al estar totalizadas en tablas pequeñas el resultado de los querys es rápido y de pocas tuplas

5) para tu caso específico, pensando que tu metadata puede ser obtenido de un key server, tomaría los e-mails como punto de relación entrew las llaves, sus id’s y fingerprints así como sus fechas de creación y expiración, asei como las firmas de usuarios que avalen a esta.

6) si “cacheas” tu mega query y lo fragmentas en ventanas mas pequeñas, puedes alimentar una tabla de cavche intermediaria que puede ser actualizada de manera dinámica.

7) y saco este tipo de reportes:

este es el análisis de un año de operaciones de una empresa y toma este tiempo en ejecutarse:

cfdi=# explain analyze select date_part('month', date_time) as mes,
cfdi-# Rol,
cfdi-# Tipo_Documento,
cfdi-# sum(Num_Documentos) as nd,
cfdi-# sum(subtotal)::numeric(20,2) as subtotal,
cfdi-# sum(descuento)::numeric(20,2) as descuento,
cfdi-# sum(impuesto)::numeric(20,2) as impuesto,
cfdi-# sum(total)::numeric(20,2) as total
cfdi-# from cache_month
cfdi-# where date_part('year', date_time) = '2016' and
cfdi-# rfc = ‘XXXXXXXXXXX' and
cfdi-# moneda in('USDMXN','MXN')
cfdi-# group by date_time, Rol, Tipo_Documento
cfdi-# order by date_time, rol, tipo_documento;
QUERY PLAN

--------------------------------------------------------------------------------
-------------------------------------------------------------
GroupAggregate (cost=28.86..28.94 rows=1 width=48) (actual time=0.958..1.260 r
ows=48 loops=1)
Group Key: date_time, rol, tipo_documento
-> Sort (cost=28.86..28.86 rows=2 width=48) (actual time=0.917..0.931 rows=
67 loops=1)
Sort Key: date_time, rol, tipo_documento
Sort Method: quicksort Memory: 33kB
-> Index Scan using in_cm_rfc on cache_month (cost=0.28..28.85 rows=2
width=48) (actual time=0.298..0.693 rows=67 loops=1)
Index Cond: ((rfc)::text = 'FPS5403223V7'::text)
Filter: (((moneda)::text = ANY ('{USDMXN,MXN}'::text[])) AND (dat
e_part('year'::text, date_time) = '2016'::double precision))
Rows Removed by Filter: 327
Planning time: 1.801 ms
Execution time: 1.478 ms
(11 rows)

8) Obviamente la magia la hace el cache, que un mega query a todos los documentos base hace imposible ejecutarlo.

9) analizando la metadata de tus llaves, se puede definir un criterio de cache útil, pero no se si tienes mas detalle de los campos a considerar

Saludos… El Pop

> On Mar 13, 2017, at 7:24 PM, Gunnar Wolf <gwolf(at)gwolf(dot)org> wrote:
>
> 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,
> <keyring.sql>

Gunnar Wolf escribió:

> 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.

¿Por qué las definiciones de las vistas tienen DISTINCT? Creo que eso
puede explicar gran parte del problema. Me parece que lo ideal sería
eliminar completamente el uso de DISTINCT, lo cual puede significar que
agregues restricciones adicionales en algunas tablas para asegurar
unicidad, o alguna otra modificación al esquema.

--
Álvaro Herrera https://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

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

Alvaro Herrera dijo [Tue, Mar 14, 2017 at 08:54:58AM -0300]:
> > 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.
>
> ¿Por qué las definiciones de las vistas tienen DISTINCT? Creo que eso
> puede explicar gran parte del problema. Me parece que lo ideal sería
> eliminar completamente el uso de DISTINCT, lo cual puede significar que
> agregues restricciones adicionales en algunas tablas para asegurar
> unicidad, o alguna otra modificación al esquema.

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), regeneré las tres vistas
en cuestión (people_metadata, pubkey_metadata,
pubkey_metadata_equivalence) quitando los DISTINCT, y....

keyring=> select distinct * from people_metadata_b;
ERROR: could not write block 1060433 of temporary file: No space left on device

:-(

-
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

Fernando Romo dijo [Mon, Mar 13, 2017 at 08:49:34PM -0600]:
> Deja te platico:
>
> para un proyecto de facturación electrónica, tenía que analizar unos
> 26 millones de CFDI’s en formato XML y buscar información que
> pudiera representar en grafiquitas bonitas y de manera “instantanea”
> por lo cual en lugar de hacer super querys hice lo siguiente:
>
> 1) Crear un conjunto de tablas que son un cache de la información de
> manera dinámica (...)
>
> 2) definiendo el punto 1, hice un algoritmo sencillo que le puse el
> nombre mamón de “cache diferencia”, con lo cual hago cortes de pocas
> tuplas, para ir propagando la información.
>
> El proceso es el siguiente:
> (...)
> 6) si “cacheas” tu mega query y lo fragmentas en ventanas mas
> pequeñas, puedes alimentar una tabla de cavche intermediaria que
> puede ser actualizada de manera dinámica.
>
> 7) y saco este tipo de reportes:

Sí, platicaba con Víctor, y esta era una de las sugerencias originales
que teníamos. Pero bueno... Me conoces, soy terco y purista... E
insistí en que busquemos una alternativa más limpia y que no lleve a
desfasar la realidad y los datos. Pero dada la naturaleza de nuestra
información... no suena descabellado hacer un corte diario y
actualizar nuestros cachés.

En fin, me mantengo al pendiente en caso de haber alguna otra
recomendación por esta vía. ¡Gracias!

Gunnar Wolf escribió:

> 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 ...

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

Creo que gran parte del problema son está en esta vista y
people_metadata:

CREATE OR REPLACE VIEW pubkey_metadata_equivalence AS
SELECT DISTINCT pkm1.keyid AS original_keyid,
pkm2.keyid,
pkm2.name,
pkm2.email,
pkm2.algorithm,
pkm2.created_at,
pkm2.expires,
pkm2.comment
FROM pubkey_metadata pkm1
JOIN pubkey_metadata pkm2 ON pkm1.email = pkm2.email
ORDER BY pkm1.keyid;

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

--
Álvaro Herrera https://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

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

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

Gunnar Wolf escribió:

> > 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!

Creo que lo que más te puede ayudar en este caso es "scalar op array
expression", donde tienes un valor escalar, un operador, y un array.
No sé si hay más casos que estos dos:

escalar = ANY (array)
escalar <> ALL (array)
el operador puede ser cualquier cosa, no solo = o <>;
el escalar puede ser una columna de una tabla.

La primera retorna TRUE si cualquier elemento del array es igual al
escalar. La segunda retorna TRUE si todos los elementos del array son
<> al escalar.

El "scalar op array expr" es un caso específicamente optimizado --
particularmente con btrees.

¡Saludos!

--
Álvaro Herrera https://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

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

Alvaro Herrera dijo [Tue, Mar 14, 2017 at 04:07:35PM -0300]:
> > Creo que con esto podemos seguir trabajando. ¡A estudiar funciones
> > sobre arreglos!
>
> Creo que lo que más te puede ayudar en este caso es "scalar op array
> expression", donde tienes un valor escalar, un operador, y un array.
> No sé si hay más casos que estos dos:
>
> escalar = ANY (array)
> escalar <> ALL (array)
> el operador puede ser cualquier cosa, no solo = o <>;
> el escalar puede ser una columna de una tabla.
>
> La primera retorna TRUE si cualquier elemento del array es igual al
> escalar. La segunda retorna TRUE si todos los elementos del array son
> <> al escalar.
>
> El "scalar op array expr" es un caso específicamente optimizado --
> particularmente con btrees.

OK, si está específicamente optimizado, buscaré utilizarlo. Por ahora,
hice un par de consultas (que me dejaron satisfecho) especificando mi
escalar explícitamente como arreglo. Obviamente sucio, porque sólo
estoy jugando:

SELECT * FROM (
SELECT email, array_agg(show_keyid(keyid)) AS keys,
cardinality(array_agg(show_keyid(keyid))) as numkeys
FROM pubkey_metadata GROUP BY email) AS set
WHERE keys && array['673a03e4c1db921f'];

SELECT * FROM (
SELECT email, array_agg(show_keyid(keyid)) AS keys,
cardinality(array_agg(show_keyid(keyid))) as numkeys
FROM pubkey_metadata GROUP BY email) AS set
WHERE '673a03e4c1db921f' = ANY(keys);

Comparando sus query plans y tiempos de ejecución me parecen casi
sinónimos. Pero, sí, la versión con ANY resulta un poco más legible y
clara para el humano, que no es poca cosa :)

(¿que por qué usé un subquery para algo tan trivial como esto? Porque
si no, el ANY(keys) se quejaba de que keys no existía, y no puedo
incluir una función en el WHERE).

-
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

Gunnar Wolf escribió:

> SELECT * FROM (
> SELECT email, array_agg(show_keyid(keyid)) AS keys,
> cardinality(array_agg(show_keyid(keyid))) as numkeys
> FROM pubkey_metadata GROUP BY email) AS set
> WHERE '673a03e4c1db921f' = ANY(keys);
>
> Comparando sus query plans y tiempos de ejecución me parecen casi
> sinónimos. Pero, sí, la versión con ANY resulta un poco más legible y
> clara para el humano, que no es poca cosa :)
>
> (¿que por qué usé un subquery para algo tan trivial como esto? Porque
> si no, el ANY(keys) se quejaba de que keys no existía, y no puedo
> incluir una función en el WHERE).

Ah, claro. Creo que puedes escribirlo más elegante así:

SELECT email,
array_agg(show_keyid(keyid)) AS keys,
cardinality(array_agg(show_keyid(keyid))) as numkeys
FROM pubkey_metadata
GROUP BY email
HAVING '673a03e4c1db921f' = ANY(array_agg(show_keyid(keyid)));

WHERE aplica a los registros de entrada a la agrupación, antes de ser
agrupados (por eso no puedes poner la función ahí); HAVING aplica
después de la agrupación.

--
Álvaro Herrera https://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

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