Re: Need more speed from this.
От | wsheldah@lexmark.com |
---|---|
Тема | Re: Need more speed from this. |
Дата | |
Msg-id | 200111192217.RAA14453@interlock2.lexmark.com обсуждение исходный текст |
Ответ на | Need more speed from this. (Brian Avis <brian.avis@searhc.org>) |
Список | 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
В списке pgsql-general по дате отправления: