Thread: Optimizing
Please advise how I can improve performance of query searching for telephone numbers. I have a table with over a million records of phone numbers in a single indexed field. To find one phone number takes nearly 20 seconds. Aside from hardware upgrades, how can I improve the performance of this query? Thank you in advance.
On Fri, 28 Feb 2003, Montrone, Marc -- 7183 wrote: > Please advise how I can improve performance of query searching for telephone > numbers. I have a table with over a million records of phone numbers in a > single indexed field. To find one phone number takes nearly 20 seconds. > Aside from hardware upgrades, how can I improve the performance of this > query? Thank you in advance. Please provide the following which will help us to help you: The output of this commend : select version(); Please prefix your query with "explain analyze" and provide us with both the query and the output of this command.
That should be virtually instant. First, the usual suspects: have you run "vacuum analyze" on your table? If not, do it and try again. If so, please show us your table structure, index, and query - just fire up psql and run "\d yourtablename" and "explain <your query>" and send it to us. Cheers, Steve On Friday 28 February 2003 7:26 am, Montrone, Marc -- 7183 wrote: > Please advise how I can improve performance of query searching for > telephone numbers. I have a table with over a million records of phone > numbers in a single indexed field. To find one phone number takes nearly 20 > seconds. Aside from hardware upgrades, how can I improve the performance of > this query? Thank you in advance. > > ---------------------------(end of broadcast)--------------------------- > TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org