On Monday 29 September 2003 15:58, Christopher Kings-Lynne wrote:
> >>So a db designer made a bloody mistake.
> >>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.
> >
> > It could probably be doable for EXPLAIN ANALYZE (by actually tracing
> > execution), but then you will see really _all_ queries, i.e. for a 1000
> > row update you would see 1 UPDATE query and 1000 fk checks ...
> >
> > OTOH, you probably can get that already from logs with right logging
> > parameters.
>
> Actually - it shouldn't be too hard to write a query that returns all
> unindexed foreign keys, surely?
Correct me if I am wrong but I remember postgresql throwing error that foreign
key field was not unique in foreign table. Obviously it can not detect that
without an index. Either primary key or unique constraint would need an
index.
What am I missing here?
IOW, how do I exactly create foreign keys without an index?
Shridhar
