Gurjeet Singh Index Adviser User Interface

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
10 messages Options
Reply | Threaded
Open this post in threaded view
|

Gurjeet Singh Index Adviser User Interface

Yessica Brinkmann-2
Goodnight,
I would like to ask the following question:
As some of you may recall, I was doing my university thesis as a modification of Gurjeet Singh's Index Adviser.
Now I have finished the programming part.
But I have the following problem:
Gurjeet Singh's Index Adviser readme describes how to use an Index Adviser interface called pg_advise_index tool.
The readme mentions the following:
i) pg_advise_index tool.
---------------------

Create a file that contains all the queries (semicolon terminated; may
be multi-line) that are expected to be executed by the application; and
feed this file to the pg_advise_index tool with appropriate options.

pg_advise_index -d DB -h host -U user -s 10M -o advisory.sql workload.sql

pg_advise_index will open a connection with the PostgreSQL server by
setting appropriate session level options that will force the backend to load
the pg_index_adviser plugin. It will then prepend the keywords EXPLAIN to each
of the queries found in the workload file, and execute them against the backend.
For each query EXPLAINed, the backend will generate advice for each index that
might have been beneficial in executing these queries.

At the end, pg_advise_index will enumerate all the indexes suggested for
the current session, and output the CREATE INDEX statements for each of them.
Optinally, if the -size option was specified, pg_advise_index will output suggestions
for only those indexes, that fit into that size.
-------------------------------------------------- ----------------------------------------
To test my thesis, I need to use this interface pg_advise_index tool, but unfortunately I have not been able to use it yet.
I would like to know if any of you have any experience testing or using this interface, or if you understand what the readme says anyway.
In that case, please, can you help me. Now to test my thesis.
I've actually already tried to run this interface pg_advise_index tool in various ways but haven't been able to.
What I don't understand specifically is from which directory should I run the command
pg_advise_index -d DB -h host -U user -s 10M -o advisory.sql workload.sql
I will greatly appreciate any help, please.

The Index Adviser readme link is as follows:
Best regards,

Yessica Brinkmann
Reply | Threaded
Open this post in threaded view
|

Re: Gurjeet Singh Index Adviser User Interface

Yessica Brinkmann-2
I will greatly appreciate a help with this topic please. I really need to use that interface to be able to test my thesis. And I am not being able to use.
Best regards,
Yessica Brinkmann


-------- Mensaje original --------
Asunto: Gurjeet Singh Index Adviser User Interface
De: Yessica Brinkmann
Para: pgsql-general
CC:


Goodnight,
I would like to ask the following question:
As some of you may recall, I was doing my university thesis as a modification of Gurjeet Singh's Index Adviser.
Now I have finished the programming part.
But I have the following problem:
Gurjeet Singh's Index Adviser readme describes how to use an Index Adviser interface called pg_advise_index tool.
The readme mentions the following:
i) pg_advise_index tool.
---------------------

Create a file that contains all the queries (semicolon terminated; may
be multi-line) that are expected to be executed by the application; and
feed this file to the pg_advise_index tool with appropriate options.

pg_advise_index -d DB -h host -U user -s 10M -o advisory.sql workload.sql

pg_advise_index will open a connection with the PostgreSQL server by
setting appropriate session level options that will force the backend to load
the pg_index_adviser plugin. It will then prepend the keywords EXPLAIN to each
of the queries found in the workload file, and execute them against the backend.
For each query EXPLAINed, the backend will generate advice for each index that
might have been beneficial in executing these queries.

At the end, pg_advise_index will enumerate all the indexes suggested for
the current session, and output the CREATE INDEX statements for each of them.
Optinally, if the -size option was specified, pg_advise_index will output suggestions
for only those indexes, that fit into that size.
-------------------------------------------------- ----------------------------------------
To test my thesis, I need to use this interface pg_advise_index tool, but unfortunately I have not been able to use it yet.
I would like to know if any of you have any experience testing or using this interface, or if you understand what the readme says anyway.
In that case, please, can you help me. Now to test my thesis.
I've actually already tried to run this interface pg_advise_index tool in various ways but haven't been able to.
What I don't understand specifically is from which directory should I run the command
pg_advise_index -d DB -h host -U user -s 10M -o advisory.sql workload.sql
I will greatly appreciate any help, please.

