How to create virtual indexes on postgres
Hi,
I use Postgres 9.4 database.Now,I am optimizing the queries by using
the results of "explain" and "explain analyze",Sometimes I am creating
Indexes to optimize them. But, I was not successful sometimes as even I
create Index to optimize them, the planner is not using them .
So my question was can we know whether the planner will use the index
before actually creating a real Index..or can we create "virtual" or
"Hypothetical" Index those can only be known to the planner and not the
user or Is there any alternative to do it..If present,share with me.
On Thu, Feb 26, 2015 at 6:20 PM, Sreerama Manoj
<manoj.sreerama973@gmail.com> wrote:
So my question was can we know whether the planner will use the index
before actually creating a real Index..or can we create "virtual" or
"Hypothetical" Index those can only be known to the planner and not the user
or Is there any alternative to do it..If present,share with me.
No, the index needs to be created to allow the planner to use it. This
reminds me of this project though, that if I recall correctly has
patches Postgres to allow the use of hypothetical indexes:
https://sourceforge.net/projects/hypotheticalind/
--
Michael
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
But, it runs with Postgres 9.1 version...But I use 9.4..I think I cant use
that. Or as an alternative Is there any provision in postgres to know
use(Increase in Performance) of an index before creating that index.
On Thu, Feb 26, 2015 at 5:37 PM, Michael Paquier <michael.paquier@gmail.com>
wrote:
Show quoted text
On Thu, Feb 26, 2015 at 6:20 PM, Sreerama Manoj
<manoj.sreerama973@gmail.com> wrote:So my question was can we know whether the planner will use the index
before actually creating a real Index..or can we create "virtual" or
"Hypothetical" Index those can only be known to the planner and not theuser
or Is there any alternative to do it..If present,share with me.
No, the index needs to be created to allow the planner to use it. This
reminds me of this project though, that if I recall correctly has
patches Postgres to allow the use of hypothetical indexes:
https://sourceforge.net/projects/hypotheticalind/
--
Michael
On 2/26/15 6:17 AM, Sreerama Manoj wrote:
But, it runs with Postgres 9.1 version...But I use 9.4..I think I cant
use that. Or as an alternative Is there any provision in postgres to
know use(Increase in Performance) of an index before creating that index.
No. It might not be too hard to port the hypothetical index work to 9.4
though.
Also, just to let you know, this is really a topic for pgsql-general,
not pgsql-hackers. It's also best to reply to list emails in-line, or at
the bottom, not at the top.
--
Jim Nasby, Data Architect, Blue Treble Consulting
Data in Trouble? Get it in Treble! http://BlueTreble.com
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers