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
>