Re: [GENERAL] Definitive answer: can functions use indexes?

2016-01-07 Thread Alban Hertroys
> On 07 Jan 2016, at 5:19, Jim Nasby wrote: > > On 1/6/16 5:41 PM, Tom Lane wrote: >> Since the question makes little sense as stated, I'm going to assume >> you mean "can a query like SELECT ... WHERE left(foo, 3) = 'bar' >> use an index on column foo?" >> >> The

Re: [GENERAL] Definitive answer: can functions use indexes?

2016-01-06 Thread Seamus Abshere
On Wed, Jan 6, 2016, at 08:41 PM, Tom Lane wrote: > Seamus Abshere writes: > > -> Can a function like `LEFT()` use an index? > Since the question makes little sense as stated, I'm going to assume > you mean "can a query like SELECT ... WHERE left(foo, 3) = 'bar' > use an index

Re: [GENERAL] Definitive answer: can functions use indexes?

2016-01-06 Thread Tom Lane
Seamus Abshere writes: > I've been using Postgres for years ( :heart: ) and I'm still in doubt > about this. Would somebody provide an authoritative, definitive, > narrative answer? > -> Can a function like `LEFT()` use an index? To do what? Since the question makes little

Re: [GENERAL] Definitive answer: can functions use indexes?

2016-01-06 Thread Jim Nasby
On 1/6/16 5:15 PM, Seamus Abshere wrote: I've been using Postgres for years ( :heart: ) and I'm still in doubt about this. Would somebody provide an authoritative, definitive, narrative answer? -> Can a function like `LEFT()` use an index? (Or do I have to find an "equivalent" operator in

Re: [GENERAL] Definitive answer: can functions use indexes?

2016-01-06 Thread Tom Lane
Seamus Abshere writes: > I should have been more general. In layman's/narrative terms, what's the > deal with functions vs. operators for postgres indexes? > For example, `exist(hstore,text)` vs. `hstore ? text` ? Yeah. exist(hstore,text) and hstore?text may yield the same

[GENERAL] Definitive answer: can functions use indexes?

2016-01-06 Thread Seamus Abshere
hi, I've been using Postgres for years ( :heart: ) and I'm still in doubt about this. Would somebody provide an authoritative, definitive, narrative answer? -> Can a function like `LEFT()` use an index? (Or do I have to find an "equivalent" operator in order to leverage indexes?) Thanks!

Re: [GENERAL] Definitive answer: can functions use indexes?

2016-01-06 Thread Jim Nasby
On 1/6/16 5:41 PM, Tom Lane wrote: Since the question makes little sense as stated, I'm going to assume you mean "can a query like SELECT ... WHERE left(foo, 3) = 'bar' use an index on column foo?" The answer to that is no, there is no such optimization built into Postgres. (In principle there