When the user clicks to the second page , it will see the next set of rows i.e 100 to 200 and next will see 200 to 300 and so on till the result set finishes.
As others have pointed out, that still makes no sense. You will either fail to show certain rows completely, or have a stale view with additional tracking overhead.
All those attributes are majorly Varchar and numeric in nature , so not sure if any options exist there for these?
Nothing built in, but there are application-level tricks to combine fields, depending on what they are. Whether that would work for your use case or be an overall performance benefit is impossible to say without hard data. Normally, I'd say don't worry about that but - not to belabor the point - 500 columns is a lot, to the point where normal advice may not apply.
If PR_ID is a must in the Join criteria between these table tables table1, table2 in all the queries, then is it advisable to have a composite index like (pr_id, mid), (pr_id,cid) etc rather than having index on individual columns?
No - individual indexes are better, and Postgres has no problem combining them when needed.
Actually this inner query is doing the main work, i.e finding the search results based on the input search criteria. The outer query is just fetching the results from the inner query along with count(*), to pass on to the API , so as to calculate and show the user how many pages there total with a full result set. basically it will count(*)/N records per page, and that figure will be displayed in the first page of the UI screen.
Okay, understood. But back to the pagination discussion, this number is relatively meaningless on a rapidly changing table.
Sure will try to test and see how it behaves when the number of simultaneous queries (here 32/4=8 concurrent queries) exceed the max_parallel_workers limit. Though I am expecting the further queries exceeding the limit might get serialized.
Yes - if there are not enough workers available, it will run with a reduced number of workers, including possibly zero. You can see that when you run an explain analyze, it will show you the number of workers it wants and the number if actually was able to get.
Cheers,
Greg