Agilizar consultas sobre fechas

Saludos a todos.

Tengo la siguiente consulta:

select vd.cantidad,vd.precio_venta_unidad_venta,vd.id_esquema_impuesto
from ventas_detalle vd, ventas v, articulos a
where v.id_sucursal=sucursal and
vd.id_sucursal=v.id_sucursal and
vd.id_venta=v.id_venta and
cast(v.fecha as date) = fecha_revision and
vd.id_articulo = a.id_articulo and
a.servicio is null

La estructura de las tablas es la siguiente:

CREATE TABLE ventas
(
id_venta serial NOT NULL,
id_corte_caja bigint NOT NULL,
id_sucursal smallint NOT NULL,
id_concepto smallint NOT NULL,
numero_caja smallint NOT NULL,
folio_venta bigint,
folio_devolucion bigint,
fecha timestamp without time zone NOT NULL,
id_estatus smallint NOT NULL,
devuelto boolean,
sincronizado integer DEFAULT 0,
CONSTRAINT ventas_pkey PRIMARY KEY (id_venta, id_corte_caja,
id_sucursal, numero_caja),
CONSTRAINT ventas_fk_conceptos FOREIGN KEY (id_concepto)
REFERENCES conceptos (id_concepto) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION,
CONSTRAINT ventas_fk_corte_caja FOREIGN KEY (id_corte_caja,
id_sucursal, numero_caja)
REFERENCES corte_caja (id_corte_caja, id_sucursal, numero_caja)
MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION,
CONSTRAINT ventas_fk_estatus FOREIGN KEY (id_estatus)
REFERENCES estatus (id_estatus) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION
)
WITHOUT OIDS;
ALTER TABLE ventas OWNER TO yepas;

-- Index: ventas_id_sucursal_index

-- DROP INDEX ventas_id_sucursal_index;

CREATE INDEX ventas_id_sucursal_index
ON ventas
USING btree
(id_sucursal);

-- Index: ventas_id_venta_index

-- DROP INDEX ventas_id_venta_index;

CREATE INDEX ventas_id_venta_index
ON ventas
USING btree
(id_venta);

CREATE TABLE ventas_detalle
(
id_venta bigint NOT NULL,
id_sucursal smallint NOT NULL,
id_corte_caja bigint NOT NULL,
numero_caja smallint NOT NULL,
id_venta_detalle serial NOT NULL,
id_esquema_impuesto smallint NOT NULL,
id_esquema_oferta smallint NOT NULL,
cantidad numeric(12,3),
precio_venta_unidad_venta numeric(12,3),
tipo_precio text,
precio_regular_unidad_venta numeric(12,3),
precio_chequeo_unidad_venta numeric(12,3),
precio_oferta_unidad_venta numeric(12,3),
kit text,
id_kit smallint,
factor numeric(12,3),
costo_unitario_promedio numeric(12,3),
cantidad_devuelta numeric(12,3),
id_articulo bigint NOT NULL,
CONSTRAINT ventas_detalle_pkey PRIMARY KEY (id_venta, id_sucursal,
id_corte_caja, numero_caja, id_venta_detalle),
CONSTRAINT esquema_ofertas_fk_ofertas FOREIGN KEY (id_esquema_oferta)
REFERENCES esquema_ofertas (id_esquema_oferta) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION,
CONSTRAINT venta_detalle_fk_esquema_impuesto FOREIGN KEY
(id_esquema_impuesto)
REFERENCES esquema_impuestos (id_esquema_impuesto) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION,
CONSTRAINT ventas_detalle_fk_articulos FOREIGN KEY (id_articulo)
REFERENCES articulos (id_articulo) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION,
CONSTRAINT ventas_detalle_fk_kits FOREIGN KEY (id_kit)
REFERENCES kits (id_kit) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION,
CONSTRAINT ventas_detalle_fk_ventas FOREIGN KEY (id_venta,
id_corte_caja, numero_caja, id_sucursal)
REFERENCES ventas (id_venta, id_corte_caja, numero_caja,
id_sucursal) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION
)
WITHOUT OIDS;
ALTER TABLE ventas_detalle OWNER TO yepas;

-- Index: ix_id_esquema_oferta

-- DROP INDEX ix_id_esquema_oferta;

CREATE INDEX ix_id_esquema_oferta
ON ventas_detalle
USING btree
(id_esquema_oferta);

-- Index: ventas_detalle_id_sucursal_index

-- DROP INDEX ventas_detalle_id_sucursal_index;

