From: Michael Fuhr <mike@fuhr.org>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: dracula007@atlas.cz
Cc: pgsql-sql@postgresql.org
Subject: Re: **SPAM** Faster count(*)?
Date: Wed, 10 Aug 2005 07:31:57 -0600
Message-ID: <20050810133157.GA46247@winnie.fuhr.org> (raw)
In-Reply-To: <20050810032913.GA40917@winnie.fuhr.org>
References: <000101c59d33$36a4a560$9b00015a@osl.com>
<196456155.20050810012958@karneval.cz>
<21626.1123642154@sss.pgh.pa.us>
<20050810032913.GA40917@winnie.fuhr.org>
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
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-sql@postgresql.org
Cc: mike@fuhr.org, tgl@sss.pgh.pa.us, dracula007@atlas.cz
Subject: Re: **SPAM** Faster count(*)?
In-Reply-To: <20050810133157.GA46247@winnie.fuhr.org>
* 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