Can we add syntax for references auto create index or not.
When we create table, some column use foreign key references.
Now PostgreSQL don't create index for the FK, and there is no problem.
But when some body need the index to speed up the query within these APP,
they need to add the index manual one-by-one when has many tables.
If we can add syntax for auto create index for FK by user's choose, and
default not create?
On Tue, May 26, 2015 at 7:03 PM, digoal zhou <digoal.zhou@gmail.com> wrote:
When we create table, some column use foreign key references.
Now PostgreSQL don't create index for the FK, and there is no problem.
But when some body need the index to speed up the query within these APP,
they need to add the index manual one-by-one when has many tables.
If we can add syntax for auto create index for FK by user's choose, and
default not create?
I presume you mean to modify the CREATE TABLE statement.
How does this help solve the problem "when some body need the index to
speed up the query"?
My first impression is that I like the idea. The fact that it is not
supported by the SQL standard is a drawback Do any other vendors do this?
David J.
2015-05-28 1:41 GMT+08:00 David G. Johnston <david.g.johnston@gmail.com>:
On Tue, May 26, 2015 at 7:03 PM, digoal zhou <digoal.zhou@gmail.com>
wrote:When we create table, some column use foreign key references.
Now PostgreSQL don't create index for the FK, and there is no problem.
But when some body need the index to speed up the query within these APP,
they need to add the index manual one-by-one when has many tables.
If we can add syntax for auto create index for FK by user's choose, and
default not create?I presume you mean to modify the CREATE TABLE statement.
How does this help solve the problem "when some body need the index to
speed up the query"?
Sometimes, when user need delete FK's parent table's tuple, FK has an
index will improve the performance.
or sometime, user need query FK table with FK's condition (assume it need
the btree index).
There is no other database product do the thing, but if PG has the
syntax(when add FK, user can choose whether or not create index), users
will happy to use it.
Regards,
digoal
Show quoted text
My first impression is that I like the idea. The fact that it is not
supported by the SQL standard is a drawback Do any other vendors do this?David J.
On Wednesday, May 27, 2015, digoal zhou <digoal.zhou@gmail.com> wrote:
2015-05-28 1:41 GMT+08:00 David G. Johnston <david.g.johnston@gmail.com
<javascript:_e(%7B%7D,'cvml','david.g.johnston@gmail.com');>>:On Tue, May 26, 2015 at 7:03 PM, digoal zhou <digoal.zhou@gmail.com
<javascript:_e(%7B%7D,'cvml','digoal.zhou@gmail.com');>> wrote:When we create table, some column use foreign key references.
Now PostgreSQL don't create index for the FK, and there is no problem.
But when some body need the index to speed up the query within these
APP, they need to add the index manual one-by-one when has many tables.
If we can add syntax for auto create index for FK by user's choose, and
default not create?I presume you mean to modify the CREATE TABLE statement.
How does this help solve the problem "when some body need the index to
speed up the query"?Sometimes, when user need delete FK's parent table's tuple, FK has an
index will improve the performance.
or sometime, user need query FK table with FK's condition (assume it need
the btree index).
I understand why one would want a FK but if they actually thought they
would need it they would have issued a create index at the same time (well,
just after) they create the table. Seeing a poorly performing query and
going, "crap, I forgot the index" isn't solved by this though it may make
the need more obvious while the user is writing the create table in the
first place.
There is no other database product do the thing, but if PG has the
syntax(when add FK, user can choose whether or not create index), users
will happy to use it.
Are you asking if such a featured would be accepted or pleading for someone
to code it?
David J.