From: Denis @ Next2Me <denis@next2me.com>
To: Martijn van Oosterhout <kleptog@svana.org>
To: 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> (raw)
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
>
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: denis@next2me.com, kleptog@svana.org, pgsql-performance@postgresql.org
Subject: Re: [GENERAL] Yet Another (Simple) Case of Index not used
In-Reply-To: <EKEBJNAJDPKJBDFGJNIJEENLDBAA.denis@next2me.com>
* 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