From: Justin Pryzby <pryzby@telsasoft.com>
To: Guillaume Cottenceau <gc@mnc.ch>
Cc: pgsql-performance@postgresql.org
Subject: Re: much slower query in production
Date: Wed, 26 Feb 2020 10:28:09 -0600
Message-ID: <20200226162809.GZ31889@telsasoft.com> (raw)
In-Reply-To: <878skpnx0u.fsf@mnc.ch>
References: <878skpnx0u.fsf@mnc.ch>
On Wed, Feb 26, 2020 at 05:17:21PM +0100, Guillaume Cottenceau wrote:
> On production:
>
> # EXPLAIN ANALYZE SELECT transaction_uid, (SELECT COUNT(*) FROM tickets WHERE multicard_uid = multicards.uid) from multicards;
> QUERY PLAN
> ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
> Seq Scan on multicards (cost=0.00..1455177.30 rows=204548 width=12) (actual time=0.178..1694987.355 rows=204548 loops=1)
> SubPlan 1
> -> Aggregate (cost=7.07..7.08 rows=1 width=8) (actual time=8.283..8.283 rows=1 loops=204548)
> -> Index Only Scan using tickets_multicard_uid on tickets (cost=0.43..7.05 rows=9 width=0) (actual time=1.350..8.280 rows=6 loops=204548)
> Index Cond: (multicard_uid = multicards.uid)
> Heap Fetches: 1174940
> Planning Time: 1.220 ms
> Execution Time: 1695029.673 ms
> The execution time ratio is a huge 3700. I guess the Heap Fetches
> difference is the most meaningful here;
Yes, it's doing an "index only" scan, but not very effectively.
Vacuum the tickets table to set relallvisible and see if that helps.
If so, try to keep it better vacuumed with something like
ALTER TABLE tickets SET (AUTOVACUUM_VACUUM_SCALE_FACTOR=0.005);
--
Justin
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: pryzby@telsasoft.com, gc@mnc.ch
Subject: Re: much slower query in production
In-Reply-To: <20200226162809.GZ31889@telsasoft.com>
* 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