Re: Migración de datos atablaparticionada

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.

--
Alvaro Herrera http://www.advogato.org/person/alvherre
"The important things in the world are problems with society that we don't
understand at all. The machines will become more complicated but they won't
be more complicated than the societies that run them." (Freeman Dyson)