Re: LIKE conditions in PGSQL very, very slow!

.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(at)magproductions(dot)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 //