CREATE INDEX ventas_detalle_id_sucursal_index
ON ventas_detalle
USING btree
(id_sucursal);

-- Index: ventas_detalle_id_ventas_index

-- DROP INDEX ventas_detalle_id_ventas_index;

CREATE INDEX ventas_detalle_id_ventas_index
ON ventas_detalle
USING btree
(id_venta);

El problema es el tiempo de respuesta de la consulta la tabla ventas
tiene alrededor de 36 millones de registros y la de venta detalle cerca
de 76 millones.Y para una sucursal y un día en específico tarda entre 30
a 40 minutos. ¿Alguna sugerencia o tip para mejorar la consulta?

--
Atentamente.

Manuel Alejandro Estévez Fernández

2009/10/1 Manuel Alejandro Estevez Fernandez <stvzito(at)gmail(dot)com>:
> Saludos a todos.
>
> Tengo la siguiente consulta:
>
> select vd.cantidad,vd.precio_venta_unidad_venta,vd.id_esquema_impuesto
>                        from ventas_detalle  vd, ventas v, articulos a
>                        where     v.id_sucursal=sucursal and
>                                vd.id_sucursal=v.id_sucursal and
>                                vd.id_venta=v.id_venta and
>                                cast(v.fecha as date) = fecha_revision  and
>                                vd.id_articulo = a.id_articulo and
>                                a.servicio is null
>
> La estructura de las tablas es la siguiente:
>
> CREATE TABLE ventas
> (
>  id_venta serial NOT NULL,
>  id_corte_caja bigint NOT NULL,
>  id_sucursal smallint NOT NULL,
>  id_concepto smallint NOT NULL,
>  numero_caja smallint NOT NULL,
>  folio_venta bigint,
>  folio_devolucion bigint,
>  fecha timestamp without time zone NOT NULL,
>  id_estatus smallint NOT NULL,
>  devuelto boolean,
>  sincronizado integer DEFAULT 0,
>  CONSTRAINT ventas_pkey PRIMARY KEY (id_venta, id_corte_caja, id_sucursal,
> numero_caja),
>  CONSTRAINT ventas_fk_conceptos FOREIGN KEY (id_concepto)
>      REFERENCES conceptos (id_concepto) MATCH SIMPLE
>      ON UPDATE NO ACTION ON DELETE NO ACTION,
>  CONSTRAINT ventas_fk_corte_caja FOREIGN KEY (id_corte_caja, id_sucursal,
> numero_caja)
>      REFERENCES corte_caja (id_corte_caja, id_sucursal, numero_caja) MATCH
> SIMPLE
>      ON UPDATE NO ACTION ON DELETE NO ACTION,
>  CONSTRAINT ventas_fk_estatus FOREIGN KEY (id_estatus)
>      REFERENCES estatus (id_estatus) MATCH SIMPLE
>      ON UPDATE NO ACTION ON DELETE NO ACTION
> )
> WITHOUT OIDS;
> ALTER TABLE ventas OWNER TO yepas;
>
> -- Index: ventas_id_sucursal_index
>
> -- DROP INDEX ventas_id_sucursal_index;
>
> CREATE INDEX ventas_id_sucursal_index
>  ON ventas
>  USING btree
>  (id_sucursal);
>
> -- Index: ventas_id_venta_index
>
> -- DROP INDEX ventas_id_venta_index;
>
> CREATE INDEX ventas_id_venta_index
>  ON ventas
>  USING btree
>  (id_venta);
>
> CREATE TABLE ventas_detalle
> (
>  id_venta bigint NOT NULL,
>  id_sucursal smallint NOT NULL,
>  id_corte_caja bigint NOT NULL,
>  numero_caja smallint NOT NULL,
>  id_venta_detalle serial NOT NULL,
>  id_esquema_impuesto smallint NOT NULL,
>  id_esquema_oferta smallint NOT NULL,
>  cantidad numeric(12,3),
>  precio_venta_unidad_venta numeric(12,3),
>  tipo_precio text,
>  precio_regular_unidad_venta numeric(12,3),
>  precio_chequeo_unidad_venta numeric(12,3),
>  precio_oferta_unidad_venta numeric(12,3),
>  kit text,
>  id_kit smallint,
>  factor numeric(12,3),
>  costo_unitario_promedio numeric(12,3),
>  cantidad_devuelta numeric(12,3),
>  id_articulo bigint NOT NULL,
>  CONSTRAINT ventas_detalle_pkey PRIMARY KEY (id_venta, id_sucursal,
> id_corte_caja, numero_caja, id_venta_detalle),
>  CONSTRAINT esquema_ofertas_fk_ofertas FOREIGN KEY (id_esquema_oferta)
>      REFERENCES esquema_ofertas (id_esquema_oferta) MATCH SIMPLE
>      ON UPDATE NO ACTION ON DELETE NO ACTION,
>  CONSTRAINT venta_detalle_fk_esquema_impuesto FOREIGN KEY
> (id_esquema_impuesto)
>      REFERENCES esquema_impuestos (id_esquema_impuesto) MATCH SIMPLE
>      ON UPDATE NO ACTION ON DELETE NO ACTION,
>  CONSTRAINT ventas_detalle_fk_articulos FOREIGN KEY (id_articulo)
>      REFERENCES articulos (id_articulo) MATCH SIMPLE
>      ON UPDATE NO ACTION ON DELETE NO ACTION,
>  CONSTRAINT ventas_detalle_fk_kits FOREIGN KEY (id_kit)
>      REFERENCES kits (id_kit) MATCH SIMPLE
>      ON UPDATE NO ACTION ON DELETE NO ACTION,
>  CONSTRAINT ventas_detalle_fk_ventas FOREIGN KEY (id_venta, id_corte_caja,
> numero_caja, id_sucursal)
>      REFERENCES ventas (id_venta, id_corte_caja, numero_caja, id_sucursal)
> MATCH SIMPLE
>      ON UPDATE NO ACTION ON DELETE NO ACTION
> )
> WITHOUT OIDS;
> ALTER TABLE ventas_detalle OWNER TO yepas;
>
> -- Index: ix_id_esquema_oferta
>
> -- DROP INDEX ix_id_esquema_oferta;
>
> CREATE INDEX ix_id_esquema_oferta
>  ON ventas_detalle
>  USING btree
>  (id_esquema_oferta);
>
> -- Index: ventas_detalle_id_sucursal_index
>
> -- DROP INDEX ventas_detalle_id_sucursal_index;
>
> CREATE INDEX ventas_detalle_id_sucursal_index
>  ON ventas_detalle
>  USING btree
>  (id_sucursal);
>
> -- Index: ventas_detalle_id_ventas_index
>
> -- DROP INDEX ventas_detalle_id_ventas_index;
>
> CREATE INDEX ventas_detalle_id_ventas_index
>  ON ventas_detalle
>  USING btree
>  (id_venta);
>
> El problema es el tiempo de respuesta de la consulta la tabla ventas tiene
> alrededor de 36 millones de registros y la de venta detalle cerca de 76
> millones.Y para una sucursal y un día en específico tarda entre 30 a 40
> minutos. ¿Alguna sugerencia o tip para mejorar la consulta?
>
>
> --
> Atentamente.
>
> Manuel Alejandro Estévez Fernández
>
> --
> TIP 5: ¿Has leído nuestro extenso FAQ?
>        http://www.postgresql.org/docs/faqs.FAQ.html
>

