Creating Empty Index

On 11/01/2013 02:11 AM, naman.iitb wrote:

Hello

Is there a way by which we can create an empty index on a relation and later
fill it manually by inserting tuples.

I want to know how it can be done in Postgresql code.

Thanks in advance

What exactly would be the point? Indexes are automatically maintained by
postgres. Something that isn't doesn't seem to me to qualify for the
description of "index".

cheers

andrew

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

On Sat, Nov 2, 2013 at 10:28 PM, Andrew Dunstan <andrew@dunslane.net> wrote:

What exactly would be the point? Indexes are automatically maintained by
postgres. Something that isn't doesn't seem to me to qualify for the
description of "index".

Perhaps an index without data that could be used by the planner for
automatic query tuning to evaluate how a query could run if the index
exists? Like the concept of hypothetical indexes or something like the
possibility to do a CREATE/ALTER INDEX ... WITH [ NO ] DATA.
--
Michael

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Michael Paquier <michael.paquier@gmail.com> writes:

On Sat, Nov 2, 2013 at 10:28 PM, Andrew Dunstan <andrew@dunslane.net> wrote:

What exactly would be the point? Indexes are automatically maintained by
postgres. Something that isn't doesn't seem to me to qualify for the
description of "index".

Perhaps an index without data that could be used by the planner for
automatic query tuning to evaluate how a query could run if the index
exists? Like the concept of hypothetical indexes or something like the
possibility to do a CREATE/ALTER INDEX ... WITH [ NO ] DATA.

But we already provide support for hypothetical indexes via planner
plugins. Why would you need an actual empty index underlying that?

I agree with Andrew that the use-case for this hasn't been explained.

regards, tom lane

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

On Sun, Nov 3, 2013 at 3:01 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:

Michael Paquier <michael.paquier@gmail.com> writes:

On Sat, Nov 2, 2013 at 10:28 PM, Andrew Dunstan <andrew@dunslane.net> wrote:

What exactly would be the point? Indexes are automatically maintained by
postgres. Something that isn't doesn't seem to me to qualify for the
description of "index".

Perhaps an index without data that could be used by the planner for
automatic query tuning to evaluate how a query could run if the index
exists? Like the concept of hypothetical indexes or something like the
possibility to do a CREATE/ALTER INDEX ... WITH [ NO ] DATA.

But we already provide support for hypothetical indexes via planner
plugins. Why would you need an actual empty index underlying that?

Using only the planner hook? Forgive my lack of knowledge of the
planner, but this does not seem really straight-forward :)

One of the only documentation I found about hypothetical indexes was
this tutorial of PGCon 2010:
http://www.inf.puc-rio.br/~postgresql/conteudo/projeto1/Tutorial9/TUTORIAL_9_0_1.pdf
But in this case the grammar of CREATE INDEX has been modified, so
core code was changed as well.

Of course if Naman has something else in mind...
--
Michael

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers