On Wed, May 12, 2010 at 12:58 AM, David Fetter <david@xxxxxxxxxx> wrote:
> If you've measured a performance issue for a table that tiny, it's a
> bug that needs fixing in PostgreSQL. What measurements have you done
> so far?
Just for fun, I tried it out myself. Here are the times I got on my
modest laptop:
CHECK constraint:
* 500k INSERTs: 3.8 seconds
* 500k UPDATEs: 6.0 seconds
Foreign Key:
* 500k INSERTs: 18.7 seconds
* 500k UPDATEs: 21.2 seconds
Test SQL is attached.
Josh
BEGIN;
CREATE TABLE design (
design_style text
);
ALTER TABLE design ADD CONSTRAINT "design_style_is_invalid"
CHECK (design_style = ANY (ARRAY['rls'::text, 'sdp'::text, 'rf'::text, 'ssa'::text, 'rom'::text, 'rpt'::text,
'analog'::text, 'sdprpt'::text, 'clkdist'::text, 'global'::text]));
INSERT INTO design (design_style)
SELECT 'rpt' FROM generate_series(1, 500000);
UPDATE design
SET design_style = 'rf'
WHERE design_style = 'rpt';
ROLLBACK;
BEGIN;
CREATE TABLE design_style (
style_name text PRIMARY KEY
);
INSERT INTO design_style (style_name)
VALUES ('rls'), ('sdp'), ('rf'), ('ssa'), ('rom'), ('rpt'),
('analog'), ('sdprpt'), ('clkdist'), ('global');
CREATE TABLE design (
design_style text
);
ALTER TABLE design
ADD CONSTRAINT "design_style_fk"
FOREIGN KEY ("design_style") REFERENCES "design_style"("style_name");
INSERT INTO design (design_style)
SELECT 'rpt' FROM generate_series(1, 500000);
UPDATE design
SET design_style = 'rf'
WHERE design_style = 'rpt';
ROLLBACK;
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
