Re: BUG #16183: PREPARED STATEMENT slowed down by jit

On Mon, Jan 6, 2020 at 10:51 AM Jeff Janes <jeff(dot)janes(at)gmail(dot)com> wrote:

> On Sun, Jan 5, 2020 at 7:21 PM Ryan Lambert <ryan(at)rustprooflabs(dot)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