Felipe Fernandez wrote:Muestro el resultado de un explain analyze de la query ¿Como se puede mejorar el rendimiento? Tabla "public.clasificados" Columna | Tipo | Modificadores -----------------+------------------------+---------------------------------------------------------------------- referencia | integer | not null default nextval('clasificados_referencia_seq'::regclass) categoria | smallint | nombre | text | telefono | character varying(20) | muestratelefono | boolean | email | character varying(100) | provincia | smallint | fecha | date | hora | time without time zone | estado | smallint | clave | character varying(20) | borrado | boolean | fechaborrado | date | horaborrado | time without time zone | motivoborrado | text | foto1 | text | foto2 | text | foto3 | text | foto4 | text | video | text | observaciones | text | opcion | smallint | tipo | smallint | control | text | localidad | integer | destacado | boolean | default false muestrainicio | boolean | default true zona | character varying(2) | visitas | integer | default 0 Índices: "clasificados_pkey" llave primaria, btree (referencia) "clascate" btree (categoria) Restricciones de llave foránea: "clasificados_categoria_fkey" FOREIGN KEY (categoria) REFERENCES categorias(codigo) "clasificados_provincia_fkey" FOREIGN KEY (provincia) REFERENCES provincias(codigo) "clasificados_opcion_fkey" FOREIGN KEY (opcion) REFERENCES opciones(codigo) Tabla "public.categorias" Columna | Tipo | Modificadores ----------------------+-----------------------+---------------------------------------------------------------- codigo | integer | not null default nextval('categorias_codigo_seq'::regclass) grupo | smallint | descripcionextendida | character varying(80) | descripcion | character varying(30) | Índices: "categorias_pkey" llave primaria, btree (codigo) "categoriasgrupo" btree (grupo) "catgru" btree (grupo) Restricciones de llave foránea: "categorias_grupo_fkey" FOREIGN KEY (grupo) REFERENCES grupos(codigo)Las 5 sql que se generan. Caso de no encontrarse nada en la primera se realiza la segunda donde hace una búsqueda random() explain analyze SELECT DESTACADO,FECHA,HORA,REFERENCIA,FOTO1,LOCALIDAD FROM CL_CLASIFICADOS WHERE FOTO1 != '' AND ESTADO = 3 AND MUESTRAINICIO = TRUE AND DESTACADO = TRUE AND CATEGORIA IN (SELECT CODIGO FROM CL_CATEGORIAS WHERE GRUPO IN (2)) AND CATEGORIA NOT IN (59,60,61,62,63,64,65,66,80) ORDER BY DESTACADO DESC,FECHA DESC, HORA DESC LIMIT 1; explain analyze SELECT (LOCALIDADES.NOMBRE || ' (' || PROVINCIAS.NOMBRE || ')') AS NOMBRE FROM LOCALIDADES,PROVINCIAS WHERE LOCALIDADES.PROVINCIA = PROVINCIAS.CODIGO AND LOCALIDADES.CODIGO = '38039'; explain analyze SELECT DESTACADO,FECHA,HORA,REFERENCIA,FOTO1,LOCALIDAD FROM CL_CLASIFICADOS WHERE FOTO1 != '' AND ESTADO = 3 AND MUESTRAINICIO = TRUE AND DESTACADO = TRUE AND CATEGORIA IN (SELECT CODIGO FROM CL_CATEGORIAS WHERE GRUPO IN (3)) AND CATEGORIA NOT IN (59,60,61,62,63,64,65,66,80) AND REFERENCIA NOT IN (7843) ORDER BY DESTACADO DESC,FECHA DESC, HORA DESC LIMIT 1; explain analyze SELECT (LOCALIDADES.NOMBRE || ' (' || PROVINCIAS.NOMBRE || ')') AS NOMBRE FROM LOCALIDADES,PROVINCIAS WHERE LOCALIDADES.PROVINCIA = PROVINCIAS.CODIGO AND LOCALIDADES.CODIGO = '35022'; explain analyze SELECT DESTACADO,FECHA,HORA,REFERENCIA,FOTO1,LOCALIDAD FROM CL_CLASIFICADOS WHERE FOTO1 != '' AND ESTADO = 3 AND MUESTRAINICIO = TRUE AND DESTACADO = TRUE AND CATEGORIA IN (SELECT CODIGO FROM CL_CATEGORIAS WHERE GRUPO IN (9,11,4,6)) AND CATEGORIA NOT IN (59,60,61,62,63,64,65,66,80) AND REFERENCIA NOT IN (7843,6619) ORDER BY DESTACADO DESC,FECHA DESC, HORA DESC LIMIT 1; explain analyze SELECT FECHA,HORA,REFERENCIA,FOTO1,LOCALIDAD FROM CL_CLASIFICADOS WHERE FOTO1 != '' AND ESTADO = 3 AND MUESTRAINICIO = TRUE AND CATEGORIA IN (SELECT CODIGO FROM CL_CATEGORIAS WHERE GRUPO IN (9,11,4,6)) AND CATEGORIA NOT IN (59,60,61,62,63,64,65,66,80) AND REFERENCIA NOT IN (7843,6619) ORDER BY RANDOM() LIMIT 1; explain analyze SELECT (LOCALIDADES.NOMBRE || ' (' || PROVINCIAS.NOMBRE || ')') AS NOMBRE FROM LOCALIDADES,PROVINCIAS WHERE LOCALIDADES.PROVINCIA = PROVINCIAS.CODIGO AND LOCALIDADES.CODIGO = '38023'; explain analyze SELECT DESTACADO,FECHA,HORA,REFERENCIA,FOTO1,LOCALIDAD FROM CL_CLASIFICADOS WHERE FOTO1 != '' AND ESTADO = 3 AND MUESTRAINICIO = TRUE AND DESTACADO = TRUE AND CATEGORIA IN (SELECT CODIGO FROM CL_CATEGORIAS WHERE GRUPO IN (1)) AND CATEGORIA NOT IN (59,60,61,62,63,64,65,66,80) AND REFERENCIA NOT IN (7843,6619,5110) ORDER BY DESTACADO DESC,FECHA DESC, HORA DESC LIMIT 1; explain analyze SELECT (LOCALIDADES.NOMBRE || ' (' || PROVINCIAS.NOMBRE || ')') AS NOMBRE FROM LOCALIDADES,PROVINCIAS WHERE LOCALIDADES.PROVINCIA = PROVINCIAS.CODIGO AND LOCALIDADES.CODIGO = '38026'; explain analyze SELECT DESTACADO,FECHA,HORA,REFERENCIA,FOTO1,LOCALIDAD FROM CL_CLASIFICADOS WHERE FOTO1 != '' AND ESTADO = 3 AND MUESTRAINICIO = TRUE AND DESTACADO = TRUE AND CATEGORIA IN (SELECT CODIGO FROM CL_CATEGORIAS WHERE GRUPO IN (2)) AND CATEGORIA NOT IN (59,60,61,62,63,64,65,66,80) AND REFERENCIA NOT IN (7843,6619,5110,7522) ORDER BY DESTACADO DESC,FECHA DESC, HORA DESC LIMIT 1; explain analyze SELECT (LOCALIDADES.NOMBRE || ' (' || PROVINCIAS.NOMBRE || ')') AS NOMBRE FROM LOCALIDADES,PROVINCIAS WHERE LOCALIDADES.PROVINCIA = PROVINCIAS.CODIGO AND LOCALIDADES.CODIGO = '38038' Ahora el resultado QUERY PLAN ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Limit (cost=268.31..268.31 rows=1 width=53) (actual time=1091.849..1091.850 rows=1 loops=1) -> Sort (cost=268.31..268.31 rows=1 width=53) (actual time=1091.844..1091.844 rows=1 loops=1) Sort Key: cl_clasificados.destacado, cl_clasificados.fecha, cl_clasificados.hora -> Nested Loop (cost=16.83..268.30 rows=1 width=53) (actual time=276.266..1091.657 rows=26 loops=1) -> HashAggregate (cost=1.60..1.61 rows=1 width=4) (actual time=0.073..0.092 rows=6 loops=1) -> Seq Scan on cl_categorias (cost=0.00..1.60 rows=1 width=4) (actual time=0.039..0.059 rows=6 loops=1) Filter: (grupo = 2) -> Bitmap Heap Scan on cl_clasificados (cost=15.22..266.50 rows=15 width=55) (actual time=181.374..181.837 rows=4 loops=6) Recheck Cond: (cl_clasificados.categoria = "outer".codigo) Filter: ((foto1 <> ''::text) AND (estado = 3) AND muestrainicio AND destacado AND (categoria <> 59) AND (categoria <> 60) AND (categoria <> 61) AND (categoria <> 62) AND (categoria <> 63) AND (categoria <> 64) AND (categoria <> 65) AND (categoria <> 66) AND (categoria <> 80)) -> Bitmap Index Scan on clascate (cost=0.00..15.22 rows=64 width=0) (actual time=17.860..17.860 rows=59873 loops=6) Index Cond: (cl_clasificados.categoria = "outer".codigo) Total runtime: 1092.063 ms (13 filas) QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------------- Nested Loop (cost=0.00..8.18 rows=1 width=65) (actual time=0.170..0.177 rows=1 loops=1) Join Filter: ("outer".provincia = "inner".codigo) -> Index Scan using localidades_pkey on localidades (cost=0.00..5.98 rows=1 width=34) (actual time=0.071..0.072 rows=1 loops=1) Index Cond: (codigo = 38039) -> Seq Scan on provincias (cost=0.00..1.53 rows=53 width=37) (actual time=0.018..0.059 rows=53 loops=1) Total runtime: 0.216 ms (6 filas) QUERY PLAN ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Limit (cost=268.47..268.47 rows=1 width=53) (actual time=481.092..481.093 rows=1 loops=1) -> Sort (cost=268.47..268.47 rows=1 width=53) (actual time=481.090..481.090 rows=1 loops=1) Sort Key: cl_clasificados.destacado, cl_clasificados.fecha, cl_clasificados.hora -> Nested Loop (cost=16.83..268.46 rows=1 width=53) (actual time=181.630..481.061 rows=2 loops=1) -> HashAggregate (cost=1.60..1.61 rows=1 width=4) (actual time=0.052..0.060 rows=3 loops=1) -> Seq Scan on cl_categorias (cost=0.00..1.60 rows=1 width=4) (actual time=0.035..0.043 rows=3 loops=1) Filter: (grupo = 3) -> Bitmap Heap Scan on cl_clasificados (cost=15.22..266.66 rows=15 width=55) (actual time=160.175..160.289 rows=1 loops=3) Recheck Cond: (cl_clasificados.categoria = "outer".codigo) Filter: ((foto1 <> ''::text) AND (estado = 3) AND muestrainicio AND destacado AND (categoria <> 59) AND (categoria <> 60) AND (categoria <> 61) AND (categoria <> 62) AND (categoria <> 63) AND (categoria <> 64) AND (categoria <> 65) AND (categoria <> 66) AND (categoria <> 80) AND (referencia <> 7843)) -> Bitmap Index Scan on clascate (cost=0.00..15.22 rows=64 width=0) (actual time=16.542..16.542 rows=71415 loops=3) Index Cond: (cl_clasificados.categoria = "outer".codigo) Total runtime: 481.278 ms (13 filas) QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------------- Nested Loop (cost=0.00..8.18 rows=1 width=65) (actual time=0.166..0.174 rows=1 loops=1) Join Filter: ("outer".provincia = "inner".codigo) -> Index Scan using localidades_pkey on localidades (cost=0.00..5.98 rows=1 width=34) (actual time=0.072..0.074 rows=1 loops=1) Index Cond: (codigo = 35022) -> Seq Scan on provincias (cost=0.00..1.53 rows=53 width=37) (actual time=0.015..0.056 rows=53 loops=1) Total runtime: 0.213 ms (6 filas) QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ Limit (cost=1070.04..1070.05 rows=1 width=53) (actual time=244.373..244.374 rows=1 loops=1) -> Sort (cost=1070.04..1070.05 rows=1 width=53) (actual time=244.369..244.369 rows=1 loops=1) Sort Key: cl_clasificados.destacado, cl_clasificados.fecha, cl_clasificados.hora -> Nested Loop (cost=17.19..1070.03 rows=1 width=53) (actual time=35.123..244.318 rows=6 loops=1) -> HashAggregate (cost=1.97..2.01 rows=4 width=4) (actual time=0.082..0.132 rows=18 loops=1) -> Seq Scan on cl_categorias (cost=0.00..1.96 rows=4 width=4) (actual time=0.035..0.058 rows=18 loops=1) Filter: ((grupo = 9) OR (grupo = 11) OR (grupo = 4) OR (grupo = 6)) -> Bitmap Heap Scan on cl_clasificados (cost=15.22..266.82 rows=15 width=55) (actual time=13.540..13.550 rows=0 loops=18) Recheck Cond: (cl_clasificados.categoria = "outer".codigo) Filter: ((foto1 <> ''::text) AND (estado = 3) AND muestrainicio AND destacado AND (categoria <> 59) AND (categoria <> 60) AND (categoria <> 61) AND (categoria <> 62) AND (categoria <> 63) AND (categoria <> 64) AND (categoria <> 65) AND (categoria <> 66) AND (categoria <> 80) AND (referencia <> 7843) AND (referencia <> 6619)) -> Bitmap Index Scan on clascate (cost=0.00..15.22 rows=64 width=0) (actual time=0.435..0.435 rows=1257 loops=18) Index Cond: (cl_clasificados.categoria = "outer".codigo) Total runtime: 244.467 ms (13 filas) QUERY PLAN ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Limit (cost=1070.80..1070.80 rows=1 width=52) (actual time=244.045..244.046 rows=1 loops=1) -> Sort (cost=1070.80..1070.80 rows=1 width=52) (actual time=244.042..244.042 rows=1 loops=1) Sort Key: random() -> Nested Loop (cost=17.19..1070.79 rows=1 width=52) (actual time=11.788..243.622 rows=63 loops=1) -> HashAggregate (cost=1.97..2.01 rows=4 width=4) (actual time=0.086..0.136 rows=18 loops=1) -> Seq Scan on cl_categorias (cost=0.00..1.96 rows=4 width=4) (actual time=0.038..0.066 rows=18 loops=1) Filter: ((grupo = 9) OR (grupo = 11) OR (grupo = 4) OR (grupo = 6)) -> Bitmap Heap Scan on cl_clasificados (cost=15.22..266.82 rows=30 width=54) (actual time=13.445..13.502 rows=4 loops=18) Recheck Cond: (cl_clasificados.categoria = "outer".codigo) Filter: ((foto1 <> ''::text) AND (estado = 3) AND muestrainicio AND (categoria <> 59) AND (categoria <> 60) AND (categoria <> 61) AND (categoria <> 62) AND (categoria <> 63) AND (categoria <> 64) AND (categoria <> 65) AND (categoria <> 66) AND (categoria <> 80) AND (referencia <> 7843) AND (referencia <> 6619)) -> Bitmap Index Scan on clascate (cost=0.00..15.22 rows=64 width=0) (actual time=0.434..0.434 rows=1257 loops=18) Index Cond: (cl_clasificados.categoria = "outer".codigo) Total runtime: 244.147 ms (13 filas) QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------------- Nested Loop (cost=0.00..8.18 rows=1 width=65) (actual time=0.186..0.191 rows=1 loops=1) Join Filter: ("outer".provincia = "inner".codigo) -> Index Scan using localidades_pkey on localidades (cost=0.00..5.98 rows=1 width=34) (actual time=0.087..0.089 rows=1 loops=1) Index Cond: (codigo = 38023) -> Seq Scan on provincias (cost=0.00..1.53 rows=53 width=37) (actual time=0.020..0.060 rows=53 loops=1) Total runtime: 0.228 ms (6 filas) QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Limit (cost=268.79..268.79 rows=1 width=53) (actual time=471.215..471.216 rows=1 loops=1) -> Sort (cost=268.79..268.79 rows=1 width=53) (actual time=471.212..471.212 rows=1 loops=1) Sort Key: cl_clasificados.destacado, cl_clasificados.fecha, cl_clasificados.hora -> Nested Loop (cost=16.83..268.78 rows=1 width=53) (actual time=259.407..471.180 rows=3 loops=1) -> HashAggregate (cost=1.60..1.61 rows=1 width=4) (actual time=0.061..0.084 rows=7 loops=1) -> Seq Scan on cl_categorias (cost=0.00..1.60 rows=1 width=4) (actual time=0.024..0.049 rows=7 loops=1) Filter: (grupo = 1) -> Bitmap Heap Scan on cl_clasificados (cost=15.22..266.98 rows=15 width=55) (actual time=67.248..67.263 rows=0 loops=7) Recheck Cond: (cl_clasificados.categoria = "outer".codigo) Filter: ((foto1 <> ''::text) AND (estado = 3) AND muestrainicio AND destacado AND (categoria <> 59) AND (categoria <> 60) AND (categoria <> 61) AND (categoria <> 62) AND (categoria <> 63) AND (categoria <> 64) AND (categoria <> 65) AND (categoria <> 66) AND (categoria <> 80) AND (referencia <> 7843) AND (referencia <> 6619) AND (referencia <> 5110)) -> Bitmap Index Scan on clascate (cost=0.00..15.22 rows=64 width=0) (actual time=3.202..3.202 rows=11153 loops=7) Index Cond: (cl_clasificados.categoria = "outer".codigo) Total runtime: 471.334 ms (13 filas) QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------------- Nested Loop (cost=0.00..8.18 rows=1 width=65) (actual time=0.197..0.203 rows=1 loops=1) Join Filter: ("outer".provincia = "inner".codigo) -> Index Scan using localidades_pkey on localidades (cost=0.00..5.98 rows=1 width=34) (actual time=0.095..0.096 rows=1 loops=1) Index Cond: (codigo = 38026) -> Seq Scan on provincias (cost=0.00..1.53 rows=53 width=37) (actual time=0.019..0.062 rows=53 loops=1) Total runtime: 0.240 ms (6 filas) QUERY PLAN -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Limit (cost=268.95..268.95 rows=1 width=53) (actual time=1093.699..1093.700 rows=1 loops=1) -> Sort (cost=268.95..268.95 rows=1 width=53) (actual time=1093.696..1093.696 rows=1 loops=1) Sort Key: cl_clasificados.destacado, cl_clasificados.fecha, cl_clasificados.hora -> Nested Loop (cost=16.83..268.94 rows=1 width=53) (actual time=274.583..1093.616 rows=25 loops=1) -> HashAggregate (cost=1.60..1.61 rows=1 width=4) (actual time=0.061..0.079 rows=6 loops=1) -> Seq Scan on cl_categorias (cost=0.00..1.60 rows=1 width=4) (actual time=0.030..0.051 rows=6 loops=1) Filter: (grupo = 2) -> Bitmap Heap Scan on cl_clasificados (cost=15.22..267.14 rows=15 width=55) (actual time=181.707..182.169 rows=4 loops=6) Recheck Cond: (cl_clasificados.categoria = "outer".codigo) Filter: ((foto1 <> ''::text) AND (estado = 3) AND muestrainicio AND destacado AND (categoria <> 59) AND (categoria <> 60) AND (categoria <> 61) AND (categoria <> 62) AND (categoria <> 63) AND (categoria <> 64) AND (categoria <> 65) AND (categoria <> 66) AND (categoria <> 80) AND (referencia <> 7843) AND (referencia <> 6619) AND (referencia <> 5110) AND (referencia <> 7522)) -> Bitmap Index Scan on clascate (cost=0.00..15.22 rows=64 width=0) (actual time=17.732..17.733 rows=59873 loops=6) Index Cond: (cl_clasificados.categoria = "outer".codigo) Total runtime: 1093.807 ms <<------------problema (13 filas) QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------------- Nested Loop (cost=0.00..8.18 rows=1 width=65) (actual time=0.188..0.195 rows=1 loops=1) Join Filter: ("outer".provincia = "inner".codigo) -> Index Scan using localidades_pkey on localidades (cost=0.00..5.98 rows=1 width=34) (actual time=0.092..0.093 rows=1 loops=1) Index Cond: (codigo = 38038) -> Seq Scan on provincias (cost=0.00..1.53 rows=53 width=37) (actual time=0.016..0.056 rows=53 loops=1) Total runtime: 0.230 ms (6 filas) -- ---------------------------(fin del mensaje)--------------------------- TIP 5: ¿Has leído nuestro extenso FAQ? http://www.postgresql.org/files/documentation/faqs/FAQ.html