Thread: Hypothetical Indexes - PostgreSQL extension - PGCON 2010

Hypothetical Indexes - PostgreSQL extension - PGCON 2010

From
Ana Carolina Brito de Almeida
Date:
<pre><font size="2">Hackers,<br /><br />We would like to inform you all that our extension to PostgreSQL, that includes
hypotheticalindexes (and soon index self-tuning), is available through a sourgeforge project. <br />This was suggested
atPgCon 2010 and we hope some of you may find it useful, contribute and give us your feedback.<br /> 
<br />Link to project: </font><br /><font color="#ff0000"><a href="http://sourceforge.net/projects/hypotheticalind/"
target="_blank">http://sourceforge.net/projects/hypotheticalind/</a></font><br /><br />Thanks,<br />Sergio Lifschitz<br
/>
Ana Carolina Almeida<br /></pre>

Re: Hypothetical Indexes - PostgreSQL extension - PGCON 2010

From
Josh Berkus
Date:
Ana,

> We would like to inform you all that our extension to PostgreSQL, that includes hypothetical indexes (and soon index
self-tuning),is available through a sourgeforge project. 
 
> This was suggested at PgCon 2010 and we hope some of you may find it useful, contribute and give us your feedback.

Great to see this being available finally!

--                                  -- Josh Berkus                                    PostgreSQL Experts Inc.
                        http://www.pgexperts.com
 


Re: Hypothetical Indexes - PostgreSQL extension - PGCON 2010

From
Jeroen Vermeulen
Date:
On 2010-12-02 00:48, Ana Carolina Brito de Almeida wrote:

> We would like to inform you all that our extension to PostgreSQL, that includes hypothetical indexes (and soon index
self-tuning),is available through a sourgeforge project.
 
> This was suggested at PgCon 2010 and we hope some of you may find it useful, contribute and give us your feedback.

Looking at the sourceforge page, I'm left with one burning question: 
what are they for?

I can see what a hypothetical index is, but neither the project pages 
nor the README in the tarball say why I might want one.  I'd be quite 
interested to know that.


Jeroen


Re: Hypothetical Indexes - PostgreSQL extension - PGCON 2010

From
Richard Huxton
Date:
On 03/12/10 08:14, Jeroen Vermeulen wrote:
> On 2010-12-02 00:48, Ana Carolina Brito de Almeida wrote:
>
>> We would like to inform you all that our extension to PostgreSQL, that
>> includes hypothetical indexes (and soon index self-tuning), is
>> available through a sourgeforge project.

> Looking at the sourceforge page, I'm left with one burning question:
> what are they for?

I believe they're for performance testing. Add hypothetical index (takes 
very little time). Check estimated costs with EXPLAIN. If good, add real 
index (takes lots of time).

Of course, they're also good for indexing hypothetical data ;-)

--   Richard Huxton  Archonet Ltd


Re: Hypothetical Indexes - PostgreSQL extension - PGCON 2010

From
Jeroen Vermeulen
Date:
On 2010-12-03 19:44, Sergio Lifschitz wrote:
> Indeed, hypothetical indexes are good to check potentially good
> configurations without harming the whole system with actual index
> creation. Please observer that we've added an "explain hypothetical"
> command, that will include plans considering hypothetical indexes! We'll
> try to add a simple case study that would help those wondering about
> this project.

That sounds very useful indeed!


Jeroen


Re: Hypothetical Indexes - PostgreSQL extension - PGCON 2010

From
Ana Carolina Brito de Almeida
Date:
Jeroen,<br /><br />We add a simple case study (sourceforge page):<br /><a
href="http://sourceforge.net/projects/hypotheticalind/files/TUTORIAL_8_4.pdf/download">http://sourceforge.net/projects/hypotheticalind/files/TUTORIAL_8_4.pdf/download</a><br
/><br/><span class="long_text" id="result_box" lang="en"><span style="" title="">Although this tutorial is for version
8.4,it also applies to other versions.</span></span><br /><br />Att,<br />Ana Carolina<br /><br /><div
class="gmail_quote">2010/12/3 Jeroen Vermeulen <span dir="ltr"><<a
href="mailto:jtv@xs4all.nl">jtv@xs4all.nl</a>></span><br/><blockquote class="gmail_quote" style="border-left: 1px
solidrgb(204, 204, 204); margin: 0pt 0pt 0pt 0.8ex; padding-left: 1ex;"><div class="im">On 2010-12-03 19:44, Sergio
Lifschitzwrote:<br /><blockquote class="gmail_quote" style="border-left: 1px solid rgb(204, 204, 204); margin: 0pt 0pt
0pt0.8ex; padding-left: 1ex;"> Indeed, hypothetical indexes are good to check potentially good<br /> configurations
withoutharming the whole system with actual index<br /> creation. Please observer that we've added an "explain
hypothetical"<br/> command, that will include plans considering hypothetical indexes! We'll<br /> try to add a simple
casestudy that would help those wondering about<br /> this project.<br /></blockquote><br /></div> That sounds very
usefulindeed!<br /><font color="#888888"><br /><br /> Jeroen<br /></font></blockquote></div><br /> 

Re: Hypothetical Indexes - PostgreSQL extension - PGCON 2010

From
Sergio Lifschitz
Date:
Indeed, hypothetical indexes are good to check potentially good 
configurations without harming the whole system with actual index 
creation. Please observer that we've added an "explain hypothetical" 
command, that will include plans considering hypothetical indexes! We'll 
try to add a simple case study that would help those wondering about 
this project.

Sergio

On 3/12/2010 08:06, Richard Huxton wrote:
> On 03/12/10 08:14, Jeroen Vermeulen wrote:
>> On 2010-12-02 00:48, Ana Carolina Brito de Almeida wrote:
>>
>>> We would like to inform you all that our extension to PostgreSQL, that
>>> includes hypothetical indexes (and soon index self-tuning), is
>>> available through a sourgeforge project.
>
>> Looking at the sourceforge page, I'm left with one burning question:
>> what are they for?
>
> I believe they're for performance testing. Add hypothetical index 
> (takes very little time). Check estimated costs with EXPLAIN. If good, 
> add real index (takes lots of time).
>
> Of course, they're also good for indexing hypothetical data ;-)
>


Re: Hypothetical Indexes - PostgreSQL extension - PGCON 2010

From
Jeroen Vermeulen
Date:
On 2010-12-03 20:49, Ana Carolina Brito de Almeida wrote:

> We add a simple case study (sourceforge page):
> http://sourceforge.net/projects/hypotheticalind/files/TUTORIAL_8_4.pdf/download

Great, thanks!

I'll try to write a bit more about it later:

http://pqxx.org/development/libpqxx/wiki/HypotheticalIndexes


Jeroen