.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 [EMAIL PROTECTED] 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 // ---------------------------(end of broadcast)--------------------------- TIP 2: Don't 'kill -9' the postmaster
