Re: Trigger Performance - Mailing list pgsql-general

From Randall Smith
Subject Re: Trigger Performance
Date
Msg-id 1295156155.7382.61.camel@randall-laptop
Whole thread Raw
In response to Re: Trigger Performance  (Jasen Betts <jasen@xnet.co.nz>)
List pgsql-general
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


pgsql-general by date:

Previous
From: Jasen Betts
Date:
Subject: Re: Trigger Performance
Next
From: Steve Litt
Date:
Subject: Why can't I change a password