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.ibr.has...@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 :) > >
