Re: Need more speed from this. - Mailing list pgsql-general

Forwarding to the list...........

---------------------- Forwarded by Wesley Sheldahl/Lex/Lexmark on 11/19/2001
05:19 PM ---------------------------

Brian Avis <brian.avis%searhc.org@interlock.lexmark.com> on 11/19/2001 05:10:31
PM

To:   "Wesley_Sheldahl/Lex/Lexmark.LEXMARK"@sweeper.lex.lexmark.com
cc:    (bcc: Wesley Sheldahl/Lex/Lexmark)
Subject:  Re: [GENERAL] Need more speed from this.


indexing the date field sped things up a lot. Thanks for the help.

Oh and I forgot to mention.  I didn't mention the vacuum analyze bit
because this is a brand new database that hasn't had very many updates
or deletes at all, maybe half a dozen.

Thanks again gang.


wsheldah@lexmark.com wrote:

>
>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.avis%searhc.org@interlock.lexmark.com> on 11/19/2001 04:06:17
>PM
>
>To:   pgsql-general%postgresql.org@interlock.lexmark.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
>
>
>
>

--
Brian Avis
SEARHC Medical Clinic
Juneau, AK 99801
(907) 463-4049
cd /pub
more beer