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

Re: **SPAM** Faster count(*)?

From: Michael Fuhr <mike(at)fuhr(dot)org>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: dracula007(at)atlas(dot)cz, pgsql-sql(at)postgresql(dot)org
Subject: Re: **SPAM** Faster count(*)?
Date: 2005-08-10 13:31:57
Message-ID: 20050810133157.GA46247@winnie.fuhr.org (view raw or flat)
Thread:
Lists: pgsql-sql
On Tue, Aug 09, 2005 at 09:29:13PM -0600, Michael Fuhr wrote:
> On Tue, Aug 09, 2005 at 10:49:14PM -0400, Tom Lane wrote:
> > Current best practice is to run the explain and parse out the "rows"
> > figure using a perl (or axe-of-choice) regexp, though we could be
> > persuaded to supply a simpler API if there's enough demand for it.
> 
> Somebody else requested a row-count-estimate function a couple of
> weeks ago:
> 
> http://archives.postgresql.org/pgsql-admin/2005-07/msg00256.php

Here's a simple example that parses EXPLAIN output.  It should work
in 8.0.2 and later:

CREATE FUNCTION count_estimate(query text) RETURNS integer AS $$
DECLARE
    rec   record;
    rows  integer;
BEGIN
    FOR rec IN EXECUTE 'EXPLAIN ' || query LOOP
        rows := substring(rec."QUERY PLAN" FROM ' rows=([[:digit:]]+)');
        EXIT WHEN rows IS NOT NULL;
    END LOOP;

    RETURN rows;
END;
$$ LANGUAGE plpgsql VOLATILE STRICT;

CREATE TABLE foo (r double precision);
INSERT INTO foo SELECT random() FROM generate_series(1, 1000);
ANALYZE foo;

SELECT count_estimate('SELECT * FROM foo WHERE r < 0.1');
 count_estimate 
----------------
             97
(1 row)

EXPLAIN SELECT * FROM foo WHERE r < 0.1;
                     QUERY PLAN                      
-----------------------------------------------------
 Seq Scan on foo  (cost=0.00..17.50 rows=97 width=8)
   Filter: (r < 0.1::double precision)
(2 rows)

-- 
Michael Fuhr

In response to

pgsql-sql by date

Next:From: Richard HuxtonDate: 2005-08-10 14:04:48
Subject: Re: Faster count(*)?
Previous:From: Andrew SullivanDate: 2005-08-10 10:50:59
Subject: Re: **SPAM** Faster count(*)?

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