Re: Performance problem with row count trigger - Mailing list pgsql-sql

-----BEGIN PGP SIGNED MESSAGE-----
Hash: RIPEMD160


> I was looking to speed up a count(*) query

A few things spring to mind:

1) Use a separate table, rather than storing things inside of
dataset itself. This will reduce the activity on the dataset table.

2) Do you really need bigint for the counts?

3) If you do want to do this, you'll need a different approach as
Tom mentioned. One way to do this is to have a special method for
bulk loading, that gets around the normal updates and requires that
the user take responsiblity for knowing when and how to call the
alternate path. The basic scheme is this:

1. Disable the normal triggers
2. Enable special (perl) triggers that keep the count in memory
3. Do the bulk changes
4. Enable normal triggers, disable special perl one
5. Do other things as needed....
6. Commit the changes to the assoc_count field.

Number 6 can be done anytime, as long as you are in the same session. The danger
is in leaving the session without calling the final function. This can be
solved with some deferred FK trickery, or by careful scripting of the events.
All this doesn't completely remove the pain, but it may shift it around enough
in useful ways for your app.

Here is some code to play with:

- -- Stores changes into memory, no disk access:
CREATE OR REPLACE FUNCTION update_assoc_count_perl()
RETURNS TRIGGER
LANGUAGE plperlu
AS $_$ use strict; my $event = $_TD->{event}; my ($oldid,$newid) = ($_TD->{old}{dataset_id},$_TD->{new}{dataset_id});
if($event eq 'INSERT') {   $_SHARED{foobar}{$newid}++; } elsif ($event eq 'DELETE') {   $_SHARED{foobar}{$oldid}--;
$_SHARED{foobar}{$oldid}||=-1;} elsif ($oldid ne $newid) {   $_SHARED{foobar}{$oldid}--;
$_SHARED{foobar}{$oldid}||=-1;  $_SHARED{foobar}{$newid}++; } return;
 
$_$;

- -- Quick little debug function to view counts:
CREATE OR REPLACE FUNCTION get_assoc_count(int)
RETURNS INTEGER
LANGUAGE plperlu
AS $_$ my $id = shift; return $_SHARED{foobar}{$id} || 0;
$_$;

- -- Create, then disable, the perl trigger
CREATE TRIGGER update_assoc_count_perl AFTER INSERT OR UPDATE OR DELETE ON assoc FOR EACH ROW EXECUTE PROCEDURE
update_assoc_count_perl();

ALTER TABLE assoc DISABLE TRIGGER update_assoc_count_perl;

- -- Switches the main triggers off, and the memory triggers on
- -- Use deferred constraints to ensure that stop_bulkload_assoc_count is called
CREATE OR REPLACE FUNCTION start_bulkload_assoc_count()
RETURNS TEXT
LANGUAGE plperlu
AS $_$ spi_exec_query("ALTER TABLE assoc DISABLE TRIGGER update_assoc_count_insert"); ## x 3 as needed
spi_exec_query("ALTERTABLE assoc ENABLE TRIGGER update_assoc_count_perl"); -- Put foreign key magic here return 'Ready
tobulkload';
 
$_$;

- -- Switches the triggers back, and allows a commit to proceed
CREATE OR REPLACE FUNCTION end_bulkload_assoc_count()
RETURNS TEXT
LANGUAGE plperlu
AS $_$ my $sth = spi_prepare(   'UPDATE dataset SET assoc_count = assoc_count + $1 WHERE dataset_id = $2',   'INTEGER',
'INTEGER');for my $id (keys %{$_SHARED{foobar}}) {   my $val = $_SHARED{foobar}{$id};
spi_exec_prepared($sth,$val,$id);} spi_exec_query("ALTER TABLE assoc ENABLE TRIGGER update_assoc_count_insert"); ## x3
etc.spi_exec_query("ALTER TABLE assoc DISABLE TRIGGER update_assoc_count_perl"); -- Put FK magic here return 'Bulk load
complete';
$_$;

- -- Usage:
SELECT start_bulkload_assoc_count();
- -- Lots of inserts and updates
SELECT end_bulkload_assoc_count();


- --
Greg Sabino Mullane greg@turnstep.com
End Point Corporation
PGP Key: 0x14964AC8 200904021644
http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8
-----BEGIN PGP SIGNATURE-----

iEYEAREDAAYFAknVJiUACgkQvJuQZxSWSsisTQCg4iPr4fepGc/wA3LBUMLz13Gj
aEsAoLFB/KbA572VNKooa2a82Ok4DKUy
=Z95U
-----END PGP SIGNATURE-----