Thread: 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 >