Обсуждение: Statistics and Indexes

Hi

I am from a MSSQL background and am trying to understand something about
statistics in PostgreSQL.

Question 1:
In MSSQL, if you create an index (and you are using MSSQL's default
settings) the Server will automatically create appropriate statistics for
you. Does this happen in PostgreSQL? Or should I explicitly create
statistics for every one of my indexes?

Question 2:
I believe ANALYZE keeps indexes and statistics up to date. How often
should this be run (assume that my DB has 200,000 new records daily)?

Thanks in advance
Craig

postgresql@bryden.co.za wrote:
> Hi
>
> I am from a MSSQL background and am trying to understand something about
> statistics in PostgreSQL.
>
> Question 1:
> In MSSQL, if you create an index (and you are using MSSQL's default
> settings) the Server will automatically create appropriate statistics for
> you. Does this happen in PostgreSQL? Or should I explicitly create
> statistics for every one of my indexes?
>

Q2 sort of answers Q1.

> Question 2:
> I believe ANALYZE keeps indexes and statistics up to date. How often
> should this be run (assume that my DB has 200,000 new records daily)?

Try out a VACUUM ANALYZE every hour or three.

--
_______________________________

This e-mail may be privileged and/or confidential, and the sender does
not waive any related rights and obligations. Any distribution, use or
copying of this e-mail or the information it contains by other than an
intended recipient is unauthorized. If you received this e-mail in
error, please advise me (by return e-mail or otherwise) immediately.
_______________________________

Hi.  I am using array_dims to give me dimensions of  multidimensional
array ie:

[1:5][1:2]

In my function I want to retreive the value of the second number from
array_dims (5 in example above)

This is what I am trying:

count_str = substr(dimensions from '\[\d\:(\d+)\]\[\d\:\d\]')
(this should give me 5 in the above example)

I have two problems:

1) I can't seem to escape the [ character - so how do I do this?
2) How do I turn my result which is originally text into an integer?

Regards,
David

David Pratt <fairwinds@eastlink.ca> writes:
> Hi.  I am using array_dims to give me dimensions of  multidimensional
> array ie:

> [1:5][1:2]

> In my function I want to retreive the value of the second number from
> array_dims (5 in example above)

Why aren't you using array_upper()?

> This is what I am trying:
> count_str = substr(dimensions from '\[\d\:(\d+)\]\[\d\:\d\]')

Perhaps you forgot to double the backslashes?

            regards, tom lane

Hi Tom.  I misread the manual.  I thought I could not do array_upper on
multidimensional array but it was specific concatenation functions.
Thank you for clarifying this.

Regards,
David

On Tuesday, July 5, 2005, at 01:22 PM, Tom Lane wrote:

> David Pratt <fairwinds@eastlink.ca> writes:
>> Hi.  I am using array_dims to give me dimensions of  multidimensional
>> array ie:
>
>> [1:5][1:2]
>
>> In my function I want to retreive the value of the second number from
>> array_dims (5 in example above)
>
> Why aren't you using array_upper()?
>
>> This is what I am trying:
>> count_str = substr(dimensions from '\[\d\:(\d+)\]\[\d\:\d\]')
>
> Perhaps you forgot to double the backslashes?
>
>             regards, tom lane
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 8: explain analyze is your friend
>