pg.ddx.io pgsql-performance@postgresql.org mailing list archive help / color / mirror / Atom feed From: Rondat Flyag <rondatflyag@yandex.ru>
To: Jeff Janes <jeff.janes@gmail.com>
Cc: pgsql-performance@lists.postgresql.org <pgsql-performance@lists.postgresql.org>
Subject: Re: Queries containing ORDER BY and LIMIT started to work slowly
Date: Wed, 30 Aug 2023 20:31:05 +0300
Message-ID: <203321693416472@mail.yandex.ru> (raw )
In-Reply-To: <CAMkU=1yX2TtLw+Fcb=ZSYWmGvY-Mx1LOAU67VTryZLMcor6gRA@mail.gmail.com >
References: <32431693330715@mail.yandex.ru >
<CAMkU=1w+RgLQVkqLudpfsax5HQgY94WFmSH+RfydmsN55a_QGQ@mail.gmail.com >
<2854111693335271@mail.yandex.ru >
<CAMkU=1yX2TtLw+Fcb=ZSYWmGvY-Mx1LOAU67VTryZLMcor6gRA@mail.gmail.com >
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=766983.18..767070.52 rows=100 width=498) (actual time=5508.261..5508.532 rows=100 loops=1)
Buffers: shared hit=30249 read=342473, temp read=16856 written=28392
-> Merge Join (cost=766983.18..2008284.16 rows=1421289 width=498) (actual time=5508.260..5508.527 rows=100 loops=1)
Merge Cond: ((books.asin)::text = (asins.value)::text)
Buffers: shared hit=30249 read=342473, temp read=16856 written=28392
-> Index Scan using books_asin_key on books (cost=0.43..1216522.35 rows=1403453 width=333) (actual time=0.007..0.150 rows=100 loops=1)
Buffers: shared hit=103
-> Materialize (cost=766980.48..774092.68 rows=1422439 width=155) (actual time=5508.248..5508.304 rows=100 loops=1)
Buffers: shared hit=30146 read=342473, temp read=16856 written=28392
-> Sort (cost=766980.48..770536.58 rows=1422439 width=155) (actual time=5508.245..5508.293 rows=100 loops=1)
Sort Key: asins.value
Sort Method: external merge Disk: 136864kB
Buffers: shared hit=30146 read=342473, temp read=16856 written=28392
-> Hash Join (cost=55734.25..509782.68 rows=1422439 width=155) (actual time=412.394..2071.400 rows=1404582 loops=1)
Hash Cond: (asins_statistics.asin_id = asins.id)
Buffers: shared hit=30146 read=342473, temp read=11281 written=11279
-> Seq Scan on asins_statistics (cost=0.00..373686.39 rows=1422439 width=120) (actual time=0.005..782.893 rows=1404582 loops=1)
Buffers: shared hit=16989 read=342473
-> Hash (cost=27202.89..27202.89 rows=1404589 width=35) (actual time=412.025..412.026 rows=1404589 loops=1)
Buckets: 1048576 Batches: 2 Memory Usage: 51393kB
Buffers: shared hit=13157, temp written=4363
-> Seq Scan on asins (cost=0.00..27202.89 rows=1404589 width=35) (actual time=0.010..151.754 rows=1404589 loops=1)
Buffers: shared hit=13157
Planning time: 0.770 ms
Execution time: 5525.959 ms
(25 rows)
SET enable_seqscan = OFF;
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=10001314403.06..10001314490.39 rows=100 width=498) (actual time=6171.243..6171.489 rows=100 loops=1)
Buffers: shared hit=1030733 read=346050, temp read=22327 written=34044
-> Merge Join (cost=10001314403.06..10002555704.04 rows=1421289 width=498) (actual time=6171.241..6171.478 rows=100 loops=1)
Merge Cond: ((books.asin)::text = (asins.value)::text)
Buffers: shared hit=1030733 read=346050, temp read=22327 written=34044
-> Index Scan using books_asin_key on books (cost=0.43..1216522.35 rows=1403453 width=333) (actual time=0.019..0.144 rows=100 loops=1)
Buffers: shared hit=103
-> Materialize (cost=10001314400.36..10001321512.55 rows=1422439 width=155) (actual time=6171.213..6171.265 rows=100 loops=1)
Buffers: shared hit=1030630 read=346050, temp read=22327 written=34044
-> Sort (cost=10001314400.36..10001317956.46 rows=1422439 width=155) (actual time=6171.133..6171.178 rows=100 loops=1)
Sort Key: asins.value
Sort Method: external merge Disk: 136864kB
Buffers: shared hit=1030630 read=346050, temp read=22327 written=34044
-> Hash Join (cost=10000416471.30..10001057202.55 rows=1422439 width=155) (actual time=1074.832..2719.409 rows=1404582 loops=1)
Hash Cond: (asins.id = asins_statistics.asin_id)
Buffers: shared hit=1030630 read=346050, temp read=16937 written=16931
-> Index Scan using isbns_pkey on asins (cost=0.43..574466.58 rows=1404589 width=35) (actual time=0.012..688.506 rows=1404589 loops=1)
Buffers: shared hit=1015561 read=1657
-> Hash (cost=10000373686.39..10000373686.39 rows=1422439 width=120) (actual time=1065.611..1065.611 rows=1404582 loops=1)
Buckets: 524288 Batches: 4 Memory Usage: 35931kB
Buffers: shared hit=15069 read=344393, temp written=10377
-> Seq Scan on asins_statistics (cost=10000000000.00..10000373686.39 rows=1422439 width=120) (actual time=0.025..795.542 rows=1404582 loops=1)
Buffers: shared hit=15069 read=344393
Planning time: 1.128 ms
Execution time: 6189.046 ms
(25 rows)
SET enable_sort = OFF;
Query takes a lot of time.
Attachments:
[text/plain] query_plans.txt (5.3K, ../203321693416472@mail.yandex.ru/2-query_plans.txt)
download | inline:
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=766983.18..767070.52 rows=100 width=498) (actual time=5508.261..5508.532 rows=100 loops=1)
Buffers: shared hit=30249 read=342473, temp read=16856 written=28392
-> Merge Join (cost=766983.18..2008284.16 rows=1421289 width=498) (actual time=5508.260..5508.527 rows=100 loops=1)
Merge Cond: ((books.asin)::text = (asins.value)::text)
Buffers: shared hit=30249 read=342473, temp read=16856 written=28392
-> Index Scan using books_asin_key on books (cost=0.43..1216522.35 rows=1403453 width=333) (actual time=0.007..0.150 rows=100 loops=1)
Buffers: shared hit=103
-> Materialize (cost=766980.48..774092.68 rows=1422439 width=155) (actual time=5508.248..5508.304 rows=100 loops=1)
Buffers: shared hit=30146 read=342473, temp read=16856 written=28392
-> Sort (cost=766980.48..770536.58 rows=1422439 width=155) (actual time=5508.245..5508.293 rows=100 loops=1)
Sort Key: asins.value
Sort Method: external merge Disk: 136864kB
Buffers: shared hit=30146 read=342473, temp read=16856 written=28392
-> Hash Join (cost=55734.25..509782.68 rows=1422439 width=155) (actual time=412.394..2071.400 rows=1404582 loops=1)
Hash Cond: (asins_statistics.asin_id = asins.id)
Buffers: shared hit=30146 read=342473, temp read=11281 written=11279
-> Seq Scan on asins_statistics (cost=0.00..373686.39 rows=1422439 width=120) (actual time=0.005..782.893 rows=1404582 loops=1)
Buffers: shared hit=16989 read=342473
-> Hash (cost=27202.89..27202.89 rows=1404589 width=35) (actual time=412.025..412.026 rows=1404589 loops=1)
Buckets: 1048576 Batches: 2 Memory Usage: 51393kB
Buffers: shared hit=13157, temp written=4363
-> Seq Scan on asins (cost=0.00..27202.89 rows=1404589 width=35) (actual time=0.010..151.754 rows=1404589 loops=1)
Buffers: shared hit=13157
Planning time: 0.770 ms
Execution time: 5525.959 ms
(25 rows)
SET enable_seqscan = OFF;
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=10001314403.06..10001314490.39 rows=100 width=498) (actual time=6171.243..6171.489 rows=100 loops=1)
Buffers: shared hit=1030733 read=346050, temp read=22327 written=34044
-> Merge Join (cost=10001314403.06..10002555704.04 rows=1421289 width=498) (actual time=6171.241..6171.478 rows=100 loops=1)
Merge Cond: ((books.asin)::text = (asins.value)::text)
Buffers: shared hit=1030733 read=346050, temp read=22327 written=34044
-> Index Scan using books_asin_key on books (cost=0.43..1216522.35 rows=1403453 width=333) (actual time=0.019..0.144 rows=100 loops=1)
Buffers: shared hit=103
-> Materialize (cost=10001314400.36..10001321512.55 rows=1422439 width=155) (actual time=6171.213..6171.265 rows=100 loops=1)
Buffers: shared hit=1030630 read=346050, temp read=22327 written=34044
-> Sort (cost=10001314400.36..10001317956.46 rows=1422439 width=155) (actual time=6171.133..6171.178 rows=100 loops=1)
Sort Key: asins.value
Sort Method: external merge Disk: 136864kB
Buffers: shared hit=1030630 read=346050, temp read=22327 written=34044
-> Hash Join (cost=10000416471.30..10001057202.55 rows=1422439 width=155) (actual time=1074.832..2719.409 rows=1404582 loops=1)
Hash Cond: (asins.id = asins_statistics.asin_id)
Buffers: shared hit=1030630 read=346050, temp read=16937 written=16931
-> Index Scan using isbns_pkey on asins (cost=0.43..574466.58 rows=1404589 width=35) (actual time=0.012..688.506 rows=1404589 loops=1)
Buffers: shared hit=1015561 read=1657
-> Hash (cost=10000373686.39..10000373686.39 rows=1422439 width=120) (actual time=1065.611..1065.611 rows=1404582 loops=1)
Buckets: 524288 Batches: 4 Memory Usage: 35931kB
Buffers: shared hit=15069 read=344393, temp written=10377
-> Seq Scan on asins_statistics (cost=10000000000.00..10000373686.39 rows=1422439 width=120) (actual time=0.025..795.542 rows=1404582 loops=1)
Buffers: shared hit=15069 read=344393
Planning time: 1.128 ms
Execution time: 6189.046 ms
(25 rows)
SET enable_sort = OFF;
Query takes a lot of time.
view thread (11+ messages) latest in thread
Message-ID: <203321693416472@mail.yandex.ru>
Permalink: ../203321693416472@mail.yandex.ru/
Also on: postgresql.org/message-id/203321693416472@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, jeff.janes@gmail.com, pgsql-performance@lists.postgresql.org
Subject: Re: Queries containing ORDER BY and LIMIT started to work slowly
In-Reply-To: <203321693416472@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