The Index Adviser readme link is as follows:
Best regards,

Yessica Brinkmann
lup
Reply | Threaded
Open this post in threaded view
|

Re: Gurjeet Singh Index Adviser User Interface

lup


On 9/29/20 3:46 PM, Yessica Brinkmann wrote:
> I will greatly appreciate a help with this topic please. I really need
> to use that interface to be able to test my thesis. And I am not being
> able to use.
> Best regards,
> Yessica Brinkmann

I didn't see any reported error messages in your post.

And you followed all the installation instructions: apply patch, compile
install postgres, etc?  Not for the faint of heart, to be sure.

As presented

pg_advise_index -d DB -h host -U user -s 10M -o advisory.sql workload.sql

would need to be run in the directory containing advisory.sql and your
PATH would need to include the directory containing the
'pg_advise_index' executable.




Reply | Threaded
Open this post in threaded view
|

Re: Gurjeet Singh Index Adviser User Interface

Yessica Brinkmann-2
Goodnight,
Thank you very much for the answer.
I followed all the installation instructions: apply patch, compile
install postgres, etc.
I just tried everything with the UI ii) Manually (through psql session), which is also mentioned in the readme, and everything works for me. But to better test my thesis, I would also need to use the other user interface mentioned in the readme and that is precisely i) pg_advise_index tool.
I understand what you are telling me about the directory. I will be testing this way.
Many thanks.
Best regards,
Yessica brinkmann

El mar., 29 sept. 2020 a las 18:21, Rob Sargent (<[hidden email]>) escribió:


On 9/29/20 3:46 PM, Yessica Brinkmann wrote:
> I will greatly appreciate a help with this topic please. I really need
> to use that interface to be able to test my thesis. And I am not being
> able to use.
> Best regards,
> Yessica Brinkmann

I didn't see any reported error messages in your post.

And you followed all the installation instructions: apply patch, compile
install postgres, etc?  Not for the faint of heart, to be sure.

As presented

pg_advise_index -d DB -h host -U user -s 10M -o advisory.sql workload.sql

would need to be run in the directory containing advisory.sql and your
PATH would need to include the directory containing the
'pg_advise_index' executable.





Libre de virus. www.avg.com
Reply | Threaded
Open this post in threaded view
|

Re: Gurjeet Singh Index Adviser User Interface

Yessica Brinkmann-2
Goodnight,
I apologize please that today I was able to test the directory, since I had a health problem in recent days.
Well, I actually found the files corresponding to the pg_advise_index interface. But I was surprised that the executable is not present, but apparently this interface must be compiled by another part of the index adviser, to be able to execute it. It has a Makefile.
I tried to compile as follows, and I get the following errors:
root@debian:/home/yessica/Descargas/postgresql-8.3.23/contrib/pg_adviser_master/pg_advise# USE_PGXS = 1 make
You need to install postgresql-server-dev-X.Y for building a server-side extension or libpq-dev for building a client-side application.
make: *** There are no targets. High.
root@debian:/home/yessica/Descargas/postgresql-8.3.23/contrib/pg_adviser_master/pg_advise# make install
make: Nothing is done for 'install'.
I will greatly appreciate a help please, regarding these errors that I got. The truth is that I honestly  don't have much experience using Linux.
Best regards,
Yessica brinkmann

Libre de virus. www.avg.com

