Re: Inquiry/Help with pg_adviser (problem in index_create function for creating indexes)
| От | Ahmed Ibrahim |
|---|---|
| Тема | Re: Inquiry/Help with pg_adviser (problem in index_create function for creating indexes) |
| Дата | |
| Msg-id | CAHiW8twbPDsS0w7cuhiu-yDkC0Syb-wA5D0TBd5xu3ArOyzpOQ@mail.gmail.com обсуждение исходный текст |
| Ответ на | Inquiry/Help with pg_adviser (problem in index_create function for creating indexes) (Ahmed Ibrahim <ahmed.ibr.hashim@gmail.com>) |
| Ответы |
Re: Inquiry/Help with pg_adviser (problem in index_create function for creating indexes)
(Heikki Linnakangas <hlinnaka@iki.fi>)
|
| Список | pgsql-hackers |
Hi,
Since some people prefer plain text over screenshots/pdfs (but I think the pdf is more readable), I will post the problem here, in case anyone can help. I will appreciate that :)
The idea behind what is being done is creating virtual indexes, and measuring the query cost after creating those indexes, and see whether we will get a better cost or not, and maximize the benefit from those choices.
Since some people prefer plain text over screenshots/pdfs (but I think the pdf is more readable), I will post the problem here, in case anyone can help. I will appreciate that :)
The full current code (PR is still draft) can be found at https://github.com/DrPostgres/pg_adviser/pull/4
The idea behind what is being done is creating virtual indexes, and measuring the query cost after creating those indexes, and see whether we will get a better cost or not, and maximize the benefit from those choices.
So far, the project is okay and compiling/working successfully (with Postgres 16), but the problem is when creating
the virtual indexes (with version 16), I give it flag INDEX_CREATE_SKIP_BUILD (just like it was with version 8.3 and was
working)
==================================================After that, the index gets created successfully, but when trying to call standard_planner for the same query with the new index created (to see
how the query cost changed), I get the following error
2023-06-24 19:09:21.843 EEST [45000] ERROR: could not read block 0 in file "base/16384/139323": read only 0 of 8192 bytes
2023-06-24 19:09:21.843 EEST [45000] STATEMENT: explain select * from t where a > 5000;
ERROR: could not read block 0 in file "base/16384/139323": read only 0 of 8192 bytes
=====================================================
=====================================================
I tried too many things, like letting it build the whole index, or REINDEX ing it after being created. I also debugged
PostgreSQL source code to see where it stops, but wasn’t able to solve the problem.
When trying to let it build the Index, the function index_build gets errors
One last thing I tried is giving it flag INDEX_CREATE_SKIP_BUILD and INDEX_CREATE_CONCURRENT , the index gets created
successfully but when doing so, the query cost never changes, and the query never uses the index. When I try to
REINDEX it, I just get that query is aborted.
Although I think it might be a trivial thing I might have forgotten :D, I would appreciate any help as I have been
trying to fix this for more than 2 days.
Some screenshots can be found in the pdf mentioned in the first mail.
Thanks all
Some screenshots can be found in the pdf mentioned in the first mail.
Thanks all
On Sun, Jun 25, 2023 at 2:50 AM Ahmed Ibrahim <ahmed.ibr.hashim@gmail.com> wrote:
Hi everyone!
I am new to PostgreSQL community and working currently on project pg_adviser [https://github.com/DrPostgres/pg_adviser/]
The extension last worked with version 8.3, and currently I am working to make it support version 16 and then the other active versions.
I will give a brief about the extension:
It's used to recommend useful indexes for a set of queries. It does that by planning the query initially and seeing the initial cost and then creating *virtual* indexes (based on the query and columns used in it, ..etc) and planning again to see how those indexes changed the cost.
The problem I am facing is in creating those indexes in Postgres 16 (while calling *index_create*), and you can find here a detail description about the problem along with the code/PR
https://drive.google.com/file/d/1x2PnDEfEo094vgNiBd1-BfJtB5Fovrih/view
I would appreciate any help. Thanks :)
В списке pgsql-hackers по дате отправления:
Следующее
От: Pavel LuzanovДата:
Сообщение: Re: psql: Add role's membership options to the \du+ command