Opening a view takes forever, and maybe times out after 300s

[jimis]

When I open a VIEW that basically joins a huge (2G rows) table with other smaller tables, it feels like it's loading forever, until 5 minutes pass and the UI informs me that the query timed out.

Opening the huge table itself is instantaneous.

Monitoring the database, I see that it is doing I/O for the query SELECT COUNT(1) FROM .... I believe this is for pagination.

[jimis]

A solution would be to have a switch to disable page count.

For huge databases counting the pages costs a lot, and we don't really care about how many pages are left. As long as I can go to next page or page number N, I'm covered.

[sosedoff]

Is this generally not the case with other database browser products?

In theory, it should be okay to implement pagination based on the PK of the table and skip doing COUNT(1) and OFFSET queries, rely on the row estimation first. But this will only apply to table data searches that do not contain any filtering.

I don't have a specific plan in mind on how to accommodate both use cases right now. If you have any thoughts, please share them in here, otherwise i dont really want to spend any time on this.

[jimis]

Is this generally not the case with other database browser products?

I don't know. But especially for views it sounds risky to do count().

I don't have a specific plan in mind on how to accommodate both use cases right now. If you have any thoughts, please share them in here, otherwise i dont really want to spend any time on this.

A generic switch to disable the count() query only for views, or even for everything. As a start, via command-line: --disable-views-page-count and --disable-all-page-count.

[jimis]

Another alternative would be to leave the count() query running in the background.

• Display the first page of the table
• Submit the count() query and display "page 1 of ?" where on-hover displays "calculating total number of rows..."
• Update the UI when the count() query completes.

[jimis]

Can you re-open? Opening the VIEW still times out after minutes of unresponsiveness, and on the postgres log I see:

2023-08-25 20:12:10.961 UTC [716922] user@db ERROR:  canceling statement due to user request
2023-08-25 20:12:10.961 UTC [716922] user@db STATEMENT:  SELECT COUNT(1) FROM "public"."big_table"
2023-08-25 20:12:10.979 UTC [752467] FATAL:  terminating background worker "parallel worker" due to administrator command
2023-08-25 20:12:10.979 UTC [752467] STATEMENT:  SELECT COUNT(1) FROM "public"."big_table"
[ ... similar errors ]