El mar., 29 sept. 2020 a las 18:55, Yessica Brinkmann (<[hidden email]>) escribió:
Goodnight,
Thank you very much for the answer.
I followed all the installation instructions: apply patch, compile
install postgres, etc.
I just tried everything with the UI ii) Manually (through psql session), which is also mentioned in the readme, and everything works for me. But to better test my thesis, I would also need to use the other user interface mentioned in the readme and that is precisely i) pg_advise_index tool.
I understand what you are telling me about the directory. I will be testing this way.
Many thanks.
Best regards,
Yessica brinkmann

El mar., 29 sept. 2020 a las 18:21, Rob Sargent (<[hidden email]>) escribió:


On 9/29/20 3:46 PM, Yessica Brinkmann wrote:
> I will greatly appreciate a help with this topic please. I really need
> to use that interface to be able to test my thesis. And I am not being
> able to use.
> Best regards,
> Yessica Brinkmann

I didn't see any reported error messages in your post.

And you followed all the installation instructions: apply patch, compile
install postgres, etc?  Not for the faint of heart, to be sure.

As presented

pg_advise_index -d DB -h host -U user -s 10M -o advisory.sql workload.sql

would need to be run in the directory containing advisory.sql and your
PATH would need to include the directory containing the
'pg_advise_index' executable.





Libre de virus. www.avg.com
Reply | Threaded
Open this post in threaded view
|

Re: Gurjeet Singh Index Adviser User Interface

Yessica Brinkmann-2
I also clarify that I tried to install the libpq-dev package already but could not install it because it depends on a non-installable package called libss10.9.8. And I had to apply apt --fix-broken install to fix the problem.

Libre de virus. www.avg.com

El jue., 8 oct. 2020 a las 22:08, Yessica Brinkmann (<[hidden email]>) escribió:
Goodnight,
I apologize please that today I was able to test the directory, since I had a health problem in recent days.
Well, I actually found the files corresponding to the pg_advise_index interface. But I was surprised that the executable is not present, but apparently this interface must be compiled by another part of the index adviser, to be able to execute it. It has a Makefile.
I tried to compile as follows, and I get the following errors:
root@debian:/home/yessica/Descargas/postgresql-8.3.23/contrib/pg_adviser_master/pg_advise# USE_PGXS = 1 make
You need to install postgresql-server-dev-X.Y for building a server-side extension or libpq-dev for building a client-side application.
make: *** There are no targets. High.
root@debian:/home/yessica/Descargas/postgresql-8.3.23/contrib/pg_adviser_master/pg_advise# make install
make: Nothing is done for 'install'.
I will greatly appreciate a help please, regarding these errors that I got. The truth is that I honestly  don't have much experience using Linux.
Best regards,
Yessica brinkmann

Libre de virus. www.avg.com

El mar., 29 sept. 2020 a las 18:55, Yessica Brinkmann (<[hidden email]>) escribió:
Goodnight,
Thank you very much for the answer.
I followed all the installation instructions: apply patch, compile
install postgres, etc.
I just tried everything with the UI ii) Manually (through psql session), which is also mentioned in the readme, and everything works for me. But to better test my thesis, I would also need to use the other user interface mentioned in the readme and that is precisely i) pg_advise_index tool.
I understand what you are telling me about the directory. I will be testing this way.
Many thanks.
Best regards,
Yessica brinkmann

El mar., 29 sept. 2020 a las 18:21, Rob Sargent (<[hidden email]>) escribió:


On 9/29/20 3:46 PM, Yessica Brinkmann wrote:
> I will greatly appreciate a help with this topic please. I really need
> to use that interface to be able to test my thesis. And I am not being
> able to use.
> Best regards,
> Yessica Brinkmann

I didn't see any reported error messages in your post.

And you followed all the installation instructions: apply patch, compile
install postgres, etc?  Not for the faint of heart, to be sure.

As presented

pg_advise_index -d DB -h host -U user -s 10M -o advisory.sql workload.sql

would need to be run in the directory containing advisory.sql and your
PATH would need to include the directory containing the
'pg_advise_index' executable.