Por favor envía el explain de la consulta.
También podrías actualizar las estadísticas con un analyze.

Y me parece que como no obtienes ni un campo de la tabla
de artículos podrías sacarla de la consulta.

--
§~^Calabaza^~§ from Villa Elisa, Paraguay
http://calablogbaza.blogspot.com/

http://es.wikipedia.org/wiki/Top-posting
http://es.wikipedia.org/wiki/Netiquette

On Thu, Oct 1, 2009 at 10:34 AM, Manuel Alejandro Estevez Fernandez
<stvzito(at)gmail(dot)com> wrote:
> Saludos a todos.
>
> Tengo la siguiente consulta:
>
> select vd.cantidad,vd.precio_venta_unidad_venta,vd.id_esquema_impuesto
>                        from ventas_detalle  vd, ventas v, articulos a
>                        where     v.id_sucursal=sucursal and
>                                vd.id_sucursal=v.id_sucursal and
>                                vd.id_venta=v.id_venta and
>                                cast(v.fecha as date) = fecha_revision  and
>                                vd.id_articulo = a.id_articulo and
>                                a.servicio is null
>

un explain analyze ayudaria bastante a saber que esta pasando pero es
obvio que la consulta esta retornando un producto cartesiano, fijate
que la relacion entre ventas_detalle y ventas es: id_venta,
id_corte_caja, numero_caja, id_sucursal y tu solo estas usando dos de
esos en el JOIN

--
Atentamente,
Jaime Casanova
Soporte y capacitación de PostgreSQL
Asesoría y desarrollo de sistemas
Guayaquil - Ecuador
Cel. +59387171157

