Seamus Abshere <seamus@xxxxxxxxxxx> 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 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 could be, but I've not heard enough requests to make me think we'd ever pursue it.) The equivalent optimization that *is* built in, and has been for a long time, is for LIKE: "SELECT ... WHERE foo LIKE 'bar%'" can use an index on foo, at least if it's an index sorted according to C collation. Another answer, which might serve as long as your application only cares about a small number of prefix lengths, is functional indexes. If you create a functional index on "left(foo,3)" you're all set. This won't scale well to a whole bunch of different lengths, though. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
