Search code examples
postgresqlpgadminpgadmin-4

Why slow java app query but fast in PgAdmin?

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:

  1. create a temporary table temp (one column id integer)
  2. create index on temp table
  3. 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

Solution

  • 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.