El 01/10/09 12:13, Jaime Casanova escribió:
> On Thu, Oct 1, 2009 at 10:34 AM, Manuel Alejandro Estevez Fernandez
> <stvzito(at)gmail(dot)com> wrote:
>
>> Saludos a todos.
>>
>> Tengo la siguiente consulta:
>>
>> select vd.cantidad,vd.precio_venta_unidad_venta,vd.id_esquema_impuesto
>> from ventas_detalle vd, ventas v, articulos a
>> where v.id_sucursal=sucursal and
>> vd.id_sucursal=v.id_sucursal and
>> vd.id_venta=v.id_venta and
>> cast(v.fecha as date) = fecha_revision and
>> vd.id_articulo = a.id_articulo and
>> a.servicio is null
>>
>>
> un explain analyze ayudaria bastante a saber que esta pasando pero es
> obvio que la consulta esta retornando un producto cartesiano, fijate
> que la relacion entre ventas_detalle y ventas es: id_venta,
> id_corte_caja, numero_caja, id_sucursal y tu solo estas usando dos de
> esos en el JOIN
>
>
Si me di cuenta, bueno aqui la consulta corregida y el analize

select vd.cantidad,vd.precio_venta_unidad_venta,vd.id_esquema_impuesto
from ventas_detalle vd, ventas v, articulos a
where v.id_sucursal=11 and
vd.id_sucursal=v.id_sucursal and
vd.id_venta=v.id_venta and
v.numero_caja = vd.numero_caja and
v.id_corte_caja=vd.id_corte_caja and
cast(v.fecha as date) = '2009-08-13' and
vd.id_articulo = a.id_articulo and
a.id_servicio is null

"Nested Loop (cost=0.00..9.08 rows=1 width=23)"
" -> Nested Loop (cost=0.00..6.05 rows=1 width=31)"
" Join Filter: (("outer".id_venta = "inner".id_venta) AND
("inner".numero_caja = "outer".numero_caja) AND ("inner".id_corte_caja =
"outer".id_corte_caja))"
" -> Index Scan using ventas_detalle_id_sucursal_index on
ventas_detalle vd (cost=0.00..3.01 rows=1 width=51)"
" Index Cond: (id_sucursal = 11)"
" -> Index Scan using ventas_id_sucursal_index on ventas v
(cost=0.00..3.02 rows=1 width=16)"
" Index Cond: (id_sucursal = 11)"
" Filter: ((fecha)::date = '2009-08-13'::date)"
" -> Index Scan using articulos_pkey on articulos a (cost=0.00..3.01
rows=1 width=4)"
" Index Cond: ("outer".id_articulo = a.id_articulo)"
" Filter: (id_servicio IS NULL)"

Saludos.

--
Atentamente.

Manuel Alejandro Estévez Fernández

Manuel Alejandro Estevez Fernandez escribió:

> Si me di cuenta, bueno aqui la consulta corregida y el analize

Eso es un EXPLAIN solo, sin ANALYZE. Por favor muestra un EXPLAIN
ANALYZE.

--
Alvaro Herrera Developer, http://www.PostgreSQL.org/
"La persona que no quería pecar / estaba obligada a sentarse
en duras y empinadas sillas / desprovistas, por cierto
de blandos atenuantes" (Patricio Vogel)

2009/10/1 Manuel Alejandro Estevez Fernandez <stvzito(at)gmail(dot)com>:
>
> "Nested Loop  (cost=0.00..9.08 rows=1 width=23)"

segun el EXPLAIN esta consulta retorna un registro casi desde el
primer filtro y el planeador la considera de costo bajisimo...
esta misma consulta que pasaste, en esa fecha especifica:
- en realidad regresa solo una fila?
- demora 30 a 40 minutos como dijiste antes?

si la respuesta a la primero pregunta es NO entonces probablemente
necesites ejecutar ANALYZE a las tablas que intervienen... a todo
esto, que version de postgres estas usando?

si en realidad regresa una sola fila y se demora tanto pasa el EXPLAIN ANALYZE

--
Atentamente,
Jaime Casanova
Soporte y capacitación de PostgreSQL
Asesoría y desarrollo de sistemas
Guayaquil - Ecuador
Cel. +59387171157