Libre de virus. www.avg.com
Reply | Threaded
Open this post in threaded view
|

Re: Gurjeet Singh Index Adviser User Interface

Yessica Brinkmann-2
I will greatly appreciate a help please with this topic. I really need a lot to be able to use this interface to be able to test my thesis well. I really don't quite understand what installing postgresql-server-dev-X.Y refers to. And I really don't really know how to install it too. If you can give me a guide at least about this please? And in which version should I install it? I am using Postgresql 8.3.23, I really use this version because the Index Adviser only works with this version of Postgresql. And also I do not understand well if installing that already solves everything or should I install an additional package?
Best regards,
Yessica Brinkmann.


Libre de virus. www.avg.com

El jue., 8 oct. 2020 a las 22:21, Yessica Brinkmann (<[hidden email]>) escribió:
I also clarify that I tried to install the libpq-dev package already but could not install it because it depends on a non-installable package called libss10.9.8. And I had to apply apt --fix-broken install to fix the problem.

Libre de virus. www.avg.com

El jue., 8 oct. 2020 a las 22:08, Yessica Brinkmann (<[hidden email]>) escribió:
Goodnight,
I apologize please that today I was able to test the directory, since I had a health problem in recent days.
Well, I actually found the files corresponding to the pg_advise_index interface. But I was surprised that the executable is not present, but apparently this interface must be compiled by another part of the index adviser, to be able to execute it. It has a Makefile.
I tried to compile as follows, and I get the following errors:
root@debian:/home/yessica/Descargas/postgresql-8.3.23/contrib/pg_adviser_master/pg_advise# USE_PGXS = 1 make
You need to install postgresql-server-dev-X.Y for building a server-side extension or libpq-dev for building a client-side application.
make: *** There are no targets. High.
root@debian:/home/yessica/Descargas/postgresql-8.3.23/contrib/pg_adviser_master/pg_advise# make install
make: Nothing is done for 'install'.
I will greatly appreciate a help please, regarding these errors that I got. The truth is that I honestly  don't have much experience using Linux.
Best regards,
Yessica brinkmann

Libre de virus. www.avg.com

El mar., 29 sept. 2020 a las 18:55, Yessica Brinkmann (<[hidden email]>) escribió:
Goodnight,
Thank you very much for the answer.
I followed all the installation instructions: apply patch, compile
install postgres, etc.
I just tried everything with the UI ii) Manually (through psql session), which is also mentioned in the readme, and everything works for me. But to better test my thesis, I would also need to use the other user interface mentioned in the readme and that is precisely i) pg_advise_index tool.
I understand what you are telling me about the directory. I will be testing this way.
Many thanks.
Best regards,
Yessica brinkmann

El mar., 29 sept. 2020 a las 18:21, Rob Sargent (<[hidden email]>) escribió:


On 9/29/20 3:46 PM, Yessica Brinkmann wrote:
> I will greatly appreciate a help with this topic please. I really need
> to use that interface to be able to test my thesis. And I am not being
> able to use.
> Best regards,
> Yessica Brinkmann

I didn't see any reported error messages in your post.

And you followed all the installation instructions: apply patch, compile
install postgres, etc?  Not for the faint of heart, to be sure.

As presented

pg_advise_index -d DB -h host -U user -s 10M -o advisory.sql workload.sql

would need to be run in the directory containing advisory.sql and your
PATH would need to include the directory containing the
'pg_advise_index' executable.





Libre de virus. www.avg.com
Reply | Threaded
Open this post in threaded view
|

Re: Gurjeet Singh Index Adviser User Interface

David G Johnston
On Fri, Oct 9, 2020 at 5:20 PM Yessica Brinkmann <[hidden email]> wrote:
I am using Postgresql 8.3.23, I really use this version because the Index Adviser only works with this version of Postgresql.

