Обсуждение: LIKE conditions in PGSQL very, very slow!

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?

Here's the EXPLAIN output:


CUSTDB=# explain select id,name,start_date from customer where name
like 'eri%';
                           QUERY PLAN
----------------------------------------------------------------
 Seq Scan on customer  (cost=0.00..86032.18 rows=1 width=111)
   Filter: ((name)::text ~~ 'eri%'::text)
(2 rows)


Would appreciate any thoughts on how to make these kinds of queries
faster. I found a message (http://archives.postgresql.org/pgsql-sql/
1999-12/msg00218.php) but that's from 1999.

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.

Thanks!

On 8/10/07, .ep <erick.papa@gmail.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.

.ep 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?
>
> Here's the EXPLAIN output:
>
>
> CUSTDB=# explain select id,name,start_date from customer where name
> like 'eri%';
>                            QUERY PLAN
> ----------------------------------------------------------------
>  Seq Scan on customer  (cost=0.00..86032.18 rows=1 width=111)
>    Filter: ((name)::text ~~ 'eri%'::text)
> (2 rows)

I think there's either no index on customer.name or you didn't analyze
the table, so PG has outdated statistics on its contents (probably
stating the table is still empty) and thinks a sequential scan will be
faster. You probably want to become acquainted with autovacuum.

Another possibility is that most of your customers names start with
'eri', in which case a seq scan is actually faster... In that case you
should probably do something about your customer base ;)

Regards,

--
Alban Hertroys
alban@magproductions.nl

magproductions b.v.

T: ++31(0)534346874
F: ++31(0)534346876
M:
I: www.magproductions.nl
A: Postbus 416
   7500 AK Enschede

// Integrate Your World //