MIME-Version: 1.0
References: 
 <CADHFRcjzJoRiPoJ_5Rf9ynetBPt0R5c4L_N_k-uja=W1+izvVg@mail.gmail.com>
In-Reply-To: 
 <CADHFRcjzJoRiPoJ_5Rf9ynetBPt0R5c4L_N_k-uja=W1+izvVg@mail.gmail.com>
From: David Rowley <dgrowleyml@gmail.com>
Date: Tue, 14 May 2024 23:16:05 +1200
Message-ID: 
 <CAApHDvriMd3RpH9khEV_p+tgqOexRvS7HT1isfdv1OjMA0wSBw@mail.gmail.com>
Subject: Re: Small table selection extremely slow!
To: Bo Guo <bo.guo@gisticinc.com>
Cc: pgsql-sql@lists.postgresql.org
Content-Type: text/plain; charset="UTF-8"
Archived-At: 
 <https://www.postgresql.org/message-id/CAApHDvriMd3RpH9khEV_p%2BtgqOexRvS7HT1isfdv1OjMA0wSBw%40mail.gmail.com>
Precedence: bulk

On Tue, 14 May 2024 at 23:12, Bo Guo <bo.guo@gisticinc.com> wrote:
> The following query took 20 seconds on a small table of 108 rows with a dozen columns:
>
> SELECT * FROM azgiv.layers;
>
> EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM azgiv.layers;
> Seq Scan on layers  (cost=0.00..14.00 rows=200 width=233) (actual time=0.010..0.087 rows=200 loops=1)
>   Buffers: shared hit=12
> Planning:
>   Buffers: shared hit=51
>  Planning Time: 0.233 ms
>  Execution Time: 0.121 ms

One difference between the SELECT and EXPLAIN ANALYZE is that SELECT
will detoast your toasted datums whereas EXPLAIN ANALYZE will not.

It's likely the slowness comes from the detoasting.

David