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

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

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Kevin Brown <kevin(at)sysexperts(dot)com>
Cc: pgsql-performance(at)postgresql(dot)org
Subject: Re: [SQL] Yet Another (Simple) Case of Index not used
Date: 2003-04-19 15:58:48
Message-ID: 21955.1050767928@sss.pgh.pa.us (view raw or flat)
Thread:
Lists: pgsql-generalpgsql-performancepgsql-sql
Kevin Brown <kevin(at)sysexperts(dot)com> writes:
> Josh Berkus wrote:
>> 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

You missed the point of Josh's comment: in an MVCC system, the correct
COUNT() varies depending on which transaction is asking.  Therefore it
is not possible for a centrally maintained row counter to give accurate
results to everybody, no matter how cheap it is to maintain.

(The cheapness can be disputed as well, since it creates a single point
of contention for all inserts and deletes on the table.  But that's a
different topic.)

			regards, tom lane


In response to

Responses

pgsql-performance by date

Next:From: Stephan SzaboDate: 2003-04-19 19:03:02
Subject: Re: [PERFORM] Foreign key performance
Previous:From: Kevin BrownDate: 2003-04-19 13:01:46
Subject: Re: [SQL] Yet Another (Simple) Case of Index not used

pgsql-sql by date

Next:From: Rajesh Kumar MallahDate: 2003-04-19 16:06:19
Subject: [REPOST] replicable problem with PL/Perl
Previous:From: Tom LaneDate: 2003-04-19 15:51:17
Subject: Re: Where is the sequence value?

pgsql-general by date

Next:From: Bruno Wolff IIIDate: 2003-04-19 16:11:21
Subject: stddev returns 0 when there is one row
Previous:From: Tom LaneDate: 2003-04-19 15:40:32
Subject: Re: does parser still parse those comment out lines?

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