Re: Trigger Performance

Before reading. This is solved. Was an error on my part.

On Sun, 2011-01-16 at 03:46 +0000, Jasen Betts wrote:
> In plpgsql IF is an implicit select.
> <http://www.postgresql.org/docs/8.4/interactive/plpgsql-expressions.html>
>
> IF EXISTS (SELECT 1 FROM t1 WHERE
> volume_id = NEW.volume_id AND name = NEW.name) THEN
> RAISE EXCEPTION '% already exists on volume', NEW.name;
> END IF;

Thanks. Certainly more concise.

>
> > 0.053 ms/record / 1,000 ms/sec * 10,000 records = .53 seconds
>
> huh?
>
> > According to that stat, this lookup should be adding about 0.5 seconds
> > to 10,000 records,
>
> why? what are you doing to 10000 records.

Inserting them. Sorry, that was a critical omission on my part. The
trigger check is for inserts and I'm testing its performance by
inserting 10,000 records.

Turns out my EXPLAIN ANALYZE times were so low because I was querying
for a volume_id that had only a few rows. When I query for a volume_id
with 10,000 rows, it changes to 7 ms, which matches the performance I'm
seeing. That's acceptable to me because that's probably at the upper
end of what I'll see. 7 ms to check 10,000 text fields is actually
impressive to me.

>
> how much disk (in bytes, and dollars) are you hoping to save by not
> using the index.
>

I need to be able to fit my indexes in RAM. This table will have a few
billion records and I have several other indexes with billions of
records and I'd like my DB to run well on a machine with 20G (preferred)
60G (max) RAM and not have to resort to sharding. These text fields can
be up to 1k each. A 1 billion row int8 index comes in around 2G.
Adding the text field to the index would probably put it at over 20G per
billion records.

Thanks.

-Randall