Search Postgresql Archives

Re: LIKE conditions in PGSQL very, very slow!

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



.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@xxxxxxxxxxxxxxxxx

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

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux