Automatic Index Creation for Column Types

Hi,

I don't see indication that the capability described below exists in
Postgres (or any RDBMS), so this is likely a feature request --

For column types that are frequently defined in tables, and which are
typically indexed, it would be helpful to be able to specify in the type
definition of the column that its addition/creation into a table should
be automatically accompanied by the creation of an associated index.

Auto-index generation for a specific column type would be overridable
for specific tables, as you wouldn't always want to auto-create an index
at the time of column addition to a table. In the case of CREATE TABLE,
this would be less of a concern, and more of a concern for ALTER TABLE
ADD COLUMN.

Such a configuration would also specify the type of index.

What do you think ?

Thanks,
Charles Sheridan

Hello

2013/8/19 Charles Sheridan <cesheri(at)swbell(dot)net>

> Hi,
>
> I don't see indication that the capability described below exists in
> Postgres (or any RDBMS), so this is likely a feature request --
>
> For column types that are frequently defined in tables, and which are
> typically indexed, it would be helpful to be able to specify in the type
> definition of the column that its addition/creation into a table should
> be automatically accompanied by the creation of an associated index.
>
> Auto-index generation for a specific column type would be overridable
> for specific tables, as you wouldn't always want to auto-create an index
> at the time of column addition to a table. In the case of CREATE TABLE,
> this would be less of a concern, and more of a concern for ALTER TABLE
> ADD COLUMN.
>
> Such a configuration would also specify the type of index.
>
> What do you think ?
>
>
It is relatively dangerous, I think. What is typical type that can be
indexed?

Regards

Pavel

