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

Salve.

I understand from various web searches and so on that PostgreSQL's MVCC
mechanism makes it very hard to use indices or table metadata to optimise
count(*). Is there a better way to guess the "approximate size" of a table?

I'm trying to write a trigger that fires on insert and performs some
maintenance (collapsing overlapping boxes into a single large box,
specifically) as the table grows. My initial attempt involved count(*) and,
as the number of pages in the table grew, that trigger bogged down the
database.

Any thoughts?

-O

I believe running count(*) means fulltable scan, and there's no way
to do it without it. But what about some "intermediate" table, with
the necessary counts?

That means to create a table with values (counts) you need, and on
every insert/delete/update increment or decrement the appropriate
values. This way you won't need the count(*) query anymore, and the
performance should be much better.

t.v.

> Salve.

> I understand from various web searches and so on that PostgreSQL's MVCC
> mechanism makes it very hard to use indices or table metadata to optimise
> count(*). Is there a better way to guess the "approximate size" of a table?

> I'm trying to write a trigger that fires on insert and performs some
> maintenance (collapsing overlapping boxes into a single large box,
> specifically) as the table grows. My initial attempt involved count(*) and,
> as the number of pages in the table grew, that trigger bogged down the
> database.

> Any thoughts?

dracula007(at)atlas(dot)cz writes:
> I believe running count(*) means fulltable scan, and there's no way
> to do it without it. But what about some "intermediate" table, with
> the necessary counts?

There's a fairly complete discussion in the PG list archives of a
reasonably-efficient scheme for maintaining such counts via triggers.
It wasn't efficient enough that we were willing to impose the overhead
on every application ... but if you really NEED a fast count(*) you
could implement it. I'd like to see someone actually do it and put
up working code on pgfoundry; AFAIK it's only a paper design so far.

If you only want a very-approximate count, the best bet is to rely on
the planner's estimates, eg

regression=# explain select * from tenk1;
QUERY PLAN
-------------------------------------------------------------
Seq Scan on tenk1 (cost=0.00..458.00 rows=10000 width=244)
^^^^^

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.

regards, tom lane

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

--
Michael Fuhr

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.

FWIW, this was another one of those things I must have heard a dozen
times at OSCON. I suspect the simpler API would be popular,
particularly since post-8.0 the estimates are more reliable than they
used to be.

A

--
Andrew Sullivan | ajs(at)crankycanuck(dot)ca
The whole tendency of modern prose is away from concreteness.
--George Orwell

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

Owen Jacobson wrote:
> Salve.
>
> I understand from various web searches and so on that PostgreSQL's MVCC
> mechanism makes it very hard to use indices or table metadata to optimise
> count(*). Is there a better way to guess the "approximate size" of a table?

Plenty of good answers on how to estimate table-size, but it sounds like
you just want to run your maintenance function "every so often".

1. Create a sequence "my_table_tracker_seq"
2. On insert, call nextval(my_table_tracker_seq)
3. If value modulo 1000 = 0, run the maintenance routine

That's about as fast as you can get, and might meet your needs. Of
course you'll need to be more complex if you insert multiple rows at a time.

If you do a lot of deletion on the table and want to take that into
account, have a second sequence you increment on deletion and subtract
the one from the other.

Not always accurate enough, but it is quick.
--
Richard Huxton
Archonet Ltd

Tom Lane wrote:

> dracula007(at)atlas(dot)cz writes:
> > I believe running count(*) means fulltable scan, and there's no way
> > to do it without it. But what about some "intermediate" table, with
> > the necessary counts?
>
> There's a fairly complete discussion in the PG list archives of a
> reasonably-efficient scheme for maintaining such counts via triggers.
> It wasn't efficient enough that we were willing to impose the overhead
> on every application ... but if you really NEED a fast count(*) you
> could implement it. I'd like to see someone actually do it and put
> up working code on pgfoundry; AFAIK it's only a paper design so far.
>
> If you only want a very-approximate count, the best bet is to rely on
> the planner's estimates, eg
>
> regression=# explain select * from tenk1;
> QUERY PLAN
> -------------------------------------------------------------
> Seq Scan on tenk1 (cost=0.00..458.00 rows=10000 width=244)
> ^^^^^
>
> 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.

Yick. Ok, given all of that, I've rewritten the trigger in question to fire
on different, indexable criteria (difference between "earliest" and "latest"
rows in the table).

Thanks, everyone.

Owen

Tom Lane wrote:
> dracula007(at)atlas(dot)cz writes:
>
>>I believe running count(*) means fulltable scan, and there's no way
>>to do it without it. But what about some "intermediate" table, with
>>the necessary counts?
>
>
> There's a fairly complete discussion in the PG list archives of a
> reasonably-efficient scheme for maintaining such counts via triggers.
> It wasn't efficient enough that we were willing to impose the overhead
> on every application ... but if you really NEED a fast count(*) you
> could implement it. I'd like to see someone actually do it and put
> up working code on pgfoundry; AFAIK it's only a paper design so far.

