Skip site navigation (1) Skip section navigation (2)

[Fwd: Re: Consulta, a priori, sencilla]

From: "I(dot)N(dot)T(dot) - Programación" <dpto(dot)programacion(at)grupo-int(dot)com>
To: POSTGRESQL - Ayuda <pgsql-es-ayuda(at)postgresql(dot)org>
Subject: [Fwd: Re: Consulta, a priori, sencilla]
Date: 2008-12-29 16:57:05
Message-ID: 49590161.6090900@grupo-int.com (view raw or flat)
Thread:
Lists: pgsql-es-ayuda
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
</head>
<body bgcolor="#ffffff" text="#000000">
enviosdfa=# EXPLAIN ANALYZE SELECT * FROM envios WHERE apellidos LIKE
'BODEGAS MURUA%' ORDER BY apellidos;<br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; QUERY
PLAN&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; <br>
---------------------------------------------------------------------------------------------------------------------<br>
&nbsp;Sort&nbsp; (cost=144384.87..144384.88 rows=1 width=650) (actual
time=34724.021..34724.040 rows=36 loops=1)<br>
&nbsp;&nbsp; Sort Key: apellidos<br>
&nbsp;&nbsp; -&gt;&nbsp; Seq Scan on envios&nbsp; (cost=0.00..144384.86 rows=1 width=650)
(actual time=1691.879..34723.437 rows=36 loops=1)<br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Filter: (apellidos ~~ 'BODEGAS MURUA%'::text)<br>
&nbsp;Total runtime: 34724.121 ms<br>
(5 filas)<br>
<br>
Un tiempo de consulta exagerado<br>
<br>
&iquest;Puede ser porque mi versi&oacute;n de postgres es la 7.4.7?<br>
<br>
&iquest;Acepta esta versi&oacute;n &iacute;ndices varchar_pattern_ops?<br>
<br>
<br>
-------- Mensaje original --------
<table class="moz-email-headers-table" border="0" cellpadding="0"
 cellspacing="0">
  <tbody>
    <tr>
      <th align="right" nowrap="nowrap" valign="baseline">Asunto: </th>
      <td>Re: [pgsql-es-ayuda] Consulta, a priori, sencilla</td>
    </tr>
    <tr>
      <th align="right" nowrap="nowrap" valign="baseline">Fecha: </th>
      <td>Mon, 29 Dec 2008 14:05:22 -0200</td>
    </tr>
    <tr>
      <th align="right" nowrap="nowrap" valign="baseline">De: </th>
      <td>Guido Barosio <a class="moz-txt-link-rfc2396E" href="mailto:gbarosio(at)gmail(dot)com">&lt;gbarosio(at)gmail(dot)com&gt;</a></td>
    </tr>
    <tr>
      <th align="right" nowrap="nowrap" valign="baseline">Para: </th>
      <td>I.N.T. - Programaci&oacute;n <a class="moz-txt-link-rfc2396E" href="mailto:dpto(dot)programacion(at)grupo-int(dot)com">&lt;dpto(dot)programacion(at)grupo-int(dot)com&gt;</a></td>
    </tr>
    <tr>
      <th align="right" nowrap="nowrap" valign="baseline">CC: </th>
      <td><a class="moz-txt-link-abbreviated" href="mailto:pgsql-es-ayuda(at)postgresql(dot)org">pgsql-es-ayuda(at)postgresql(dot)org</a></td>
    </tr>
    <tr>
      <th align="right" nowrap="nowrap" valign="baseline">Referencias: </th>
      <td><a class="moz-txt-link-rfc2396E" href="mailto:4958E9D3(dot)4000903(at)grupo-int(dot)com">&lt;4958E9D3(dot)4000903(at)grupo-int(dot)com&gt;</a></td>
    </tr>
  </tbody>
</table>
<br>
<br>
<pre>el explain de la consulta que arroja?

On Mon, Dec 29, 2008 at 1:16 PM, "I.N.T. - Programaci&oacute;n"
<a class="moz-txt-link-rfc2396E" href="mailto:dpto(dot)programacion(at)grupo-int(dot)com">&lt;dpto(dot)programacion(at)grupo-int(dot)com&gt;</a> wrote:
&gt; Buenas,
&gt;
&gt; Tengo una BBDD con una tabla llamada 'envios' de 1.677.229 registros
&gt;
&gt; La consulta es:
&gt;
&gt; SELECT * FROM envios WHERE apellidos LIKE 'LOQUESEA%'
&gt;
&gt; y est&aacute; creado el &iacute;ndice:
&gt;
&gt; CREATE INDEX envios_idx01 ON envios USING btree(apellidos);
&gt;
&gt; La consulta tarda casi 50seg. en ejecutarse y es un tiempo inadmisible. Est&aacute;
&gt; claro que no utiliza el &iacute;ndice &iquest;porqu&eacute;?
&gt;
&gt; He realizado VACUUM FULL ANALYZE varias veces, regenerado el &iacute;ndice y nada
&gt;
&gt; &iquest;Alguna sugerencia?
&gt;
&gt; Gracias de antemano --
&gt; TIP 4: No hagas 'kill -9' a postmaster
&gt;
--
TIP 4: No hagas 'kill -9' a postmaster

</pre>
</body>
</html>


Attachment: unknown_filename
Description: text/html (3.8 KB)

Responses

pgsql-es-ayuda by date

Next:From: I.N.T. - ProgramaciónDate: 2008-12-29 16:57:37
Subject: [Fwd: Re: Consulta, a priori, sencilla]
Previous:From: Raul Andres DuqueDate: 2008-12-29 16:15:39
Subject: Re: Consulta, a priori, sencilla

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group