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

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
  >