On 8 February 2016 at 14:52, Tom Lane <tgl@xxxxxxxxxxxxx> wrote: > Thom Brown <thom@xxxxxxxxx> writes: >> I've just noticed a general delete performance issue while testing a >> patch, and this can be recreated on all recent major versions. > >> I have 2 tables: > >> CREATE TABLE countries ( >> country text PRIMARY KEY, >> continent text >> ); > >> CREATE TABLE contacts ( >> id serial PRIMARY KEY, >> first_name text, >> last_name text, >> age integer, >> country text REFERENCES countries (country) >> ); > > Apparently, you don't have an index on the referencing column. > That makes insertions into contacts faster, at the cost of making > deletions from countries much slower. Since there are cases where > that's a reasonable tradeoff, we don't prohibit you from omitting > the index ... but it is a pretty standard foot-gun. Yeah, there's no index on contacts.country, and that would certainly make the query acceptably quick, but I'm asking whether the non-indexed scenario is going about things the most efficient way, given what it has available to it. Thom -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
