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 9A452D9DBF for ; Fri, 7 Oct 2005 07:14:17 -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 19807-04 for ; Fri, 7 Oct 2005 10:14:12 +0000 (GMT) Received: from ns2.dialtelecom.sk (ns2.dialtelecom.sk [217.67.16.3]) by svr1.postgresql.org (Postfix) with ESMTP id A0151DA512 for ; Fri, 7 Oct 2005 07:14:07 -0300 (ADT) Received: (qmail 4334 invoked by uid 0); 7 Oct 2005 10:14:09 -0000 Received: from unknown (HELO omikron.sk) (217.67.24.100) by ns2.dialtelecom.sk with SMTP; 7 Oct 2005 10:14:09 -0000 Received: (qmail 27551 invoked from network); 7 Oct 2005 10:14:09 -0000 Received: from unknown (HELO stratos) (158.195.31.19) by 0 with SMTP; 7 Oct 2005 10:14:09 -0000 Message-ID: <9d5b01c5cb27$e7a1c970$131fc39e@stratos> From: "Cestmir Hybl" To: "hubert depesz lubaczewski" Cc: References: <935801c5cb20$dcf93a00$131fc39e@stratos> <9e4684ce0510070254v67e08bcfhb725715803a5de25@mail.gmail.com> Subject: Re: count(*) using index scan in "query often, update rarely" environment Date: Fri, 7 Oct 2005 12:14:29 +0200 Organization: NUSTEP s.r.o. MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_9D58_01C5CB38.AAF817C0" 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.042 required=5 tests=[AWL=0.014, HTML_60_70=0.027, HTML_MESSAGE=0.001] X-Spam-Level: X-Archive-Number: 200510/136 X-Sequence-Number: 14904 This is a multi-part message in MIME format. ------=_NextPart_000_9D58_01C5CB38.AAF817C0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Yes, I can possibly use triggers to maintanin counts of several fixed = groups of records or total recordcount (but it's unpractical). No, I can't speed-up evaluation of generic "count(*) where ()" queries = this way. My question was rather about general performance of count() queries in = environment with infrequent updates. Cestmir ----- Original Message -----=20 From: hubert depesz lubaczewski=20 To: Cestmir Hybl=20 Cc: pgsql-performance@postgresql.org=20 Sent: Friday, October 07, 2005 11:54 AM Subject: Re: [PERFORM] count(*) using index scan in "query often, = update rarely" environment On 10/7/05, Cestmir Hybl wrote: 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? if i understand your problem correctly, then simple usage of triggers = will do the job just fine. hubert ------=_NextPart_000_9D58_01C5CB38.AAF817C0 Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable
Yes, I can possibly use triggers to = maintanin=20 counts of several fixed groups of records or total recordcount (but it's = unpractical).
 
No, I can't speed-up evaluation of = generic=20 "count(*) where ()" queries this way.
 
My question was rather about general = performance of=20 count() queries in environment with infrequent updates.
 
Cestmir
----- Original Message -----
From:=20 hubert = depesz=20 lubaczewski
Cc: pgsql-performance@postgr= esql.org=20
Sent: Friday, October 07, 2005 = 11:54=20 AM
Subject: Re: [PERFORM] count(*) = using=20 index scan in "query often, update rarely" environment

On 10/7/05, Cestmir = Hybl <cestmirl@freeside.sk> = wrote:
Isn't it possible (and reasonable) = for these=20 environments to keep track of whether there is a transaction in = progress=20 with update to given table and if not, use an index scan (count(*) = where) or=20 cached value (count(*)) to perform this kind of query?

if i understand your problem = correctly, then=20 simple usage of triggers will do the job just=20 fine.

hubert
------=_NextPart_000_9D58_01C5CB38.AAF817C0--