Обсуждение: how to speed query
Hello, First let me say that I have spent a lot of time reading the archives to try and get an answer to my question before I decided to post this. I have a table that has about 200k rows in it and I am running a simple query to get some rows that fall within a range. the problem i and having is that the backend is doing a sequential scan. Maybe this is the best way for it to do the query but if that is the case I need some advice on how to better redesign the system since this simple query is taking too long and of course gets longer as the amount of data grows. Here is a slimmed down example of my table table sipmsg( time long, callid varchar(256) ) the query simply asks for all callids between 2 times. I have an index on the time column but it doesn't get used. Is there a better way for me to be doing this? Seems so simple. more info: the table now has over 200k rows or which the average query will return a couple of hundred rows. running on solaris 8 at 433mhz ( i think ) with 1 gig of memory I know there has to be a way to make this query run faster. t.r. missner level(3) communications
On Fri, Sep 14, 2001 at 02:05:18PM -0600, T.R.Missner@Level3.com wrote: > the query simply asks for all callids between 2 times. > I have an index on the time column but it doesn't get used. > Is there a better way for me to be doing this? > Seems so simple. Have you done a vacuum analyze? Can you show us the explain of the query. -- Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/ > Magnetism, electricity and motion are like a three-for-two special offer: > if you have two of them, the third one comes free.
On Fri, 14 Sep 2001 T.R.Missner@Level3.com wrote: > Here is a slimmed down example of my table > > table sipmsg( > > time long, > callid varchar(256) > > ) > > the query simply asks for all callids between 2 times. > I have an index on the time column but it doesn't get used. > Is there a better way for me to be doing this? > Seems so simple. > > more info: > the table now has over 200k rows or which the average query will return a > couple of hundred rows. > running on solaris 8 at 433mhz ( i think ) with 1 gig of memory Did you made a vacuum analyze? Do you have a index defined for that column? Eventually a 'set enable_seqscan = off;' will help you. Best regards Herbie -- ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Herbert Liechti http://www.thinx.ch ThinX networked business services Adlergasse 5, CH-4500 Solothurn ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~