Re: Need more speed from this.

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

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

Brian Avis <brian(dot)avis%searhc(dot)org(at)interlock(dot)lexmark(dot)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(at)lexmark(dot)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(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
>
>
>
>

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