Shridhar Daithankar kirjutas E, 29.09.2003 kell 13:34:
> 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?
hannu=# create table pkt(i int primary key);
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index
'pkt_pkey' for table 'pkt'
CREATE TABLE
hannu=# create table fkt(j int references pkt);
NOTICE: CREATE TABLE will create implicit trigger(s) for FOREIGN KEY
check(s)
CREATE TABLE
hannu=#
now the *foreygn key* column (fkt.j) is without index. As foreign keys
are enforced both ways, this can be a problem when changing table pkt or
bulk creating FK's on big tables.
----------------
Hannu
---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match
