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

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

Tiene los siguientes índices:

INDEX coursemoduleaction: course, module, "action";
INDEX courseuserid: userid, course;
INDEX mdl_log_info_idx: info;
INDEX timecoursemoduleaction: "time", course, module, "action";

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

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

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

Lo que agradecería que me explicarais:

- ¿descartada a priori la vía particionado, cómo creéis que puedo
mejorar el tema?
- ¿por qué si utilizo un limitador LIMIT 100 la primera consulta tarda
muchísimo más, incluso con el índice "timecoursemoduleaction"? ¿Es este
índice perjudicial y culpable de la lentitud?
- ¿cómo gestiona los índices PostgreSQL? ¿En función de qué utiliza uno
u otro?
- la verdad es que no alcanzo a comprender los parámetros de salida
"cost", "rows" y el "time" desglosados... ya me perdonaréis

Uf, lo siento por el tostón que os he pegado, pero a ver si con vuestra
ayuda aprendo a andar solo con este tema.

Muchas gracias,

Eduard

El dj 07 de 06 del 2007 a les 12:05 -0400, en/na Alvaro Herrera va
escriure:

> Eduard Miquel i Gavaldà escribió:
>
> Hola,
>
> > la verdad es que te agradeceré las ideas que me puedas dar y si me
> > ahorro trabajo, pues mejor.
> >
> > Básicamente en la tabla se ejecutan inserts y selects. Se trata de una
> > tabla de logs de una aplicación. La principal utilidad es registrar las
> > acciones de los usuarios y consultarlas posteriormente, filtrando por
> > tiempo, fundamentalmente.
>
> Lo más probable es que no necesites particionado. De hecho es posible
> que el funcionamiento sea _más lento_ con particionado.
>
> > ¿Cómo crees que puedo optimizar el rendimiento? Estoy aún comprobando el
> > funcionamento de los índices de que dispongo con el EXPLAIN ANALYZE,
> > aunque no tengo muy claro su funcionamento... :-(
>
> Manda los EXPLAIN ANALYZE a ver si te los podemos explicar ... ¿Qué
> versión de Postgres estás usando?
>