El 01/10/09 14:46, Jaime Casanova escribió:
> 2009/10/1 Manuel Alejandro Estevez Fernandez<stvzito(at)gmail(dot)com>:
>
>> "Nested Loop (cost=0.00..9.08 rows=1 width=23)"
>>
> segun el EXPLAIN esta consulta retorna un registro casi desde el
> primer filtro y el planeador la considera de costo bajisimo...
> esta misma consulta que pasaste, en esa fecha especifica:
> - en realidad regresa solo una fila?
> - demora 30 a 40 minutos como dijiste antes?
>
> si la respuesta a la primero pregunta es NO entonces probablemente
> necesites ejecutar ANALYZE a las tablas que intervienen... a todo
> esto, que version de postgres estas usando?
>
> si en realidad regresa una sola fila y se demora tanto pasa el EXPLAIN ANALYZE
>
>
La versión de postgresql del servidor es la 8.1.11.el5_1.1.x86_64.
Espero haberlo hecho bien esta vez. Corrí la consulta sobre una base de
pruebas que tengo y tiene muchos menos registros,por que en estos
momentos no tengo acceso a la otra ,el resultado del analyze fue el
siguiente:

"Nested Loop (cost=4.73..14.14 rows=1 width=17) (actual
time=88.309..115.750 rows=50 loops=1)"
" -> Hash Join (cost=4.73..11.20 rows=1 width=25) (actual
time=59.478..60.942 rows=50 loops=1)"
" Hash Cond: ((vd.id_venta = v.id_venta) AND (vd.numero_caja =
v.numero_caja) AND (vd.id_corte_caja = v.id_corte_caja))"
" -> Seq Scan on ventas_detalle vd (cost=0.00..4.94 rows=135
width=45) (actual time=57.870..58.568 rows=135 loops=1)"
" Filter: (id_sucursal = 11)"
" -> Hash (cost=4.71..4.71 rows=1 width=16) (actual
time=1.307..1.307 rows=31 loops=1)"
" -> Seq Scan on ventas v (cost=0.00..4.71 rows=1
width=16) (actual time=0.675..1.163 rows=31 loops=1)"
" Filter: ((id_sucursal = 11) AND ((fecha)::date =
'2009-08-13'::date))"
" -> Index Scan using articulos_pkey on articulos a (cost=0.00..2.93
rows=1 width=4) (actual time=1.078..1.083 rows=1 loops=50)"
" Index Cond: (a.id_articulo = vd.id_articulo)"
" Filter: (a.id_servicio IS NULL)"
"Total runtime: 117.070 ms"

--
Atentamente.

Manuel Alejandro Estévez Fernández

Manuel Alejandro Estevez Fernandez escribió:

> La versión de postgresql del servidor es la 8.1.11.el5_1.1.x86_64.
> Espero haberlo hecho bien esta vez. Corrí la consulta sobre una base
> de pruebas que tengo y tiene muchos menos registros,por que en estos
> momentos no tengo acceso a la otra ,el resultado del analyze fue el
> siguiente:
>
> "Nested Loop (cost=4.73..14.14 rows=1 width=17) (actual
> time=88.309..115.750 rows=50 loops=1)"
> " -> Hash Join (cost=4.73..11.20 rows=1 width=25) (actual
> time=59.478..60.942 rows=50 loops=1)"
> " Hash Cond: ((vd.id_venta = v.id_venta) AND (vd.numero_caja
> = v.numero_caja) AND (vd.id_corte_caja = v.id_corte_caja))"
> " -> Seq Scan on ventas_detalle vd (cost=0.00..4.94
> rows=135 width=45) (actual time=57.870..58.568 rows=135 loops=1)"
> " Filter: (id_sucursal = 11)"
> " -> Hash (cost=4.71..4.71 rows=1 width=16) (actual
> time=1.307..1.307 rows=31 loops=1)"
> " -> Seq Scan on ventas v (cost=0.00..4.71 rows=1
> width=16) (actual time=0.675..1.163 rows=31 loops=1)"
> " Filter: ((id_sucursal = 11) AND ((fecha)::date
> = '2009-08-13'::date))"
> " -> Index Scan using articulos_pkey on articulos a
> (cost=0.00..2.93 rows=1 width=4) (actual time=1.078..1.083 rows=1
> loops=50)"
> " Index Cond: (a.id_articulo = vd.id_articulo)"
> " Filter: (a.id_servicio IS NULL)"
> "Total runtime: 117.070 ms"

Eso retorna en 117 milisegundos, lo cual parece razonable (si bien un
poco lento, pero no parece haber nada terrible aquí). Los resultados en
la base de verdad deberían ser distintos, y es ahí donde es interesante
hacer análisis.

Tener una base de datos de prueba que no tiene más o menos la misma
cantidad de datos que la base de datos grande (o al menos una cantidad
importante de datos) no es muy buena idea, porque planes de ejecución
son muy distintos y no puedes sacar conclusiones respecto de qué tan
buenos serán los planes cuando pongas la consulta en producción.

