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
Table "public.asins_statistics"
Column | Type | Modifiers
-----------------------+-----------------------------+---------------------------------------------------------------
id | integer | not null default nextval('asins_statistics_id_seq'::regclass)
average_cost_amazon | double precision |
average_price_new | double precision |
quantity_sold_new | double precision |
quantity_in_transit | double precision |
quantity_present_new | double precision |
ranks_thirty | integer |
ranks_ninety | integer |
average_profit_new | double precision |
average_roi_new | double precision |
average_selling_time | double precision |
asin_id | integer |
average_cost_aob | double precision |
last_sold | timestamp without time zone |
average_price_used | double precision |
quantity_sold_used | integer |
quantity_present_used | integer |
average_profit_used | double precision |
Indexes:
"asins_statistics_pkey" PRIMARY KEY, btree (id)
Foreign-key constraints:
"asins_statistics_asin_id_fkey" FOREIGN KEY (asin_id) REFERENCES asins(id) ON DELETE CASCADE
Table "public.books"
Column | Type | Modifiers
--------------------------------------------+-----------------------------+----------------------------------------------------
id | integer | not null default nextval('books_id_seq'::regclass)
link | character varying(300) |
asin | character varying(60) |
title | character varying(400) |
isbn | character varying(50) |
newer_edition_available | boolean |
newer_edition_link | character varying(150) |
cover_type | character varying(100) |
block_until | timestamp without time zone |
latest_trade_in_available | boolean |
latest_trade_in_price | double precision |
latest_rank | bigint |
latest_profit_like_new | double precision |
latest_profit_very_good | double precision |
latest_profit_ratio | double precision |
latest_profit_trade_in | double precision |
category_id | integer |
aob_username | character varying(250) |
latest_minimum_price | double precision |
latest_minimum_shipping | double precision |
bsr | integer | default 1000
quantity_in_transit | integer | default 0
latest_minimum_price_like_new | double precision | default '1000000'::double precision
latest_minimum_price_very_good | double precision | default '1000000'::double precision
latest_minimum_shipping_like_new | double precision | default '1000000'::double precision
latest_minimum_shipping_very_good | double precision | default '1000000'::double precision
total_minimum_price_and_shipping | double precision |
recent_minimum_price_and_shipping | double precision |
seventy_five_percentile_price_and_shipping | double precision |
bsr_str | character varying(50) |
bsr_id | integer |
latest_profit_ratio_like_new | double precision |
latest_profit_ratio_very_good | double precision |
Indexes:
"books_pkey" PRIMARY KEY, btree (id)
"books_asin_key" UNIQUE CONSTRAINT, btree (asin)
"books_isbn_key" UNIQUE CONSTRAINT, btree (isbn)
"books_link_key" UNIQUE CONSTRAINT, btree (link)
"index_asin_books" btree (asin)
"index_isbn_books" btree (isbn)
"index_latest_rank_books" btree (latest_rank)
"index_title_books" btree (title)
Table "public.asins"
Column | Type | Modifiers
------------------+-----------------------+----------------------------------------------------
id | integer | not null default nextval('isbns_id_seq'::regclass)
value | character varying(50) |
rank_type | popularitytypeenum |
sell_constraints | character varying(50) |
isbn_thirteen | character varying(20) |
Indexes:
"isbns_pkey" PRIMARY KEY, btree (id)
"isbns_value_key" UNIQUE CONSTRAINT, btree (value)
"index_value_asins" btree (value)
Referenced by:
TABLE "asins_statistics" CONSTRAINT "asins_statistics_asin_id_fkey" FOREIGN KEY (asin_id) REFERENCES asins(id) ON DELETE CASCADE
TABLE "books_to_replenish" CONSTRAINT "books_to_replenish_asin_id_fkey" FOREIGN KEY (asin_id) REFERENCES asins(id) ON DELETE CASCADE
TABLE "inventory_item" CONSTRAINT "inventory_item_asin_id_fkey" FOREIGN KEY (asin_id) REFERENCES asins(id) ON DELETE CASCADE
TABLE "inventory_items" CONSTRAINT "inventory_items_asin_id_fkey" FOREIGN KEY (asin_id) REFERENCES asins(id) ON DELETE CASCADE
TABLE "orders_on_amazon" CONSTRAINT "orders_on_amazon_asin_id_fkey" FOREIGN KEY (asin_id) REFERENCES asins(id) ON DELETE CASCADE
TABLE "orders_on_amazon_sold" CONSTRAINT "orders_on_amazon_sold_asin_id_fkey" FOREIGN KEY (asin_id) REFERENCES asins(id) ON DELETE CASCADE
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=1048379.37..1048428.33 rows=100 width=498) (actual time=5264.193..5264.444 rows=100 loops=1)
Buffers: shared hit=40250 read=332472, temp read=16699 written=28392
-> Merge Join (cost=1048379.37..2291557.51 rows=2539360 width=498) (actual time=5264.191..5264.436 rows=100 loops=1)
Merge Cond: ((books.isbn)::text = (isbns.value)::text)
Buffers: shared hit=40250 read=332472, temp read=16699 written=28392
-> Index Scan using books_isbn_key on books (cost=0.43..1205494.88 rows=1386114 width=333) (actual time=0.020..0.150 rows=100 loops=1)
Buffers: shared hit=103
-> Materialize (cost=1042333.77..1055199.75 rows=2573197 width=155) (actual time=5263.901..5263.960 rows=100 loops=1)
Buffers: shared hit=40147 read=332472, temp read=16699 written=28392
-> Sort (cost=1042333.77..1048766.76 rows=2573197 width=155) (actual time=5263.895..5263.949 rows=100 loops=1)
Sort Key: isbns.value
Sort Method: external merge Disk: 136864kB
Buffers: shared hit=40147 read=332472, temp read=16699 written=28392
-> Hash Join (cost=55734.14..566061.44 rows=2573197 width=155) (actual time=403.962..1994.884 rows=1404582 loops=1)
Hash Cond: (isbns_statistics.isbn_id = isbns.id)
Buffers: shared hit=40147 read=332472, temp read=11281 written=11279
-> Seq Scan on isbns_statistics (cost=0.00..385193.97 rows=2573197 width=120) (actual time=0.024..779.717 rows=1404582 loops=1)
Buffers: shared hit=26990 read=332472
-> Hash (cost=27202.84..27202.84 rows=1404584 width=35) (actual time=402.431..402.431 rows=1404584 loops=1)
Buckets: 1048576 Batches: 2 Memory Usage: 51393kB
Buffers: shared hit=13157, temp written=4363
-> Seq Scan on isbns (cost=0.00..27202.84 rows=1404584 width=35) (actual time=0.027..152.568 rows=1404584 loops=1)
Buffers: shared hit=13157
Planning time: 1.160 ms
Execution time: 5279.983 ms
(25 rows)