I suggest first figuring out whether you are able to successfully install the current PostgreSQL Server (git master branch) on a current Linux release (if you really want to go Windows feel free but there is less help to be had there.)  Until you can get that to work you should not proceed any further on attempting to modify PostgreSQL server.

Then, instead of trying to get ancient PostgreSQL server code running on modern hardware, you should focus your attention on getting ancient third-party modifications to the PostgreSQL server code to work on the modern PostgreSQL server.

As an aside, I noticed the "USE_PGXS = 1", it didn't exist back in the 8.3 days.

As far as I can see, in a limited read of the readme summary, all of the relevant code needs to exist within the PostgreSQL server source tree, the pg_advise_index being placed in the contrib section just like, for example, hstore or pg_prewarm (which I think has a command line interface).  Then you modify, build and install the server and the modifications are fully incorporated as core+contrib code.  This seems all quite straight-forward, with plenty of examples to copy from, for dealing with the overall structural aspects of the codebase and build/install processes.  Which brings me back to first understanding how the unaltered system works before trying to make alterations.  You may choose to learn that using an 8.3 server but you will probably find little help if you go that route.

David J.






David J.

Reply | Threaded
Open this post in threaded view
|

Re: Gurjeet Singh Index Adviser User Interface

Yessica Brinkmann-2
Thank you very much for the answer.
I will be trying to understand and test the indicated.
Best regards,
Yessica Brinkmann.

Libre de virus. www.avg.com

El vie., 9 oct. 2020 a las 23:02, David G. Johnston (<[hidden email]>) escribió:
On Fri, Oct 9, 2020 at 5:20 PM Yessica Brinkmann <[hidden email]> wrote:
I am using Postgresql 8.3.23, I really use this version because the Index Adviser only works with this version of Postgresql.

I suggest first figuring out whether you are able to successfully install the current PostgreSQL Server (git master branch) on a current Linux release (if you really want to go Windows feel free but there is less help to be had there.)  Until you can get that to work you should not proceed any further on attempting to modify PostgreSQL server.

Then, instead of trying to get ancient PostgreSQL server code running on modern hardware, you should focus your attention on getting ancient third-party modifications to the PostgreSQL server code to work on the modern PostgreSQL server.

As an aside, I noticed the "USE_PGXS = 1", it didn't exist back in the 8.3 days.

As far as I can see, in a limited read of the readme summary, all of the relevant code needs to exist within the PostgreSQL server source tree, the pg_advise_index being placed in the contrib section just like, for example, hstore or pg_prewarm (which I think has a command line interface).  Then you modify, build and install the server and the modifications are fully incorporated as core+contrib code.  This seems all quite straight-forward, with plenty of examples to copy from, for dealing with the overall structural aspects of the codebase and build/install processes.  Which brings me back to first understanding how the unaltered system works before trying to make alterations.  You may choose to learn that using an 8.3 server but you will probably find little help if you go that route.

David J.






David J.

Reply | Threaded
Open this post in threaded view
|

Re: Gurjeet Singh Index Adviser User Interface

Jony Cohen
In reply to this post by Yessica Brinkmann-2
Hi Yessica,
Postgres 8.3 is very old, If you are using that simply because of the index advisor there are a few more “modern” options.

I at the time wrote such a tool for Postgres 9.4 (it has many features that the one by Gurjeet didn’t have like CTE and partial indexes support)
Alas I didn’t have a chance to update it to 9.6 & 10 as there were many breaking changes.

And there is even a newer module to generate hypothetical indexes to allow you to test various options before building the actual index: https://github.com/HypoPG/hypopg 

You are missing so much functionality and features that any improvements you get by a better index recommendation will still be slower then running a new version with less optimal indexes.

I would consider using a new version.

Kind Regards,
Jony

On 10 Oct 2020, at 3:19, Yessica Brinkmann <[hidden email]> wrote:

