Обсуждение: How to create virtual indexes on postgres
<div dir="ltr"><span style="font-size:12.8000001907349px">Hi,</span><div style="font-size:12.8000001907349px"> I usePostgres 9.4 database.Now,I am optimizing the queries by using the results of "explain" and "explain analyze",SometimesI am creating Indexes to optimize them. But, I was not successful sometimes as even I create Index to optimizethem, the planner is not using them .</div><div style="font-size:12.8000001907349px"><br /></div><div style="font-size:12.8000001907349px"><spanstyle="white-space:pre-wrap"> </span>So my question was can we know whether theplanner will use the index before actually creating a real Index..or can we create "virtual" or "Hypothetical" Indexthose can only be known to the planner and not the user or Is there any alternative to do it..If present,share withme.</div></div>
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
<div dir="ltr">But, it runs with Postgres 9.1 version...But I use 9.4..I think I cant use that. Or as an alternative Isthere any provision in postgres to know use(Increase in Performance) of an index before creating that index.</div><divclass="gmail_extra"><br /><div class="gmail_quote">On Thu, Feb 26, 2015 at 5:37 PM, Michael Paquier <spandir="ltr"><<a href="mailto:michael.paquier@gmail.com" target="_blank">michael.paquier@gmail.com</a>></span> wrote:<br/><blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex"><span class="">OnThu, Feb 26, 2015 at 6:20 PM, Sreerama Manoj<br /> <<a href="mailto:manoj.sreerama973@gmail.com">manoj.sreerama973@gmail.com</a>>wrote:<br /> > So my question was can weknow whether the planner will use the index<br /> > before actually creating a real Index..or can we create "virtual"or<br /> > "Hypothetical" Index those can only be known to the planner and not the user<br /> > or Is thereany alternative to do it..If present,share with me.<br /><br /></span>No, the index needs to be created to allow theplanner to use it. This<br /> reminds me of this project though, that if I recall correctly has<br /> patches Postgresto allow the use of hypothetical indexes:<br /><a href="https://sourceforge.net/projects/hypotheticalind/" target="_blank">https://sourceforge.net/projects/hypotheticalind/</a><br/><span class="HOEnZb"><font color="#888888">--<br/> Michael<br /></font></span></blockquote></div><br /></div>
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