Re: Need more speed from this.

On Lun 19 Nov 2001 18:06, you wrote:
> I hope someone can help me out with this.
>
> Postgres 7.1.3
> Solaris 2.8
>
> I have a table called system_data setup like this.
>
> | server_id | date | time | data_type_id | data |
> |___________|________|________|______________|______|
> |
> | 26 | a date | a time | 8 | 98 |
>
> -----------------------------------------------------
>
>
> This table is indexed on server_id and data_type_id.
>
> It currently has about 674,456 rows and is growing on a daily
> basis.
>
> The problem is that selects from this table are really slow.
>
> For instance if I do this.
>
> SELECT AVG(data) FROM system_data
> WHERE date BETWEEN CAST('2001-02-01' AS DATE)
> AND CAST ('2001-02-28' AS DATE)
> AND
> server_id = 26
> AND
> data_type_id = 8;
>
> It will take several seconds to complete this select.
>
> I need this type of data retrieval to be much faster if
> possible.

have you tried adding an index on the date column? It should make the
condition 'date between ...' much faster.

Saludos... :-)

--
Porqué usar una base de datos relacional cualquiera,
si podés usar PostgreSQL?
-----------------------------------------------------------------
Martín Marqués | mmarques(at)unl(dot)edu(dot)ar
Programador, Administrador, DBA | Centro de Telematica
Universidad Nacional
del Litoral
-----------------------------------------------------------------