X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org
Received: from localhost (av.hub.org [200.46.204.144])
	by svr1.postgresql.org (Postfix) with ESMTP id 2894CDA48E
	for <pgsql-performance-postgresql.org@localhost.postgresql.org>;
	Fri,  7 Oct 2005 06:24:18 -0300 (ADT)
Received: from svr1.postgresql.org ([200.46.204.71])
	by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024)
	with ESMTP id 86454-07
	for <pgsql-performance-postgresql.org@localhost.postgresql.org>;
	Fri,  7 Oct 2005 09:24:13 +0000 (GMT)
Received: from ns2.dialtelecom.sk (ns2.dialtelecom.sk [217.67.16.3])
	by svr1.postgresql.org (Postfix) with ESMTP id E6289D9001
	for <pgsql-performance@postgresql.org>;
	Fri,  7 Oct 2005 06:24:11 -0300 (ADT)
Received: (qmail 2406 invoked by uid 0); 7 Oct 2005 09:23:48 -0000
Received: from unknown (HELO omikron.sk) (217.67.24.100)
	by ns2.dialtelecom.sk with SMTP; 7 Oct 2005 09:23:45 -0000
Received: (qmail 15740 invoked from network); 7 Oct 2005 09:23:45 -0000
Received: from unknown (HELO stratos) (158.195.31.19)
	by 0 with SMTP; 7 Oct 2005 09:23:45 -0000
Message-ID: <935801c5cb20$dcf93a00$131fc39e@stratos>
From: "Cestmir Hybl" <cestmirl@freeside.sk>
To: <pgsql-performance@postgresql.org>
Subject: count(*) using index scan in "query often, update rarely" environment
Date: Fri, 7 Oct 2005 11:24:05 +0200
Organization: NUSTEP s.r.o.
MIME-Version: 1.0
Content-Type: multipart/alternative;
	boundary="----=_NextPart_000_9355_01C5CB31.A0496DD0"
X-Priority: 3
X-MSMail-Priority: Normal
X-Mailer: Microsoft Outlook Express 6.00.2900.2527
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2527
X-Virus-Scanned: by amavisd-new at hub.org
X-Spam-Status: No, hits=0.062 required=5 tests=[AWL=-0.034, HTML_50_60=0.095,
	HTML_MESSAGE=0.001]
X-Spam-Level: 
X-Archive-Number: 200510/134
X-Sequence-Number: 14902

This is a multi-part message in MIME format.

------=_NextPart_000_9355_01C5CB31.A0496DD0
Content-Type: text/plain;
	charset="iso-8859-2"
Content-Transfer-Encoding: quoted-printable

Hello all

First of all, I do understand why pgsql with it's MVCC design has to =
examine tuples to evaluate "count(*)" and "count(*) where (...)" queries =
in environment with heavy concurrent updates.

This kind of usage IMHO isn't the average one. There are many =
circumstances with rather "query often, update rarely" character.

Isn't it possible (and reasonable) for these environments to keep track =
of whether there is a transaction in progress with update to given table =
and if not, use an index scan (count(*) where) or cached value =
(count(*)) to perform this kind of query?

(sorry for disturbing if this was already discussed)

Regards,

Cestmir Hybl
------=_NextPart_000_9355_01C5CB31.A0496DD0
Content-Type: text/html;
	charset="iso-8859-2"
Content-Transfer-Encoding: quoted-printable

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META http-equiv=3DContent-Type content=3D"text/html; =
charset=3Diso-8859-2">
<META content=3D"MSHTML 6.00.2900.2722" name=3DGENERATOR>
<STYLE></STYLE>
</HEAD>
<BODY bgColor=3D#ffffff>
<DIV><FONT face=3DArial size=3D2>Hello all</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT face=3DArial size=3D2>First of all, I do understand why pgsql =
with it's=20
MVCC design has to examine tuples to evaluate "count(*)"&nbsp;and =
"count(*)=20
where (...)" queries in environment with heavy concurrent =
updates.</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT face=3DArial size=3D2>This kind of usage IMHO isn't the =
average one.=20
There are many circumstances with rather "query often, update=20
rarely"&nbsp;character.</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT face=3DArial size=3D2>Isn't it possible (and reasonable) for =
these=20
environments to keep track of whether there is a transaction in progress =
with=20
update to given table and if not, use an index scan (count(*) where) or =
cached=20
value (count(*)) to perform this kind of query?</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT face=3DArial size=3D2>(sorry for disturbing if this was =
already=20
discussed)</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT face=3DArial size=3D2>Regards,</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT face=3DArial size=3D2>Cestmir Hybl</FONT></DIV></BODY></HTML>

------=_NextPart_000_9355_01C5CB31.A0496DD0--