pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  

help / color / mirror / Atom feed
From: Heikki Linnakangas <heikki@enterprisedb.com>
To: Matthew <matthew@flymine.org>
Cc: pgsql-performance@postgresql.org
Subject: Re: count * performance issue
Date: Tue, 11 Mar 2008 14:31:18 +0000
Message-ID: <47D697B6.9070106@enterprisedb.com> (raw)
In-Reply-To: <Pine.LNX.4.64.0803111407580.20402@aragorn.flymine.org>
References: <f966c2ee0803052142l6a105678jfe2da92a0d9371@mail.gmail.com>
	<47D00DB2.7040007@emolecules.com>
	<18451.1204824562@sss.pgh.pa.us>
	<200803071727.36146.josh@agliodbs.com>
	<13345.1204940295@sss.pgh.pa.us>
	<87r6eia9tz.fsf@oxford.xeocode.com>
	<12ba3bf00803101354w15b023e1o237f564a1b01105f@mail.gmail.com>
	<Pine.GSO.4.64.0803101853090.25041@westnet.com>
	<36af4bed0803101957u6078127fq78dc3384fe78de5d@mail.gmail.com>
	<20080311093537.a60e2e3d.wmoran@collaborativefusion.com>
	<Pine.LNX.4.64.0803111355440.20402@aragorn.flymine.org>
	<47D690A2.2090509@wildenhain.de>
	<Pine.LNX.4.64.0803111407580.20402@aragorn.flymine.org>

Matthew wrote:
> No, actually I was referring to a race condition. So, you find the count 
> of rows with IS NULL, then someone changes a row, then you find the 
> count of rows with IS NOT NULL. Add the two together, and there may be 
> rows that were counted twice, or not at all.

Not a problem if you use a serializable transaction, or if you do

SELECT COUNT(*) from table WHERE indexed_field IS NULL
UNION ALL
SELECT COUNT(*) from table WHERE indexed_field IS NOT NULL

as one statement.

However, this makes no sense whatsoever. As both index scans (assuming 
the planner even chooses an index scan for them, which seems highly 
unlikely) still have to visit each tuple in the heap. It's always going 
to be slower than a single "SELECT COUNT(*) FROM table" with a seq scan.

-- 
   Heikki Linnakangas
   EnterpriseDB   http://www.enterprisedb.com



view thread (62+ messages)  latest in thread

Message-ID: <47D697B6.9070106@enterprisedb.com>
Permalink:  ../47D697B6.9070106@enterprisedb.com/
Also on:    postgresql.org/message-id/47D697B6.9070106@enterprisedb.com

 · 

reply
Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-performance@postgresql.org
  Cc: heikki@enterprisedb.com, matthew@flymine.org
  Subject: Re: count * performance issue
  In-Reply-To: <47D697B6.9070106@enterprisedb.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox