Thread: Re: [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

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
  >

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

Stephan, Martijn,
good call, that was it: the C locale.

I had used all the default settings when installing/creating the database,
and apparently it used my default locale (en_US).
I recreated (initdb) the database with --no-locale, and recreated the database,
and sure enough, the query:
select count(*) from table where table.column like 'fol%'
was a zillion (well almost) time faster than it used to be,
and on pair with mysql's performance.
And as expected, the EXPLAIN on that query does show indeed
the use of the index I had created on the table.

Sweet, I can now nuke mysql out of my system.

Folks, thank you all for the help and other suggestions.

Denis Amselem
Next2Me Inc.




Stephan said:
  > If it doesn't use the index (ie, it's still using a sequential scan)
  > after the enable_seqscan=off it's likely that you didn't initdb in "C"
  > locale in which case like won't use indexes currently (you can see the
  > archives for long description, but the short one is that some of the
  > locale rules can cause problems with using the index).

Martijn said:

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