--
Alvaro Herrera http://planet.postgresql.org/
"La principal característica humana es la tontería"
(Augusto Monterroso)

>
> Eso retorna en 117 milisegundos, lo cual parece razonable (si bien un
> poco lento, pero no parece haber nada terrible aquí). Los resultados en
> la base de verdad deberían ser distintos, y es ahí donde es interesante
> hacer análisis.
>
> Tener una base de datos de prueba que no tiene más o menos la misma
> cantidad de datos que la base de datos grande (o al menos una cantidad
> importante de datos) no es muy buena idea, porque planes de ejecución
> son muy distintos y no puedes sacar conclusiones respecto de qué tan
> buenos serán los planes cuando pongas la consulta en producción.
>
>
Gracias por tus comentarios Alvaro, agradezco que te tomes la molestía
de leer y responder.

Bueno aqui es el analize sobre la base de datos grande:

"Nested Loop (cost=0.00..9.11 rows=1 width=23) (actual
time=2496.067..1308257.999 rows=1231 loops=1)"
" -> Nested Loop (cost=0.00..6.08 rows=1 width=31) (actual
time=2496.033..1308230.160 rows=1231 loops=1)"
" Join Filter: (("inner".id_venta = "outer".id_venta) AND
("outer".numero_caja = "inner".numero_caja) AND ("outer".id_corte_caja =
"inner".id_corte_caja))"
" -> Index Scan using ventas_id_sucursal_index on ventas v
(cost=0.00..3.02 rows=1 width=16) (actual time=600.848..626.010 rows=673
loops=1)"
" Index Cond: (id_sucursal = 11)"
" Filter: ((fecha)::date = '2009-08-13'::date)"
" -> Index Scan using ventas_detalle_id_sucursal_index on
ventas_detalle vd (cost=0.00..3.03 rows=2 width=51) (actual
time=0.026..1312.527 rows=1641934 loops=673)"
" Index Cond: (id_sucursal = 11)"
" -> Index Scan using articulos_pkey on articulos a (cost=0.00..3.01
rows=1 width=4) (actual time=0.017..0.018 rows=1 loops=1231)"
" Index Cond: ("outer".id_articulo = a.id_articulo)"
" Filter: (id_servicio IS NULL)"
"Total runtime: 1308259.159 ms"

Espero sus comentarios.

--
Atentamente.

Manuel Alejandro Estévez Fernández

El día 3 de octubre de 2009 13:40, Manuel Alejandro Estevez Fernandez
<stvzito(at)gmail(dot)com> escribió:
>>
>> Eso retorna en 117 milisegundos, lo cual parece razonable (si bien un
>> poco lento, pero no parece haber nada terrible aquí).  Los resultados en
>> la base de verdad deberían ser distintos, y es ahí donde es interesante
>> hacer análisis.
>>
>> Tener una base de datos de prueba que no tiene más o menos la misma
>> cantidad de datos que la base de datos grande (o al menos una cantidad
>> importante de datos) no es muy buena idea, porque planes de ejecución
>> son muy distintos y no puedes sacar conclusiones respecto de qué tan
>> buenos serán los planes cuando pongas la consulta en producción.
>>
>>
>
> Gracias por tus comentarios Alvaro, agradezco que te tomes la molestía de
> leer y responder.
>
> Bueno aqui es el analize sobre la base de datos grande:
>
> "Nested Loop  (cost=0.00..9.11 rows=1 width=23) (actual
> time=2496.067..1308257.999 rows=1231 loops=1)"
> "  ->  Nested Loop  (cost=0.00..6.08 rows=1 width=31) (actual
> time=2496.033..1308230.160 rows=1231 loops=1)"
> "        Join Filter: (("inner".id_venta = "outer".id_venta) AND
> ("outer".numero_caja = "inner".numero_caja) AND ("outer".id_corte_caja =
> "inner".id_corte_caja))"
> "        ->  Index Scan using ventas_id_sucursal_index on ventas v
>  (cost=0.00..3.02 rows=1 width=16) (actual time=600.848..626.010 rows=673
> loops=1)"
> "              Index Cond: (id_sucursal = 11)"
> "              Filter: ((fecha)::date = '2009-08-13'::date)"
> "        ->  Index Scan using ventas_detalle_id_sucursal_index on
> ventas_detalle vd  (cost=0.00..3.03 rows=2 width=51) (actual
> time=0.026..1312.527 rows=1641934 loops=673)"
> "              Index Cond: (id_sucursal = 11)"
> "  ->  Index Scan using articulos_pkey on articulos a  (cost=0.00..3.01
> rows=1 width=4) (actual time=0.017..0.018 rows=1 loops=1231)"
> "        Index Cond: ("outer".id_articulo = a.id_articulo)"
> "        Filter: (id_servicio IS NULL)"
> "Total runtime: 1308259.159 ms"
>
> Espero sus comentarios.
>

