From: Martijn van Oosterhout <kleptog@svana.org>
To: Denis @ Next2Me <denis@next2me.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: [GENERAL] Yet Another (Simple) Case of Index not used
Date: Wed, 9 Apr 2003 13:18:39 +1000
Message-ID: <20030409031839.GE15928@svana.org> (raw)
In-Reply-To: <EKEBJNAJDPKJBDFGJNIJEENLDBAA.denis@next2me.com>
References: <20030408234623.GD15928@svana.org>
<EKEBJNAJDPKJBDFGJNIJEENLDBAA.denis@next2me.com>
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@svana.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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: kleptog@svana.org, denis@next2me.com, pgsql-performance@postgresql.org
Subject: Re: [GENERAL] Yet Another (Simple) Case of Index not used
In-Reply-To: <20030409031839.GE15928@svana.org>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox