Reply-To: <denis@next2me.com>
From: "Denis @ Next2Me" <denis@next2me.com>
To: "Martijn van Oosterhout" <kleptog@svana.org>, "Denis" <denis@next2me.com>
Cc: <pgsql-performance@postgresql.org>
Subject: Re: [GENERAL] Yet Another (Simple) Case of Index not used
Date: Tue, 8 Apr 2003 17:10:01 -0700
Message-ID: <EKEBJNAJDPKJBDFGJNIJEENLDBAA.denis@next2me.com>
MIME-Version: 1.0
Content-Type: text/plain;
	charset="us-ascii"
Content-Transfer-Encoding: 7bit
Importance: Normal
In-Reply-To: <20030408234623.GD15928@svana.org>

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".
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.
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?

D.


  > -----Original Message-----
  > From: pgsql-performance-owner@postgresql.org
  > [mailto:pgsql-performance-owner@postgresql.org]On Behalf Of Martijn van
  > Oosterhout
  > Sent: Tuesday, April 08, 2003 4:46 PM
  > To: Denis
  > Cc: pgsql-performance@postgresql.org; pgsql-general@postgresql.org;
  > pgsql-sql@postgresql.org
  > Subject: Re: [PERFORM] [GENERAL] Yet Another (Simple) Case of Index not
  > used
  > 
  > 
  > On Tue, Apr 08, 2003 at 12:57:16PM -0700, Denis wrote:
  > > The query I am trying to do (fast) is:
  > > 
  > > select count(*) from addresses;
  > > 
  > > This takes more than a second to complete, because, as the 'explain' command
  > > shows me,
  > > the index created on 'addresses' is not used, and a seq scan is being used.
  > > One would assume that the creation of an index would allow the counting of
  > > the number of entries in a table to be instantanous?
  > 
  > Incorrect assumption. select count(*) can produce different results in
  > different backends depending on the current state of the active
  > transactions.
  > -- 
  > 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
  >