Re: [pgsql-es-ayuda] Migración de datosatablaparticionada

Buenas lista,
OK Álvaro, probaré las recomendaciones que me das. En todo caso, el
diseño me viene ya dado, ya que como dice Sebastian, se trata de la
tabla de logs de Moodle. Con lo que, de momento, he partido del diseño
predeterminado.

Empecemos a retocar cositas y a ver si obtengo unos mejores resultados.

Muchas gracias, os comento cómo va el asunto.

Eduard

El dv 08 de 06 del 2007 a les 09:41 -0400, en/na Alvaro Herrera va
escriure:

> Eduard Miquel i Gavaldà escribió:
> > Hola,
> > ahí va la descripción de la tabla y un par de EXPLAIN ANALYZE de
> > consultas habituales.
> >
> > CREATE TABLE mdl_log
> > (
> > id serial NOT NULL,
> > "time" int4 NOT NULL DEFAULT 0,
> > userid int4 NOT NULL DEFAULT 0,
> > ip varchar(15) NOT NULL DEFAULT ''::character varying,
> > course int4 NOT NULL DEFAULT 0,
> > module varchar(20) NOT NULL DEFAULT ''::character varying,
> > cmid int4 NOT NULL DEFAULT 0,
> > "action" varchar(15) NOT NULL DEFAULT ''::character varying,
> > url varchar(100) NOT NULL DEFAULT ''::character varying,
> > info varchar(255) NOT NULL DEFAULT ''::character varying,
> > CONSTRAINT mdl_log_pkey PRIMARY KEY (id),
> > CONSTRAINT mdl_log_cmid_check CHECK (cmid >= 0),
> > CONSTRAINT mdl_log_course_check CHECK (course >= 0),
> > CONSTRAINT mdl_log_time_check CHECK ("time" >= 0),
> > CONSTRAINT mdl_log_userid_check CHECK (userid >= 0)
> > )
>
> Ok, lo mas obvio aqui es que los tipos de datos son bastante
> inadecuados. Por ejemplo el usar "time" como un entero. Por que no
> usar un campo "timestamp without time zone" mejor? Asi te ahorras la
> conversion con date_part(epoch) y todo eso. La gracia que tiene hacerlo
> de esa forma, es que el ejecutor puede estar mas al tanto de lo que
> significa y hacer predicciones de selectividad de los indices con mayor
> probabilidad de exito.
>
> Lo otro es "ip varchar". Usar tipo INET quizas.
>
> Eso de poner "DEFAULT 0" o DEFAULT '' es bastante poco feliz tambien
> (tengo entendido que es una costumbre extendida entre los usuarios de
> MySQL. No me queda claro el proposito. Te recomiendo deshacerte de
> ella)
>
> > INDEX coursemoduleaction: course, module, "action";
> > INDEX courseuserid: userid, course;
> > INDEX mdl_log_info_idx: info;
> > INDEX timecoursemoduleaction: "time", course, module, "action";
>
> Tus indices son algo redundantes. Dependiendo de la selectividad y de
> la version de Postgres que estes usando te puede convenir usar estos
> otros indices:
>
> index: course
> index: module
> index: userid
> index: info
> index: "time"
> index: action
>
> El punto es que Postgres (a partir de la version 8.1) puede usar mas de
> un indice en un mismo recorrido, y en cada recorrido puede tomar el que
> sea mas conveniente. Pero ojo, que si tienes indices que usen campos de
> muy baja selectividad (por ejemplo si los valores distintos de "action"
> no fueran mas que una docena), entonces no vale la pena tener el indice
> (probablemente nunca se usará)
>
> > La primera consulta filtra a partir de una fecha determinada:
> >
> > explain analyze
> > select * from mdl_log where time > round(date_part('epoch', timestamp
> > '2007-01-30 00:00:00')) order by time asc
> >
> > QUERY PLAN
> > Sort (cost=2144506.77..2158099.67 rows=5437160 width=83) (actual
> > time=52324.373..60723.319 rows=4751857 loops=1)
> > Sort Key: "time"
> > -> Seq Scan on mdl_log (cost=0.00..495522.22 rows=5437160 width=83)
> > (actual time=5.056..41224.555 rows=4751857 loops=1)
> > Filter: (("time")::double precision > 1170111600::double precision)
> > Total runtime: 62412.659 ms
>
> Aca el asunto es que como tiene que retornar casi toda la tabla (la
> mitad), sabe que es mas conveniente no usar el indice. No estoy seguro
> el punto de quiebre pero creo que cuando es mas de alrededor de 5% de la
> tabla lo que hay que recorrer, es mejor hacer un seqscan que un
> indexscan.
>
> Ahora, realmente quieres obtener las 5 millones de tuplas que tienen
> fecha posterior al 30 de enero de este año? No te bastaria con, por
> ejemplo, las primeras 10000 entradas? Porque en ese caso aplica lo de
> abajo:
>
> > A partir de esta misma consulta, si utilizo la clásusula LIMIT 100 da el
> > seguiente resultado:
> >
> > QUERY PLAN
> > Limit (cost=0.00..523.01 rows=100 width=83) (actual
> > time=185149.917..185150.125 rows=100 loops=1)
> > -> Index Scan using timecoursemoduleaction on mdl_log
> > (cost=0.00..28436911.33 rows=5437160 width=83) (actual
> > time=185149.913..185150.039 rows=100 loops=1)
> > Filter: (("time")::double precision > 1170111600::double precision)
> > Total runtime: 185150.204 ms
>
> Aca sabe que tiene que devolver pocos valores asi que prefiere usar un
> indice. Esto deberia ser muy rapido ... pero lo patetico es que dado
> que no sabe mucho de las propiedades de la columna "time", tiene que
> recorrer el indice completo de todas maneras. Si time fuera una columna
> timestamp esto seria mucho mas rapido.
>
>
> > La segunda consulta utiliza un filtro un poco más complejo:
> >
> > explain analyze
> > select * from mdl_log
> > where time > round(date_part('epoch', timestamp '2007-01-30 00:00:00'))
> > and course = '5020' and module = 'course' and action like 'view%' order
> > by time asc;
> >
> > QUERY PLAN
> > Sort (cost=17736.54..17739.83 rows=1318 width=83) (actual
> > time=59.422..60.643 rows=2744 loops=1)
> > Sort Key: "time"
> > -> Index Scan using coursemoduleaction on mdl_log (cost=0.00..17668.24
> > rows=1318 width=83) (actual time=1.353..55.694 rows=2744 loops=1)
> > Index Cond: ((course = 5020) AND ((module)::text = 'course'::text))
> > Filter: ((("time")::double precision > 1170111600::double precision) AND
> > (("action")::text ~~ 'view%'::text))
> > Total runtime: 61.665 ms
>
> Me parece que esto seria una consulta muy rapida con los indices por
> cada columna.
>
>
> Que es eso de "action like 'view%"? Que clase de valores almacenas en
> "action"? Idem para "module" Suena sospechosamente inapropiado ...
>
> Otra pregunta: para que tienes un "id" en una tabla de log?? Suena
> bastante inutil.
>