I was kicking this around and came up with the following. I have hit a
couple of snags.

In the function I attempt to count the number of rows in a table being
checked for the first time. I wanted to use 'FROM TG_RELNAME' but as
you can see I had to hard code my test table and comment out the trigger
parameter. FROM tbl_demo--TG_RELNAME Can someone tell me why that won't
work?

Also the function doesn't seem to be getting ROW_COUNT properly. The
end result is that for this test the table is properly inserted into the
monitoring table after it's forth insert but it is never updated after
that. Can someone help me see the forest through the trees?

-- Clean up the environment.
DROP TABLE tbl_row_count;
DROP TABLE tbl_demo;
DROP FUNCTION tf_update_row_count();

-- Build the table for holding the row counts.
CREATE TABLE tbl_row_count
(
relid oid NOT NULL,
row_count int8 NOT NULL DEFAULT 0
)
WITHOUT OIDS;
ALTER TABLE tbl_row_count OWNER TO postgres;
COMMENT ON COLUMN tbl_row_count.relid IS 'Contains relation id number.';
COMMENT ON COLUMN tbl_row_count.row_count IS 'Contains the number of
rows in a relation.';

-- Build a table to test the trigger on.
CREATE TABLE tbl_demo
(
first_name varchar(30) NOT NULL
)
WITHOUT OIDS;
ALTER TABLE tbl_demo OWNER TO postgres;
COMMENT ON TABLE tbl_demo IS 'Table used for demonstrating a trigger.';

-- Create the trigger function to maintain the row counts.
CREATE OR REPLACE FUNCTION public.tf_update_row_count()
RETURNS "trigger" AS
$BODY$
DECLARE
v_row_count int8;
BEGIN
-- Store the row count before it disappears.
GET DIAGNOSTICS v_row_count = ROW_COUNT;
-- Check if this is a new table.
PERFORM relid
FROM public.tbl_row_count
WHERE relid = TG_RELID;
IF FOUND THEN
-- Data for this table is already in the row count table.
IF TG_OP = 'INSERT' THEN
UPDATE public.tbl_row_count
SET row_count = row_count + v_row_count
WHERE relid = TG_RELID;
ELSIF TG_OP = 'DELETE' THEN
UPDATE public.tbl_row_count
SET row_count = row_count - v_row_count
WHERE relid = TG_RELID;
END IF;
ELSE
-- This is a new table so it needs to be counted.
SELECT count(*)
FROM tbl_demo--TG_RELNAME
INTO v_row_count;
INSERT INTO public.tbl_row_count ( relid, row_count )
VALUES ( TG_RELID,
v_row_count
);
END IF;
RETURN NULL;
END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE;
ALTER FUNCTION public.tf_update_row_count() OWNER TO postgres;
GRANT EXECUTE ON FUNCTION public.tf_update_row_count() TO postgres;
GRANT EXECUTE ON FUNCTION public.tf_update_row_count() TO public;

-- Insert some initial data into the demo table.
INSERT INTO public.tbl_demo
( first_name )
VALUES ( 'Keith' );
INSERT INTO public.tbl_demo
( first_name )
VALUES ( 'Ed' );
INSERT INTO public.tbl_demo
( first_name )
VALUES ( 'Kryss' );

-- Create the trigger on the demo table.
CREATE TRIGGER tgr_update_row_count
AFTER INSERT OR DELETE
ON public.tbl_demo
FOR EACH STATEMENT
EXECUTE PROCEDURE public.tf_update_row_count();

-- Examine the starting state of the tables.
SELECT *
FROM public.tbl_demo;
SELECT *
FROM public.tbl_row_count;
SELECT relid,
relname,
row_count
FROM public.tbl_row_count
LEFT JOIN pg_class
ON ( tbl_row_count.relid = pg_class.oid );

-- Insert a row.
INSERT INTO public.tbl_demo
( first_name )
VALUES ( 'Jarus' );

-- Examine the new state of the tables.
SELECT *
FROM public.tbl_demo;
SELECT relid,
relname,
row_count
FROM public.tbl_row_count
LEFT JOIN pg_class
ON ( tbl_row_count.relid = pg_class.oid );

-- Insert two more rows.
INSERT INTO public.tbl_demo
( first_name )
VALUES ( 'Dani' );
INSERT INTO public.tbl_demo
( first_name )
VALUES ( 'Mary' );

-- Examine the final state of the tables.
SELECT *
FROM public.tbl_demo;
SELECT relid,
relname,
row_count
FROM public.tbl_row_count
LEFT JOIN pg_class
ON ( tbl_row_count.relid = pg_class.oid );

--
Kind Regards,
Keith