Обсуждение: 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