Date: Wed, 26 Feb 2020 10:28:09 -0600
From: Justin Pryzby <pryzby@telsasoft.com>
To: Guillaume Cottenceau <gc@mnc.ch>
Cc: pgsql-performance@postgresql.org
Subject: Re: much slower query in production
Message-ID: <20200226162809.GZ31889@telsasoft.com>
References: <878skpnx0u.fsf@mnc.ch>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: <878skpnx0u.fsf@mnc.ch>
User-Agent: Mutt/1.5.24 (2015-08-30)
Precedence: bulk

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