Why slow java app query but fast in PgAdmin?

Question

Timings for a rather complex query:

web app: 12s
pgAdmin4 (ver 5.2): 700ms (more than 12x speedup!)

Why this difference? The query resturns only 10 rows with 8 columns.

Using VisualVm to sample my app it shows that my app spends whole query time in socket read so the bottleneck must be on postgres side.

Context:

create a temporary table temp (one column id integer)
create index on temp table
use the temp table in my complex select query

SELECT DISTINCT min("baseprop"."first"), max("baseprop"."first"), max("baseprop"."second"), "baseprop"."type", "join1name"."lexeme", count(DISTINCT "baseprop"."first"), , (array_agg(DISTINCT "baseprop"."first"))[1:10], "baseprop"."meaning"
FROM "base"
JOIN "temp" ON "temp"."id" = "base"."id"
JOIN "baseprop" ON "baseprop"."base" = "base"."id"
LEFT OUTER JOIN "join1" ON "baseprop"."join1" = "join1"."id"
LEFT OUTER JOIN "join1name" ON "join1name"."owner" = "join1"."id"
LEFT OUTER JOIN "join2" ON "join2"."id" = "join1"."join2"
WHERE (("baseprop"."meaning" = 'm1'
        AND (("join1name"."lexeme" IN ((strip((to_tsvector('en', 'name11')))), (strip((to_tsvector('en', 'name12')))), (strip((to_tsvector('en', 'name13')))))
              AND "join1"."meaning" = 'm1')
             OR ("join1name"."lexeme" IN ((strip((to_tsvector('en', 'name21')))))
                 AND "join1"."meaning" = 'm1')
             OR ("join1name"."lexeme" IN ((strip((to_tsvector('en', 'name22')))))
                 AND "join1"."meaning" = 'm1')
             OR ("join1name"."lexeme" IN ((strip((to_tsvector('en', 'name23')))))
                 AND "join1"."meaning" = 'm1'))
        AND "join2"."country" = 'en'::country)
       OR ("baseprop"."meaning" = 'm2'
           AND (("join1name"."lexeme" IN ((strip((to_tsvector('en', 'name31')))), (strip((to_tsvector('en', 'name32')))), (strip((to_tsvector('en', 'name33')))), (strip((to_tsvector('en', 'name34')))))
                 AND "join1"."meaning" = 'm2')
                OR ("join1name"."lexeme" IN ((strip((to_tsvector('en', 'name41')))))
                    AND "join1"."meaning" = 'm2')
                OR ("join1name"."lexeme" IN ((strip((to_tsvector('en', 'name51')))))
                    AND "join1"."meaning" = 'm2'))
           AND "join2"."country" = 'en'::country)
       OR ("baseprop"."meaning" = 'm3'
           AND "join1name"."lexeme" IN ((strip((to_tsvector('en', 'name61')))))
           AND "join1"."meaning" = 'm3'
           AND "join2"."country" = 'en'::country)
       OR ("baseprop"."meaning" = 'm4'
           AND "join1name"."lexeme" IN ((strip((to_tsvector('en', 'name71')))))
           AND "join1"."meaning" = 'm4'
           AND "join2"."country" = 'en'::country))
GROUP BY "baseprop"."meaning",
         "baseprop"."type",
         "join1name"."lexeme"

Even without using temp table, I still get 7x speedup diff.

jdbcdriver 'org.postgresql:postgresql:42.2.20'

Postgres 12

EDIT

Seems that the diff is due to PreparedStatement. Issuing a query with raw sql text (embedded params) works as expected. Related: PreparedStatement very slow, but manual query quick

score 2 · Accepted Answer · answered May 03 '21 at 07:17

2

With a prepared statement, PostgreSQL caches query plans and can end up using a "generic" plan that is independent from the parameter values.

To force PostgreSQL to always use a custom plan, set plan_cache_mode to force_custom_plan.

If you are using temporary tables, it may be a good idea to ANALYZE them before you use them.

answered May 03 '21 at 07:17

Laurenz Albe

209,280
17
206
263

I think setting `plan_cache_mode=force_custom_plan` in `postgresql.conf` could lower the performance of other fast queries that benefit from query plan caching. – cdalxndr May 03 '21 at 10:14
1

Absolutely. You want to set it only in the sessions / functions / transactions where you need it. – Laurenz Albe May 03 '21 at 10:52

Why slow java app query but fast in PgAdmin?

EDIT

1 Answers1