I have a legacy system that uses `Posgresql 9.6` and `Ubuntu 16.04`. Everything was fine several days ago even with standard Postgresql settings. I dumped a database with the compression option (maximum compression level -Z 9) in order to have a smaller size (`pg_dump --compress=9 database_name > database_name.sql`). After that I got a lot of problems. Some queries for certain tables started to be executed very slow. Queries for other tables work fine.

 

Here are the tables that I have issues with.

 

 

    asins:

    id (integer)

    value (string), index b-tree

    type (string)

 

  

 

    books:

    id (integer)

    asin (string), index b-tree

    ...

    (total 32 columns)

 

 

 

    asins_statistics:

    id (integer)

    average_price (float)

    average_rating (integer)

    asin_id (foreign key)

    ...

    (total 17 columns)

 

These tables contain 1 400 000 rows each. Detailed info in attachments.

 

Basically I used the following query and it worked well:

 

 

    (1) SELECT * FROM ISBNS JOIN BOOKS ON BOOKS.ISBN = ISBN.VALUE JOIN ISBNS_STATISTICS ON ISBNS_STATISTICS.ISBN_ID = ISBNS.ID ORDER BY ISBNS.VALUE LIMIT 100;

 

 

But after I made the dump it started to be executed extremely slow. I'm not sure whether it's because of the dump, but before the dump everything worked well. This query also works well:

 

 

    SELECT * FROM ISBNS JOIN BOOKS ON BOOKS.ISBN = ISBN.VALUE JOIN ISBNS_STATISTICS ON ISBNS_STATISTICS.ISBN_ID = ISBNS.ID LIMIT 100;

 

This query is executed quickly too:

 

    SELECT * FROM ISBNS JOIN BOOKS ON BOOKS.ISBN = ISBN.VALUE ORDER BY ISBNS.VALUE LIMIT 100;

 

 

I changed performance settings (for instance, increased `shared_buffer`), but it didn't increase speed too much.

 

I've read that queries containing LIMIT and ORDER BY work very slow, but if I make such queries to other tables it works fine.

 

The query plan for query (1) is in attachment.

 

So, the questions are:

1. Why everything worked well and started to work slowly?

2. Why similar queries to other tables are still executed quickly?

 

Thank you in advance.

 

Cheers,

Serg