Una vez se me presentó un problema donde las consultas se me hacían
muy lentas donde se hacía un barrido por una tabla condicionandola por
el campo fecha, en ese caso estaba fijado a timestamp. Lo
solucionamos fijando un indice a ese campo.

En nuestro caso necesitamos generar reportes del día y para eso
usabamos algo como

fecha >='2009-10-03 0:00' and fecha<= '2009-10-03 23:59'

y veo tu lo condicionas ((fecha)::date = '2009-08-13'::date)"...
posiblemente ese sea algo mas costoso.

Por lo visto en tu caso no veo que tengas un indice para ese campo.

Bueno, eso en nuestro caso nos solucionó a optimizar los tiempos de respuesta.
--
Rodrigo Ramírez Norambuena
http://decipher.blackhole.cl

Manuel Alejandro Estevez Fernandez escribió:

Obviamente el problema está aquí:

> " -> Index Scan using ventas_detalle_id_sucursal_index on
> ventas_detalle vd (cost=0.00..3.03 rows=2 width=51) (actual
> time=0.026..1312.527 rows=1641934 loops=673)"
> " Index Cond: (id_sucursal = 11)"

Hay 6 órdenes de magnitud de error! Eso es muy malo, francamente es la
primera vez que veo una estimación tan mala.

¿Has hecho ANALYZE alguna vez sobre la tabla? Te sugiero que aumentes
el tamaño de las estadísticas (default_statistics_size = 100 en
postgresql.conf), reinicies el servicio, y luego hagas ANALYZE. Luego
prueba el EXPLAIN ANALYZE nuevamente.

--
Alvaro Herrera http://www.flickr.com/photos/alvherre/
"Granting software the freedom to evolve guarantees only different results,
not better ones." (Zygo Blaxell)

Rodrigo Ramirez Norambuena escribió:

> Por lo visto en tu caso no veo que tengas un indice para ese campo.

Puede que eso sea un problema, pero si te fijas todos los recorridos
usan algún índice (aunque sea un índice en el ID, no en la fecha). En
este caso me parece que primero se debe preocupar del problema de
estimación, y una vez que haya arreglado eso vea si vale la pena agregar
o no un índice en la fecha.

--
Alvaro Herrera http://www.amazon.com/gp/registry/3BP7BYG9PUGI8
"All rings of power are equal,
But some rings of power are more equal than others."
(George Orwell's The Lord of the Rings)

El 03/10/09 18:46, Alvaro Herrera escribió:
> Manuel Alejandro Estevez Fernandez escribió:
>
> Obviamente el problema está aquí:
>
>
>> " -> Index Scan using ventas_detalle_id_sucursal_index on
>> ventas_detalle vd (cost=0.00..3.03 rows=2 width=51) (actual
>> time=0.026..1312.527 rows=1641934 loops=673)"
>> " Index Cond: (id_sucursal = 11)"
>>
> Hay 6 órdenes de magnitud de error! Eso es muy malo, francamente es la
> primera vez que veo una estimación tan mala.
>
> ¿Has hecho ANALYZE alguna vez sobre la tabla? Te sugiero que aumentes
> el tamaño de las estadísticas (default_statistics_size = 100 en
> postgresql.conf), reinicies el servicio, y luego hagas ANALYZE. Luego
> prueba el EXPLAIN ANALYZE nuevamente.
>
>
Voy a cambiar el valor en el archivo de configuración, correré el
analize y envieré los resultados.
Abusando un poco de su habilidad me podrían indicar algún link para el
tunning del servidor asi como los querys y demás, si sería mucho pedir
que fuera como un cookbook, ya que estamos sobre tiempo para entregar el
sistema y lamentablemente no recibimos la capacitación adecuada sobre la
implementación y maneja de postgresql.

Saludos.

2009/10/4 Manuel A. Estevez Fernandez <stvzito(at)gmail(dot)com>:
> Abusando un poco de su habilidad me podrían indicar algún link para el
> tunning del servidor asi como los querys y demás, si sería mucho pedir que
> fuera como un cookbook, ya que estamos sobre tiempo para entregar el sistema
> y lamentablemente no recibimos la capacitación adecuada sobre la
> implementación y maneja de postgresql.
>

Puedes encontrar algunas ideas en:

http://wiki.postgresql.org/wiki/Optimizacion_del_Rendimiento
http://wiki.postgresql.org/wiki/Presentaciones_y_Charlas

--
Atentamente,
Jaime Casanova
Soporte y capacitación de PostgreSQL
Asesoría y desarrollo de sistemas
Guayaquil - Ecuador
Cel. +59387171157

El día 3 de octubre de 2009 19:46, Alvaro Herrera
<alvherre(at)alvh(dot)no-ip(dot)org> escribió:
> Manuel Alejandro Estevez Fernandez escribió:

> Obviamente el problema está aquí:

>> "        ->  Index Scan using ventas_detalle_id_sucursal_index on
>> ventas_detalle vd  (cost=0.00..3.03 rows=2 width=51) (actual
>> time=0.026..1312.527 rows=1641934 loops=673)"
>> "              Index Cond: (id_sucursal = 11)"

> Hay 6 órdenes de magnitud de error!  Eso es muy malo, francamente es la
> primera vez que veo una estimación tan mala.

¿Podías indicarnos que significa eso de órdenes de magnitud?
Primera vez que leo eso en la interpretación de un explain analyze. :)

