Re: Can we add syntax for references auto create index or not.

On Wednesday, May 27, 2015, digoal zhou <digoal(dot)zhou(at)gmail(dot)com> wrote:
>
> 2015-05-28 1:41 GMT+08:00 David G. Johnston <david(dot)g(dot)johnston(at)gmail(dot)com
> <javascript:_e(%7B%7D,'cvml','david(dot)g(dot)johnston(at)gmail(dot)com');>>:
>
>> On Tue, May 26, 2015 at 7:03 PM, digoal zhou <digoal(dot)zhou(at)gmail(dot)com
>> <javascript:_e(%7B%7D,'cvml','digoal(dot)zhou(at)gmail(dot)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.