Re: BUG #16183: PREPARED STATEMENT slowed down by jit - Mailing list pgsql-bugs

On Sun, Jan 5, 2020 at 7:21 PM Ryan Lambert <ryan@rustprooflabs.com> wrote:

I tried applying your original patch and the later patch from that thread to REL_12_STABLE, unfortunately no luck on either.  The original patch errors with:

$ git apply -p1 < array_type_analyze_MCE_V001.patch

error: src/backend/utils/adt/array_typanalyze.c: already exists in working directory

I just use the GNU "patch -p1" command.  I don't  know what "git apply" is up to here, and the error message isn't very informative.

By the way, the patch says "However, there is no histogram to fall back on when the MCE array is NULL", which is a bit of nonsense.  It is ndistinct, not histogram, which the scalar case falls back on when MCV is NULL.  But there isn't an ndistinct for MCE either, so the conclusion is still sound.

Cheers,

Jeff

Thank you, using patch instead of git apply works.  Your patch [1] applies cleanly and passes make installcheck-world against REL_12_STABLE.  It greatly improves the estimated row counts in the prepared statements [2] that seem to be causing the issue.  I have not yet verified this patch fixes the originally reported performance issue regarding updating data with osm2pgsql [3].  

The following query uses an ID from Colorado region's ways table. A gzipped plain pg_dump (97MB) is available [4] to make this easier to replicate if helpful.

PREPARE mark_ways_by_node(BIGINT) AS
SELECT id FROM public.planet_osm_ways
WHERE nodes && ARRAY[$1];
EXPLAIN(ANALYZE)
EXECUTE mark_ways_by_node(736973985);

The explain results from Unpatched Postgres 12.1 shows estimated 10,222 rows when actual rows=1.


                                                             QUERY PLAN

--------------------------------------------------------------------------------------------------------------
----------------------
 Bitmap Heap Scan on planet_osm_ways  (cost=119.22..28648.09 rows=10222 width=8) (actual time=0.045..0.047 rows=1 loops=1)
   Recheck Cond: (nodes && '{736973985}'::bigint[])
   Heap Blocks: exact=1
   ->  Bitmap Index Scan on planet_osm_ways_nodes  (cost=0.00..116.67 rows=10222 width=0) (actual time=0.032..
0.033 rows=1 loops=1)
         Index Cond: (nodes && '{736973985}'::bigint[])
 Planning Time: 0.288 ms
 Execution Time: 0.102 ms


The same data with the patch [1] applied is much closer with an estimated 68 rows. The OP's query plan showed an estimated row count > 300k when actual rows was 2.   It's logical to suspect the costly JIT / parallel query would not be chosen if their row count estimates improve by similar margins.


                                                           QUERY PLAN

----------------------------------------------------------------------------------------------------------------------------
----
 Bitmap Heap Scan on planet_osm_ways  (cost=28.53..295.41 rows=68 width=8) (actual time=0.045..0.046 rows=1 loops=1)
   Recheck Cond: (nodes && '{736973985}'::bigint[])
   Heap Blocks: exact=1
   ->  Bitmap Index Scan on planet_osm_ways_nodes  (cost=0.00..28.51 rows=68 width=0) (actual time=0.034..0.035 rows=1 loops
=1)
         Index Cond: (nodes && '{736973985}'::bigint[])
 Planning Time: 0.209 ms
 Execution Time: 0.092 ms


I'll try to do more testing in a few days to verify if this patch fixes the issue with osm2pgsql updates. 

Thanks,
Ryan

[1] https://www.postgresql.org/message-id/attachment/39315/array_type_analyze_MCE_V001.patch
[2] https://github.com/openstreetmap/osm2pgsql/blob/master/src/middle-pgsql.cpp#L786
[3] https://github.com/openstreetmap/osm2pgsql/issues/1045  

[4] https://drive.google.com/file/d/1hDpiTiAazG_g1ObMBTRpe-4B_r8gidch/view?usp=sharing