>
> Thanks,
> Charles Sheridan
>
>
>
> --
> Sent via pgsql-hackers mailing list (pgsql-hackers(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/**mailpref/pgsql-hackers<http://www.postgresql.org/mailpref/pgsql-hackers>
>

In my use, mainly arrays and hstore cols, including arrays of hstores.
These are column types that I suspect benefit from indexes more than
most column types.

Charles

On 13-08-19 8:14 AM, Pavel Stehule wrote:
> Hello
>
>
> 2013/8/19 Charles Sheridan <cesheri(at)swbell(dot)net
> <mailto:cesheri(at)swbell(dot)net>>
>
> Hi,
>
> I don't see indication that the capability described below exists in
> Postgres (or any RDBMS), so this is likely a feature request --
>
> For column types that are frequently defined in tables, and which are
> typically indexed, it would be helpful to be able to specify in
> the type
> definition of the column that its addition/creation into a table
> should
> be automatically accompanied by the creation of an associated index.
>
> Auto-index generation for a specific column type would be overridable
> for specific tables, as you wouldn't always want to auto-create an
> index
> at the time of column addition to a table. In the case of CREATE
> TABLE,
> this would be less of a concern, and more of a concern for ALTER TABLE
> ADD COLUMN.
>
> Such a configuration would also specify the type of index.
>
> What do you think ?
>
>
> It is relatively dangerous, I think. What is typical type that can be
> indexed?
>
> Regards
>
> Pavel
>
>
> Thanks,
> Charles Sheridan
>
>
>
> --
> Sent via pgsql-hackers mailing list (pgsql-hackers(at)postgresql(dot)org
> <mailto:pgsql-hackers(at)postgresql(dot)org>)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-hackers
>
>

2013/8/19 Charles Sheridan <cesheri(at)swbell(dot)net>

> In my use, mainly arrays and hstore cols, including arrays of hstores.
> These are column types that I suspect benefit from indexes more than most
> column types.
>

depends on usage

when you use a arrays for time series, then you don't use a index usually

and in hstore, you should to decide between GiST or GIN index. What should
be default?

I don't think so this feature is a good idea. Maybe enhancing a domains
about possibility to specify a index type for domain.

Regards

Pavel

> Charles
>
>
>
> On 13-08-19 8:14 AM, Pavel Stehule wrote:
>
> Hello
>
>
> 2013/8/19 Charles Sheridan <cesheri(at)swbell(dot)net>
>
>> Hi,
>>
>> I don't see indication that the capability described below exists in
>> Postgres (or any RDBMS), so this is likely a feature request --
>>
>> For column types that are frequently defined in tables, and which are
>> typically indexed, it would be helpful to be able to specify in the type
>> definition of the column that its addition/creation into a table should
>> be automatically accompanied by the creation of an associated index.
>>
>> Auto-index generation for a specific column type would be overridable
>> for specific tables, as you wouldn't always want to auto-create an index
>> at the time of column addition to a table. In the case of CREATE TABLE,
>> this would be less of a concern, and more of a concern for ALTER TABLE
>> ADD COLUMN.
>>
>> Such a configuration would also specify the type of index.
>>
>> What do you think ?
>>
>>
> It is relatively dangerous, I think. What is typical type that can be
> indexed?
>
> Regards
>
> Pavel
>
>
>>
>> Thanks,
>> Charles Sheridan
>>
>>
>>
>> --
>> Sent via pgsql-hackers mailing list (pgsql-hackers(at)postgresql(dot)org)
>> To make changes to your subscription:
>> http://www.postgresql.org/mailpref/pgsql-hackers
>>
>
>
>

On 08/19/2013 09:10 AM, Charles Sheridan wrote:
> Hi,
>
> I don't see indication that the capability described below exists in
> Postgres (or any RDBMS), so this is likely a feature request --
>
> For column types that are frequently defined in tables, and which are
> typically indexed, it would be helpful to be able to specify in the type
> definition of the column that its addition/creation into a table should
> be automatically accompanied by the creation of an associated index.
>
> Auto-index generation for a specific column type would be overridable
> for specific tables, as you wouldn't always want to auto-create an index
> at the time of column addition to a table. In the case of CREATE TABLE,
> this would be less of a concern, and more of a concern for ALTER TABLE
> ADD COLUMN.
>
> Such a configuration would also specify the type of index.
>
> What do you think ?
>
>
>

I think it's a bad idea. Indexes are not free and it would be a huge and
unwarranted assumption that they are wanted just because some column
type is used. If you want indexes it's up to you to create them. The
only exception is when they are created as an implementation artifact
for a table constraint, but even then you have to express the
constraint, it's not just assumed.

cheers

andrew

Agree, these are significant concerns. Just to make sure the scope of
the proposal is clear, I'm referring to col types created via CREATE
TYPE, i.e. locally-defined col types of much tighter granularity than
standard col types like INTEGER. Locally, one could adopt a naming
convention for these cols to identify that they have auto-generated
indices.

Best, Charles

On 13-08-19 8:42 AM, Andrew Dunstan wrote:
>
> On 08/19/2013 09:10 AM, Charles Sheridan wrote:
>> Hi,
>>
>> I don't see indication that the capability described below exists in
>> Postgres (or any RDBMS), so this is likely a feature request --
>>
>> For column types that are frequently defined in tables, and which are
>> typically indexed, it would be helpful to be able to specify in the type
>> definition of the column that its addition/creation into a table should
>> be automatically accompanied by the creation of an associated index.
>>
>> Auto-index generation for a specific column type would be overridable
>> for specific tables, as you wouldn't always want to auto-create an index
>> at the time of column addition to a table. In the case of CREATE TABLE,
>> this would be less of a concern, and more of a concern for ALTER TABLE
>> ADD COLUMN.
>>
>> Such a configuration would also specify the type of index.
>>
>> What do you think ?
>>
>>
>>
>
>
> I think it's a bad idea. Indexes are not free and it would be a huge
> and unwarranted assumption that they are wanted just because some
> column type is used. If you want indexes it's up to you to create
> them. The only exception is when they are created as an implementation
> artifact for a table constraint, but even then you have to express the
> constraint, it's not just assumed.
>
> cheers
>
> andrew
>
>
>