Re: Encontrar una manera menos intensiva de hacer una consulta

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>