Search Postgresql Archives

Re: Trigger Performance

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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


-- 
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux