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