> ¿Has hecho ANALYZE alguna vez sobre la tabla?  Te sugiero que aumentes
> el tamaño de las estadísticas (default_statistics_size = 100

Y otra pregunta más: este valor de 100,
¿cómo haces para elegir este valor?
Intuyo que está basado en tu experiencia, pero,

¿existe alguna relación con la cantidad de registros de la tabla?

> en postgresql.conf), reinicies el servicio, y luego hagas ANALYZE.  Luego
> prueba el EXPLAIN ANALYZE nuevamente.

Bueno, espero no haber molestado con tantas preguntas,

Un abrazo a todos.
--
§~^Calabaza^~§ from Villa Elisa, Paraguay
http://calablogbaza.blogspot.com/

http://es.wikipedia.org/wiki/Top-posting
http://es.wikipedia.org/wiki/Netiquette

Calabaza escribió:
> El día 3 de octubre de 2009 19:46, Alvaro Herrera
> <alvherre(at)alvh(dot)no-ip(dot)org> escribió:
> > Manuel Alejandro Estevez Fernandez escribió:
>
> > Obviamente el problema está aquí:
>
> >> "        ->  Index Scan using ventas_detalle_id_sucursal_index on
> >> ventas_detalle vd  (cost=0.00..3.03 rows=2 width=51) (actual
> >> time=0.026..1312.527 rows=1641934 loops=673)"
> >> "              Index Cond: (id_sucursal = 11)"
>
> > Hay 6 órdenes de magnitud de error!  Eso es muy malo, francamente es la
> > primera vez que veo una estimación tan mala.
>
> ¿Podías indicarnos que significa eso de órdenes de magnitud?
> Primera vez que leo eso en la interpretación de un explain analyze. :)

Primer párrafo en
http://es.wikipedia.org/wiki/Orden_de_magnitud

> > ¿Has hecho ANALYZE alguna vez sobre la tabla?  Te sugiero que aumentes
> > el tamaño de las estadísticas (default_statistics_size = 100
>
> Y otra pregunta más: este valor de 100,
> ¿cómo haces para elegir este valor?
> Intuyo que está basado en tu experiencia, pero,
>
> ¿existe alguna relación con la cantidad de registros de la tabla?

No, es el tamaño de la lista de valores más comunes y del histograma de
distribuciones del resto de los valores. El valor por omisión de este
parámetro, 10, es muy pequeño y las estadísticas no son buenas cuando la
distribución es desviada de una normal (campana de Gauss) o una
distribución uniforme, que en la vida real es bastante común. Con un
valor de 100 es mucho más posible que el plan realmente represente la
realidad (pero para datos muy extravagantes igual puede ser poco).

La desventaja de tener un valor más alto es que ANALYZE tarda un poco
más y que el optimizador tiene que procesar más datos y por lo tanto
también es un poco más lento, pero el aumento de tiempo es poco y la
mejora de los planes es mucha.

En 8.4 se aumentó el valor por omisión a 100.

--
Alvaro Herrera http://www.amazon.com/gp/registry/3BP7BYG9PUGI8
"Find a bug in a program, and fix it, and the program will work today.
Show the program how to find and fix a bug, and the program
will work forever" (Oliver Silfridge)