pg.ddx.io pgsql-performance@postgresql.org mailing list archive help / color / mirror / Atom feed From: Rondat Flyag <rondatflyag@yandex.ru>
To: pgsql-performance@lists.postgresql.org <pgsql-performance@lists.postgresql.org>
Subject: Queries containing ORDER BY and LIMIT started to work slowly
Date: Tue, 29 Aug 2023 20:47:19 +0300
Message-ID: <32431693330715@mail.yandex.ru> (raw )
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)
Attachments:
[text/plain] asins_statistics_schema.txt (1.6K, ../32431693330715@mail.yandex.ru/2-asins_statistics_schema.txt)
download | inline:
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
[text/plain] books_schema.txt (3.4K, ../32431693330715@mail.yandex.ru/3-books_schema.txt)
download | inline:
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)
[text/plain] asins_schema.txt (1.5K, ../32431693330715@mail.yandex.ru/4-asins_schema.txt)
download | inline:
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
[text/plain] query_plan.txt (2.7K, ../32431693330715@mail.yandex.ru/5-query_plan.txt)
download | inline:
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)
view thread (11+ messages) latest in thread
Message-ID: <32431693330715@mail.yandex.ru>
Permalink: ../32431693330715@mail.yandex.ru/
Also on: postgresql.org/message-id/32431693330715@mail.yandex.ru
copy link · copy postgr.es
reply Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: rondatflyag@yandex.ru, pgsql-performance@lists.postgresql.org
Subject: Re: Queries containing ORDER BY and LIMIT started to work slowly
In-Reply-To: <32431693330715@mail.yandex.ru>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox