implicit index creation on foreign key addition

#11

Marco Mariani wrote:

Quote:

INSERT INTO bigone (dayname) VALUES ((SELECT dayname FROM week ORDER
BY RANDOM() LIMIT 1));

of course this is unneded

Quote:

coges=# EXPLAIN SELECT pk, stuff FROM bigone natural JOIN week;
QUERY PLAN

Fact is, even if you ANALYZE the table, postgres doesn't make use of the
index. It need a bigger week table. How big I don't know, and I'd like
to know if there are some heuristics.

#12

Il 18/12/2009 17.29, Andreas Kretschmer ha scritto:

Quote:

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

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

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

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

Sorry I cant understand what you mean here, I already know about
implicit creation of indexes on primary keys, I wanted a similar feature
on foreign keys...

#13

Marco Mariani <marco (AT) sferacarta (DOT) com> wrote:

Quote:

Marco Mariani wrote:

INSERT INTO bigone (dayname) VALUES ((SELECT dayname FROM week ORDER
BY RANDOM() LIMIT 1));

of course this is unneded

coges=# EXPLAIN SELECT pk, stuff FROM bigone natural JOIN week;
QUERY PLAN

Fact is, even if you ANALYZE the table, postgres doesn't make use of the
index. It need a bigger week table. How big I don't know, and I'd like
to know if there are some heuristics.

Sure, the planner works with costs, and so you can calculate this costs.

I haven't time at the moment to illustrate this, maybe later...

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°

#14

Il 18/12/2009 17.39, Marco Mariani ha scritto:

Quote:

Coniglio Sgabbiato 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.

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

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.
[CUT]

I got your point, good one. Otherwise, I believe that an index on
foreign keys in little tables is a negligible overhead, either in disk
space and in cpu time on inserts. In big tables it might be useful if
the join condition is associate to another condition, for example, if
t1.foo references t2.foo then:

select * from t1 join t2 on t1.foo=t2.foo and t1.foo>'something'

I am assuming (did not test it) that when t1 is huge the planner will
use the index, if present, on t2.foo. What do you think about?

#15

Il 18/12/2009 17.05, Marco Mariani ha scritto:

Quote:

Coniglio Sgabbiato wrote:

[ talking about foreign keys in mysql ]

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

it _seems_ to say that Mysql _could_ do that:

[...]

I tested it but it seems it does not work.

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

have to check relative moisture and wind direction first

#16

Il 18/12/2009 19.13, Coniglio Sgabbiato ha scritto:

Quote:

Il 18/12/2009 17.39, Marco Mariani ha scritto:
Coniglio Sgabbiato 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.

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

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.
[CUT]

I got your point, good one. Otherwise, I believe that an index on
foreign keys in little tables is a negligible overhead, either in disk
space and in cpu time on inserts. In big tables it might be useful if
the join condition is associate to another condition, for example, if
t1.foo references t2.foo then:

select * from t1 join t2 on t1.foo=t2.foo and t1.foo>'something'

I am assuming (did not test it) that when t1 is huge the planner will
use the index, if present, on t2.foo. What do you think about?

typo:

I am assuming (did not test it) that when t1 is huge the planner will
use the index, if present, on _t1.foo_. What do you think about?

#17

On Dec 18, 7:13*pm, Coniglio Sgabbiato <nob... (AT) nowhere (DOT) net> wrote:

Quote:

I got your point, good one. Otherwise, I believe that an index on
foreign keys in little tables is a negligible overhead,

We're not talking about an index in a little table -- but about an
index in a possibly HUGE table
So this is an index that slows down a lot of things.
An index that references a (relatively) small table, hence has a low
selectivity, so it is useless because using it would be slower than a
full scan.

#18

Il 20/12/2009 23.18, Marco Mariani ha scritto:

Quote:

On Dec 18, 7:13 pm, Coniglio Sgabbiato<nob... (AT) nowhere (DOT) net> wrote:

I got your point, good one. Otherwise, I believe that an index on
foreign keys in little tables is a negligible overhead,

We're not talking about an index in a little table -- but about an
index in a possibly HUGE table
So this is an index that slows down a lot of things.
An index that references a (relatively) small table, hence has a low
selectivity, so it is useless because using it would be slower than a
full scan.

True, I was assuming here that a fk on a big table might be there not
only for referential integrity. I was thinking about complex joins with
some "where" clause involved in the join, apart the identity fk-id (se
my post above), that will hold the planner from performing a full scan.
But my assumption it is not the general case, it is only _my_ case, so
your statement against the fk idx automatic creation is right, many
times it would be actually a waste of resources.
So... what about Mysql? It is OT but now I'm curious about their
decision to make it automatic

#19

On 2009-12-21, Coniglio Sgabbiato <nobody (AT) nowhere (DOT) net> wrote:

Quote:

So... what about Mysql? It is OT but now I'm curious about their
decision to make it automatic

it it seems like just more arbitrary mysql stuff.

#20

Coniglio Sgabbiato wrote:

Quote:

So... what about Mysql? It is OT but now I'm curious about their
decision to make it automatic

Mysql is even worse in that regard, since its SQL optimizer is... a very
special kid, as some parents would say, it will use indexes even when
scans would be more appropriate.
Several explanations with tests and charts are given in "Refactoring SQL
Applications", which I recommend
(http://oreilly.com/catalog/9780596514976) where the authors compare
Oracle, MSSQL and Mysql.
Needless to say, Postgres behaves more like the first two.