Re: LIKE conditions in PGSQL very, very slow!

On 8/10/07, .ep <erick(dot)papa(at)gmail(dot)com> wrote:
> Hi,
>
> I'm moving from the mysql camp and quite liking things like functions
> and such, but a lot of my functionality depends on queries such as
>
> SELECT id, name, start_date
> FROM customer
> WHERE name LIKE 'eri%';
>
> These kinds of queries are super fast in MySQL because "eri%" type
> conditions also use the index. Is this not the case with PG?

See http://www.postgresql.org/docs/faqs.FAQ.html#item4.6
and http://www.postgresql.org/docs/8.2/static/indexes-opclass.html

> While we're at it, are compound indexes ok in PGSQL as well? In MySQL,
> the order of columns is important if it reflects my WHERE conditions
> in SQL. Should I follow the same structure in PGSQL? I tried looking
> at the manual but did not find a section that talks about indexing in
> detail. Would appreciate pointers.

Yes, order is important. This is true for most any database and
multi-column indexes.
See http://www.postgresql.org/docs/8.2/static/indexes-multicolumn.html

Also read the rest of the docs on indexes here:
http://www.postgresql.org/docs/8.2/static/indexes.html

You can do some interesting things with indexes in pgsql, like partial
and expression based indexes.