I will greatly appreciate a help please with this topic. I really need a lot to be able to use this interface to be able to test my thesis well. I really don't quite understand what installing postgresql-server-dev-X.Y refers to. And I really don't really know how to install it too. If you can give me a guide at least about this please? And in which version should I install it? I am using Postgresql 8.3.23, I really use this version because the Index Adviser only works with this version of Postgresql. And also I do not understand well if installing that already solves everything or should I install an additional package?
Best regards,
Yessica Brinkmann.


Libre de virus. www.avg.com
<a href="x-msg://4/#DAB4FAD8-2DD7-40BB-A1B8-4E2AA1F9FDF2" width="1" height="1" class="">

El jue., 8 oct. 2020 a las 22:21, Yessica Brinkmann (<[hidden email]>) escribió:
I also clarify that I tried to install the libpq-dev package already but could not install it because it depends on a non-installable package called libss10.9.8. And I had to apply apt --fix-broken install to fix the problem.

Libre de virus. www.avg.com
<a href="x-msg://4/#m_-9019273329787422367_DAB4FAD8-2DD7-40BB-A1B8-4E2AA1F9FDF2" width="1" height="1" class="">

El jue., 8 oct. 2020 a las 22:08, Yessica Brinkmann (<[hidden email]>) escribió:
Goodnight,
I apologize please that today I was able to test the directory, since I had a health problem in recent days.
Well, I actually found the files corresponding to the pg_advise_index interface. But I was surprised that the executable is not present, but apparently this interface must be compiled by another part of the index adviser, to be able to execute it. It has a Makefile.
I tried to compile as follows, and I get the following errors:
root@debian:/home/yessica/Descargas/postgresql-8.3.23/contrib/pg_adviser_master/pg_advise# USE_PGXS = 1 make
You need to install postgresql-server-dev-X.Y for building a server-side extension or libpq-dev for building a client-side application.
make: *** There are no targets. High.
root@debian:/home/yessica/Descargas/postgresql-8.3.23/contrib/pg_adviser_master/pg_advise# make install
make: Nothing is done for 'install'.
I will greatly appreciate a help please, regarding these errors that I got. The truth is that I honestly  don't have much experience using Linux.
Best regards,
Yessica brinkmann

Libre de virus. www.avg.com
<a href="x-msg://4/#m_-9019273329787422367_m_-2818491497838977803_DAB4FAD8-2DD7-40BB-A1B8-4E2AA1F9FDF2" width="1" height="1" class="">

El mar., 29 sept. 2020 a las 18:55, Yessica Brinkmann (<[hidden email]>) escribió:
Goodnight,
Thank you very much for the answer.
I followed all the installation instructions: apply patch, compile
install postgres, etc.
I just tried everything with the UI ii) Manually (through psql session), which is also mentioned in the readme, and everything works for me. But to better test my thesis, I would also need to use the other user interface mentioned in the readme and that is precisely i) pg_advise_index tool.
I understand what you are telling me about the directory. I will be testing this way.
Many thanks.
Best regards,
Yessica brinkmann

El mar., 29 sept. 2020 a las 18:21, Rob Sargent (<[hidden email]>) escribió:


On 9/29/20 3:46 PM, Yessica Brinkmann wrote:
> I will greatly appreciate a help with this topic please. I really need
> to use that interface to be able to test my thesis. And I am not being
> able to use.
> Best regards,
> Yessica Brinkmann

I didn't see any reported error messages in your post.

And you followed all the installation instructions: apply patch, compile
install postgres, etc?  Not for the faint of heart, to be sure.

As presented

pg_advise_index -d DB -h host -U user -s 10M -o advisory.sql workload.sql

would need to be run in the directory containing advisory.sql and your
PATH would need to include the directory containing the
'pg_advise_index' executable.





Libre de virus. www.avg.com
<a href="x-msg://4/#m_-9019273329787422367_m_-2818491497838977803_m_-6212125912420465024_DAB4FAD8-2DD7-40BB-A1B8-4E2AA1F9FDF2" width="1" height="1" class="">