Обсуждение: How to create virtual indexes on postgres

Поиск
Список
Период
Сортировка

How to create virtual indexes on postgres

От
Sreerama Manoj
Дата:
<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> 

Re: How to create virtual indexes on postgres

От
Michael Paquier
Дата:
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



Re: How to create virtual indexes on postgres

От
Sreerama Manoj
Дата:
<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> 

Re: How to create virtual indexes on postgres

От
Jim Nasby
Дата:
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