Re: [GENERAL] Yet Another (Simple) Case of Index not used

From: Martijn van Oosterhout <kleptog(at)svana(dot)org>
To: "Denis (at) Next2Me" <denis(at)next2me(dot)com>
Cc: pgsql-performance(at)postgresql(dot)org
Subject: Re: [GENERAL] Yet Another (Simple) Case of Index not used
Date: 2003-04-09 03:18:39
Message-ID: 20030409031839.GE15928@svana.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general pgsql-performance pgsql-sql

On Tue, Apr 08, 2003 at 05:10:01PM -0700, Denis @ Next2Me wrote:
> Interesting generic response. In other words, "it all depends".
> Well, a de facto observation is: "In my case, it's always much slower with, say, mysql".

Curious, is mysql still so fast when you have transactions enabled? How does
it deal with the following:

begin;
delete from bigtable;
select count(*) from bigtable; -- Should return 0
abort;
select count(*) from bigtable; -- Should give original size

> Understand me, I don't mean to be starting a performance comparaison mysql
> vs postgresql, which is probably an old subject, I am just looking for a
> solution to solve this type of performance issues, ie the generic cases:
> select count(*) from addresses where address is like 'pattern%';
> Which are very fast on mysql, and very slow on postgresql.

Ah, but that may be caused by something else altogether. LIKE is only
indexable in the C locale so if you have en_US as your locale, your LIKE
won't be indexable. See the discussion threads on this mailing list in the past.

> Understood, it will always depend on some parameters, but the real
> question is: how much control does one have over those parameters, and how
> does one tweak them to reach optimal performance?

Hmm, it depends. One person put it that mysql goes for performance first,
then correctness, whereas postgresql goes for correctness first, then
performance.

Maybe fti (full text indexing) would work better?

--
Martijn van Oosterhout <kleptog(at)svana(dot)org> http://svana.org/kleptog/
> "the West won the world not by the superiority of its ideas or values or
> religion but rather by its superiority in applying organized violence.
> Westerners often forget this fact, non-Westerners never do."
> - Samuel P. Huntington

In response to

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Corey Scott 2003-04-09 03:30:48 Re: select random row from a group
Previous Message Christopher Kings-Lynne 2003-04-09 02:35:22 Re: [SQL] Yet Another (Simple) Case of Index not used

Browse pgsql-performance by date

  From Date Subject
Next Message Matthew Nuzum 2003-04-09 03:38:44 choosing the right platform
Previous Message Christopher Kings-Lynne 2003-04-09 02:35:22 Re: [SQL] Yet Another (Simple) Case of Index not used

Browse pgsql-sql by date

  From Date Subject
Next Message Tom Lane 2003-04-09 04:47:28 Re: CASE
Previous Message Christopher Kings-Lynne 2003-04-09 02:35:22 Re: [SQL] Yet Another (Simple) Case of Index not used