Re: [PERFORM] Slow functional indexes?

2006-11-05 Thread Gene
I have a varchar field which is most commonly queried like "someField like '%abcd'". Realizing that it wouldn't be able to use an index for this type of query I created a reverse() function and an index using the function reverse(someField) so that the query would be performed as "reverse(someField

Re: [PERFORM] Slow functional indexes?

2006-11-05 Thread Tom Lane
Stuart Bishop <[EMAIL PROTECTED]> writes: > Here is a minimal test case that demonstrates the issue. Can anyone else > reproduce these results? Of the four EXPLAIN ANALYZE SELECT statements at > the end, the one that orders by a user created IMMUTABLE stored procedure is > consistently slower than

Re: [PERFORM] Slow functional indexes?

2006-11-05 Thread Stuart Bishop
Stuart Bishop wrote: > I would like to understand what causes some of my indexes to be slower to > use than others with PostgreSQL 8.1. On a particular table, I have an int4 > primary key, an indexed unique text 'name' column and a functional index of > type text. The function (person_sort_key()) i

Re: [PERFORM] Slow functional indexes?

2006-10-24 Thread Stuart Bishop
Tom Lane wrote: > Stuart Bishop <[EMAIL PROTECTED]> writes: >> I would like to understand what causes some of my indexes to be slower to >> use than others with PostgreSQL 8.1. > > I was about to opine that it was all about different levels of > correlation between the index order and physical tab

Re: [PERFORM] Slow functional indexes?

2006-10-20 Thread Tom Lane
Stuart Bishop <[EMAIL PROTECTED]> writes: > I would like to understand what causes some of my indexes to be slower to > use than others with PostgreSQL 8.1. I was about to opine that it was all about different levels of correlation between the index order and physical table order ... but your expe

Re: [PERFORM] Slow functional indexes?

2006-10-20 Thread Merlin Moncure
On 10/20/06, Stuart Bishop <[EMAIL PROTECTED]> wrote: I would like to understand what causes some of my indexes to be slower to use than others with PostgreSQL 8.1. On a particular table, I have an int4 primary key, an indexed unique text 'name' column and a functional index of type text. The fun

[PERFORM] Slow functional indexes?

2006-10-20 Thread Stuart Bishop
I would like to understand what causes some of my indexes to be slower to use than others with PostgreSQL 8.1. On a particular table, I have an int4 primary key, an indexed unique text 'name' column and a functional index of type text. The function (person_sort_key()) is declared IMMUTABLE and RETU