Re: 15x slower PreparedStatement vs raw query

I am not an expert on this, But I would like to take a shot :)

Is it possible to share your prepared statement and parameter types.
I mean

something like this

PREPARE usrrptplan (int) AS
SELECT * FROM users u, logs l WHERE u.usrid=$1 AND u.usrid=l.usrid
AND l.date = $2;
EXECUTE usrrptplan(1, current_date);

It's just that sometimes the datatypes of the prepared statement params are
not the same as the datatype of the field in the join and as a result it
may add some overhead.
PostgreSQL - general - bpchar, text and indexes (postgresql-archive.org)
<https://www.postgresql-archive.org/bpchar-text-and-indexes-td5888846.html>
There was one more thread where a person has similar issues, which was
sorted by using the relevant field type in the prepared field.

bPMA | explain.depesz.com <https://explain.depesz.com/s/bPMA> -> slow
(prepared) Row 8
TsNn | explain.depesz.com <https://explain.depesz.com/s/TsNn> -> fast
(direct) Row 8
It seems the join filters in the prepared version are doing a lot of work
on the fields massaging the fields that may add the cost overhead,

Also, if the above does not work, can you try the below plan GUC to check
if you see any improvements.

Tech preview: How PostgreSQL 12 handles prepared plans - CYBERTEC
(cybertec-postgresql.com)
<https://www.cybertec-postgresql.com/en/tech-preview-how-postgresql-12-handles-prepared-plans/>

Thanks,
Vijay

On Tue, 4 May 2021 at 20:52, Laurenz Albe <laurenz(dot)albe(at)cybertec(dot)at> wrote:

> On Tue, 2021-05-04 at 13:59 +0000, Alex wrote:
> > "Powerful general purpose relational database" but not smart...
>
> Too smart can easily become slow...
>
> > I propose a feature to use information from previously executed queries
> to adjust the query plan time accordingly.
> > Reusing the same generic plan may and will lead to very long execution
> times.
>
> AI can go wrong too, and I personally would be worried that such cases
> are very hard to debug...
>
> Yours,
> Laurenz Albe
> --
> Cybertec | https://www.cybertec-postgresql.com
>
>
>
>

--
Thanks,
Vijay
Mumbai, India