Date: Sat, 19 Apr 2003 06:01:46 -0700
From: Kevin Brown <kevin@sysexperts.com>
To: pgsql-performance@postgresql.org
Subject: Re: [SQL] Yet Another (Simple) Case of Index not used
Message-ID: <20030419130146.GI1847@filer>
Mail-Followup-To: Kevin Brown <kevin@sysexperts.com>,
	pgsql-performance@postgresql.org
References: <EKEBJNAJDPKJBDFGJNIJAENMDBAA.denis@next2me.com>
	<200304090918.45275.josh@agliodbs.com>
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
Content-Disposition: inline
In-Reply-To: <200304090918.45275.josh@agliodbs.com>
User-Agent: Mutt/1.4i
Organization: Frobozzco International

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@sysexperts.com