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

From: Kevin Brown <kevin(at)sysexperts(dot)com>
To: pgsql-performance(at)postgresql(dot)org
Subject: Re: [SQL] Yet Another (Simple) Case of Index not used
Date: 2003-04-19 13:01:46
Message-ID: 20030419130146.GI1847@filer
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general pgsql-performance pgsql-sql

Josh Berkus wrote:
> Denis,
>
> > Are you saying the 7.4 'group by' trick would be faster than the simple
> > select count(*)? That seems hard to believe, being that the request now has
> > to fetch / sort the data. I must be missing something.
>
> No, I'm saying that the 7.4 hash-aggregate is faster than the same query was
> under 7.2 or 7.3. Much faster. But it does little to speed up a raw
> count(*).
>
> > The kind of requests that I am really interested in are:
> > select count(*) from table where table.column like 'pattern%'
>
> > These seems to go much master on mysql (which I guess it not a MVCC
> > database? or wasn't the Innobase supposed to make it so?),
>
> They did incorporate a lot of MVCC logic into InnoDB tables, yes.
> Which means that if SELECT count(*) on an InnoDB table is just as
> fast as a MyISAM table, then it is not accurate.

This is not necessarily true. The trigger-based approach to tracking
the current number of rows in a table might well be implemented
internally, and that may actually be much faster than doing it using
triggers (the performance losses you saw may well have been the result
of PG's somewhat poor trigger performance, and not the result of the
approach itself. It would be interesting to know how triggers effect
the performance of other databases).

--
Kevin Brown kevin(at)sysexperts(dot)com

In response to

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Alessandro Bottoni 2003-04-19 14:28:46 Status of the PGSQL replication projects?
Previous Message weiping He 2003-04-19 06:10:18 does parser still parse those comment out lines?

Browse pgsql-performance by date

  From Date Subject
Next Message Tom Lane 2003-04-19 15:58:48 Re: [SQL] Yet Another (Simple) Case of Index not used
Previous Message Stephan Szabo 2003-04-18 15:12:39 Re: [PERFORM] Foreign key performance

Browse pgsql-sql by date

  From Date Subject
Next Message Tom Lane 2003-04-19 15:51:17 Re: Where is the sequence value?
Previous Message Edipo E. F. Melo 2003-04-19 11:50:06 Where is the sequence value?