Foreign key performance
От | Kevin Brown |
---|---|
Тема | Foreign key performance |
Дата | |
Msg-id | 20030418051133.GK1833@filer обсуждение исходный текст |
Ответы |
Re: [PERFORM] Foreign key performance
|
Список | pgsql-hackers |
I'm using 7.3.2 on Linux, with a decent amount of muscle behind it (1.5 GHz PPro CPU, 1G mem, 20M/sec disks, xlog on different disk than data). I've got a database that has several foreign keys, and I'm copying a bunch of data from an MS-SQL server into it via Perl DBI. I noticed that inserts into this database are very slow, on the order of 100 per second on this hardware. All the inserts are happening in a single transaction. The postmaster I'm connected to appears to be CPU limited, as it's pegging the CPU at a constant 85 percent or more. I have no problem with that under normal circumstances (i.e., the foreign key constraints are actively being enforced): it may well be the nature of foreign keys, but the problem is this: all the keys are DEFERRABLE INITIALLY DEFERRED and, on top of that, the Perl program will SET CONSTRAINTS ALL DEFERRED at the beginning of the transaction. If I remove all the foreign key constraints, my performance goes up to 700 inserts per second! Why isn't the insert performance with all the constraints deferred approximating that of the performance I get without the foreign keys?? If anything, I should get a big delay at transaction commit time while all the foreign key constraints are checked (and, indeed, I get that too), but the performance during the transaction prior to the commit should be the same as it is without the foreign key constraints. It's almost as if the foreign key constraints are being invoked and the results ignored during the inserts... In essence, this smells like a bug to me, but I don't know enough about the internals to really call it that. Any ideas on what can be done about this? -- Kevin Brown kevin@sysexperts.com
В списке pgsql-hackers по дате отправления: