Rows Per Page suggestion #16

Closed
corgus opened this Issue Jun 22, 2015 · 8 comments

Comments

Projects
None yet
4 participants
@corgus

corgus commented Jun 22, 2015

The table preview (viewing paginated records of a table after clicking it) is an excellent idea, but is taking a long time for a large database (10M rows in my case).

Suggestion: use LIMIT in the query, and the Preferences 'Rows per page' as the value.
(to return SELECT * FROM db LIMIT 20 in this case)

screen shot 2015-06-22 at 4 15 36 pm

Could reduce execution time 1000x (17.8s - 22ms in this example):

screen shot 2015-06-22 at 4 17 24 pm

related: #7

@jakob

This comment has been minimized.

Show comment
Hide comment
@jakob

jakob Jun 23, 2015

Owner

I'm not sure why loading the records in the table view is so slow. It actually uses a LIMIT clause. However, it always orders the results (either by primary key, or by ctid if no key is available). This is necessary because otherwise going to the next page might show the same records again!

To determine why the query is slow, you can open a second connection to the server (open a new window, or use psql) and execute the following query:

SELECT * FROM pg_stat_activity

This will show you the exact query Postico uses to load the table. Then you can use the EXPLAIN ANYLYZE command to determine why it is so slow, and we could check if there are ways to improve query speed.

However, I'm not sure if there's a general way I can make the table view faster.

Owner

jakob commented Jun 23, 2015

I'm not sure why loading the records in the table view is so slow. It actually uses a LIMIT clause. However, it always orders the results (either by primary key, or by ctid if no key is available). This is necessary because otherwise going to the next page might show the same records again!

To determine why the query is slow, you can open a second connection to the server (open a new window, or use psql) and execute the following query:

SELECT * FROM pg_stat_activity

This will show you the exact query Postico uses to load the table. Then you can use the EXPLAIN ANYLYZE command to determine why it is so slow, and we could check if there are ways to improve query speed.

However, I'm not sure if there's a general way I can make the table view faster.

@qwesda

This comment has been minimized.

Show comment
Hide comment
@qwesda

qwesda Jun 23, 2015

Can you share your db/table? I have some free time to do some analysing ...

qwesda commented Jun 23, 2015

Can you share your db/table? I have some free time to do some analysing ...

@corgus

This comment has been minimized.

Show comment
Hide comment
@corgus

corgus Jun 23, 2015

Ah, yea it seems ordering would be the culprit, which would be necessary for pagination, hmmm...

Personally, I would use the 'table view' to get a preview of the tables (to get relations and some sample data, limited to 100 random samples for example), and use this to build queries in the SQL Query module, sorting there as necessary. One solution might be to default as 'preview' (disable pagination) for large tables and allow pagination for smaller?

@qwesda - This table is from geonames, downloadable here as 'allCountries.zip': http://download.geonames.org/export/dump/
After unzipping, this little tutorial has a pretty good explanation on getting the txt to db table:
http://jamescreel.net/blog/utilizing-the-geonames-dataset-in-postgres/

corgus commented Jun 23, 2015

Ah, yea it seems ordering would be the culprit, which would be necessary for pagination, hmmm...

Personally, I would use the 'table view' to get a preview of the tables (to get relations and some sample data, limited to 100 random samples for example), and use this to build queries in the SQL Query module, sorting there as necessary. One solution might be to default as 'preview' (disable pagination) for large tables and allow pagination for smaller?

@qwesda - This table is from geonames, downloadable here as 'allCountries.zip': http://download.geonames.org/export/dump/
After unzipping, this little tutorial has a pretty good explanation on getting the txt to db table:
http://jamescreel.net/blog/utilizing-the-geonames-dataset-in-postgres/

@jakob

This comment has been minimized.

Show comment
Hide comment
@jakob

jakob Jun 24, 2015

Owner

It's still odd that it is so slow. Ordering by primary key (or ctid) should be fast in general. I'll see if I find a way around this.

Owner

jakob commented Jun 24, 2015

It's still odd that it is so slow. Ordering by primary key (or ctid) should be fast in general. I'll see if I find a way around this.

@jakob

This comment has been minimized.

Show comment
Hide comment
@jakob

jakob Aug 6, 2015

Owner

@corgus I've added a new preference to Postico: "Always sort tables".

postico preferences

If you disable this checkbox, Postico will no longer sort rows by default (unless you click the table header). You can still click through pages, but PostgreSQL might decide to show some records it already showed on the previous page as the order might change from query to query.

Could you try if this improves performance for your database? The new setting is available in this build: Postico 2015-08-04

Owner

jakob commented Aug 6, 2015

@corgus I've added a new preference to Postico: "Always sort tables".

postico preferences

If you disable this checkbox, Postico will no longer sort rows by default (unless you click the table header). You can still click through pages, but PostgreSQL might decide to show some records it already showed on the previous page as the order might change from query to query.

Could you try if this improves performance for your database? The new setting is available in this build: Postico 2015-08-04

@jakob

This comment has been minimized.

Show comment
Hide comment
@jakob

jakob Aug 14, 2015

Owner

The change is now available in version 0.26

Owner

jakob commented Aug 14, 2015

The change is now available in version 0.26

@jakob jakob closed this Aug 14, 2015

@corgus

This comment has been minimized.

Show comment
Hide comment
@corgus

corgus Aug 14, 2015

Confirmed that this eliminates the delay for my db - thx @jakob !

corgus commented Aug 14, 2015

Confirmed that this eliminates the delay for my db - thx @jakob !

@aamine

This comment has been minimized.

Show comment
Hide comment
@aamine

aamine Dec 8, 2015

Disabling sort feature is great enhancement for especially Redshift !!! Redshift have primary index but does not make index for it, sorting by primary key causes full table sort. Sorting 20G rows is a kind of nightmare... Thanks a lot!

aamine commented Dec 8, 2015

Disabling sort feature is great enhancement for especially Redshift !!! Redshift have primary index but does not make index for it, sorting by primary key causes full table sort. Sorting 20G rows is a kind of nightmare... Thanks a lot!

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment