Re: Need more speed from this.

Standard questions:
Have you run VACUUM ANALYZE recently?

Could you send the results of running EXPLAIN on the query in question?

Do the statistics from the EXPLAIN look reasonably accurate?

How about adding an index to the date column?

Is the table growing interactively, or via a once-a-day batch?

HTH,

Wes Sheldahl

Brian Avis <brian(dot)avis%searhc(dot)org(at)interlock(dot)lexmark(dot)com> on 11/19/2001 04:06:17
PM

To: pgsql-general%postgresql(dot)org(at)interlock(dot)lexmark(dot)com
cc: (bcc: Wesley Sheldahl/Lex/Lexmark)
Subject: [GENERAL] Need more speed from this.

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.

Thanks in advance for any assistance.

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org