> > > So a db designer made a bloody mistake.
Not necessarily. If I'm never going to update or delete from the parent table the index would be useless. I find very few of my foreign key relationships actually need indexes on the child table. I usually only have the unique index on the parent table. And often the child table is the big table. The index would be very large and have awful selectivity. The last thing I want is a 5-million record table with half a dozen indexes each with 10-20 unique values. > > > The problem is there's no easy way to find out what's missing. > > > I'd really like EXPLAIN to display all subsequent triggered queries > > > also, to see the full scans caused by missing indexes. > > > > I'd sure second that! I think the root of problem here is the same as the root of the problem with foreign key checks being slow for large batch updates and inserts. Namely that foreign key constraint checks are being handled as a million small queries. To handle foreign key constraints optimally they would really have to be merged into the plan in a kind of join. For most inserts/updates something like a nested-loop join that is effectively the same as the current triggers would be used. But for large batch updates/inserts it's quite possible that it would look more like a hash join or even a merge join. To do that would probably mean throwing out the whole trigger-based implementation though, which seems like an awfully big project. And being able to disable and reenable constraints would still be nice. They're never going to be instantaneous. And besides, speed isn't the only reason to want to disable constraints temporarily. The database is a tool, it should be there to do the DBA's bidding, not the other way around :) -- greg ---------------------------(end of broadcast)--------------------------- TIP 8: explain analyze is your friend