Re: Inquiry/Help with pg_adviser (problem in index_create function for creating indexes)

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 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

On Sun, Jun 25, 2023 at 2:50 AM Ahmed Ibrahim <ahmed(dot)ibr(dot)hashim(at)gmail(dot)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 :)
>
>