Skip site navigation (1) Skip section navigation (2)

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 (view raw or flat)
Thread:
Lists: pgsql-generalpgsql-performancepgsql-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

pgsql-performance by date

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

pgsql-sql by date

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

pgsql-general by date

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

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group