Hi,
Is there an option in PG to do it?

Coniglio Sgabbiato <nobody (AT) nowhere (DOT) net> wrote:
No. Only for primary keys.

Andreas
--
Andreas Kretschmer
Linux - weil ich es mir wert bin!
GnuPG-ID 0x3FFF606C http://wwwkeys.de.pgp.net

Il 18/12/2009 7.45, Andreas Kretschmer ha scritto:
What a shame! Is it possible to request such a feature for PG next
release? Im my opinion it should be active by default (it is expected
that foreign keys will be used in joins besides referential integrity).

Coniglio Sgabbiato, 18.12.2009 15:44:
Do you know any DBMS where FK columns are automatically indexed?

Thomas

[CUT]

Here:

http://dev.mysql.com/doc/refman/5.1/...nstraints.html

it _seems_ to say that Mysql _could_ do that:

"InnoDB requires indexes on foreign keys and referenced keys so that
foreign key checks can be fast and not require a table scan. In the
referencing table, there must be an index where the foreign key columns
are listed as the first columns in the same order. Such an index is
created on the referencing table automatically if it does not exist.
(This is in contrast to some older versions, in which indexes had to be
created explicitly or the creation of foreign key constraints would
fail.) index_name, if given, is used as described previously."

I tested it but it seems it does not work. I am not so accustomed to
Mysql, so I could did a mistake:

mysql> create table test (id int primary key) engine=innodb;
Query OK, 0 rows affected (0.04 sec)

mysql> create table test2 (id int primary key, fk int references test)
engine=innodb;
Query OK, 0 rows affected (0.05 sec)

mysql> show keys from test2;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
1 row in set (0.00 sec)

Coniglio Sgabbiato <nobody (AT) nowhere (DOT) net> wrote:
In short: -1 from me.

No, there are a LOT of use cases where nobody needs such an index,
especially with small refereced tables.


Andreas
--
Really, I'm not out to destroy Microsoft. That will just be a completely
unintentional side effect. (Linus Torvalds)
"If I was god, I would recompile penguin with --enable-fly." (unknown)
Kaufbach, Saxony, Germany, Europe. N 51.05082°, E 13.56889°

Coniglio Sgabbiato wrote:


[ talking about foreign keys in mysql ]


[...]

Maybe you could wait for a full moon and retry..

[CUT]
I meant creation of index in referencing table, not referenced, what do
you think about?

Coniglio Sgabbiato <nobody (AT) nowhere (DOT) net> wrote:
Ahhh...

test=# create table "Coniglio Sgabbiato" (id serial primary key);
NOTICE: CREATE TABLE will create implicit sequence "Coniglio Sgabbiato_id_seq" for serial column "Coniglio Sgabbiato.id"
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "Coniglio Sgabbiato_pkey" for table "Coniglio Sgabbiato"
CREATE TABLE

Read the NOTICE - lines careful ...

But:

test=*# create table foobar(i int references "Coniglio Sgabbiato" on delete cascade);
CREATE TABLE




Andreas
--
Really, I'm not out to destroy Microsoft. That will just be a completely
unintentional side effect. (Linus Torvalds)
"If I was god, I would recompile penguin with --enable-fly." (unknown)
Kaufbach, Saxony, Germany, Europe. N 51.05082°, E 13.56889°

Coniglio Sgabbiato wrote:


Ok, so I took the time to write a test case.

Can you explain me the effect of the index bigone_dayname_idx, apart
from taking space and slowing insertions?

BTW, most of my foreign keys are never used in joins, are there purely
for referential integrity, and reference small tables.



now, join them


create the index you want (and I don't)
and retry.