Re: [PERFORM] scheduling autovacuum at lean hours only.

2009-02-12 Thread Guillaume Cottenceau
Rajesh Kumar Mallah mallah.rajesh 'at' gmail.com writes: our usage pattern is such that peak activity (indicated by load average) during day time is 10 times during night hours. Autovacuum just puts more pressure to the system. If less stressing version is used then Yet it may allow a more

[PERFORM] col1 ILIKE 'foo%' not behaving the same as lower(col1) LIKE 'foo%'

2009-02-12 Thread milos d
Hello, I have a table 'foo_bar' with a column 'col1' defined as 'col1 varchar(512)'. This column is indexed using an expression index defined as CREATE INDEX ix_foo_bar_by_col1 ON foo_bar(lower(col1) col1 varchar_pattern_ops) The problem is when I try matching using ILIKE, (col1 ILIKE 'foo%')

Re: [PERFORM] col1 ILIKE 'foo%' not behaving the same as lower(col1) LIKE 'foo%'

2009-02-12 Thread Richard Huxton
milos d wrote: Hello, I have a table 'foo_bar' with a column 'col1' defined as 'col1 varchar(512)'. This column is indexed using an expression index defined as CREATE INDEX ix_foo_bar_by_col1 ON foo_bar(lower(col1) col1 varchar_pattern_ops) The problem is when I try matching using

Re: [PERFORM] col1 ILIKE 'foo%' not behaving the same as lower(col1) LIKE 'foo%'

2009-02-12 Thread Scott Carey
I'm pretty sure the intent was: WHERE lower(col1) LIKE lower('foo%'); Most likely, his client code ensures the lower on the string passed in the query. Whether it should use an index or not has nothing to do with his example. All I can do when answering this question, is confirm that the

[PERFORM] GIST versus GIN indexes for intarrays

2009-02-12 Thread Rusty Conover
Hi Guys, I'm a bit confused when the proper way to use GIST versus GIN indexes with integer arrays. The documentation states: http://www.postgresql.org/docs/current/static/intarray.html The choice between GiST and GIN indexing depends on the relative performance characteristics of GiST

Re: [PERFORM] GIST versus GIN indexes for intarrays

2009-02-12 Thread Tom Lane
Rusty Conover rcono...@infogears.com writes: Since 100% of my queries are for retrieval, I should use GIN but it never appears to be used unlike how GIST indexes are: You haven't shown us either the table or the index declaration, so it's a bit tough to comment on that. It's worth noting

Re: [PERFORM] GIST versus GIN indexes for intarrays

2009-02-12 Thread Rusty Conover
On Feb 12, 2009, at 1:54 PM, Tom Lane wrote: Rusty Conover rcono...@infogears.com writes: Since 100% of my queries are for retrieval, I should use GIN but it never appears to be used unlike how GIST indexes are: You haven't shown us either the table or the index declaration, so it's a bit

Re: [PERFORM] GIST versus GIN indexes for intarrays

2009-02-12 Thread Tom Lane
Rusty Conover rcono...@infogears.com writes: The gist__int_ops is the default operator class for integer[] arrays, as shown at: http://www.postgresql.org/docs/current/static/intarray.html Ah, so you have contrib/intarray installed. [ pokes at it... ] Seems like what we have here is another

Re: [PERFORM] col1 ILIKE 'foo%' not behaving the same as lower(col1) LIKE 'foo%'

2009-02-12 Thread milos d
Thanks Scott, Yes you are right, my code does lower case 'foo%'. I would expect SELECT ... WHERE col1 ILIKE 'foo%' to use an index. The way I understand it is that the planner would translate this to SELECT ... WHERE lower(col1) LIKE lower('foo%') ? You may be right, with more tests I see

Re: [PERFORM] GIST versus GIN indexes for intarrays

2009-02-12 Thread Rusty Conover
On Feb 12, 2009, at 2:29 PM, Tom Lane wrote: Rusty Conover rcono...@infogears.com writes: The gist__int_ops is the default operator class for integer[] arrays, as shown at: http://www.postgresql.org/docs/current/static/intarray.html Ah, so you have contrib/intarray installed. [ pokes at