title: Query runs much slower using JDBC
body: <h2>I have two different queries that take about the same amount of time to execute when I timed with Adminer or DBeaver</h2>
  
  <p><strong>Query one</strong></p>
  
  <pre><code>select * from state where state_name = 'Florida';
  </code></pre>
  
  <p>When I run the query above in Adminer it takes anywhere from </p>
  
  <blockquote>
    <p>0.032 s to 0.058 s</p>
  </blockquote>
  
  <p>EXPLAIN ANALYZE</p>
  
  <pre><code>Seq Scan on state  (cost=0.00..3981.50 rows=1 width=28) (actual time=1.787..15.047 rows=1 loops=1)
    Filter: (state_name = 'Florida'::citext)
    Rows Removed by Filter: 50
  Planning Time: 0.486 ms
  Execution Time: 15.779 ms
  </code></pre>
  
  <p><strong>Query two</strong></p>
  
  <pre><code>select
      property.id as property_id ,
      full_address,
      street_address,
      street.street,
      city.city as city,
      state.state_code as state_code,
      zipcode.zipcode as zipcode
  from
      property
  inner join street on
      street.id = property.street_id
  inner join city on
      city.id = property.city_id
  inner join state on
      state.id = property.state_id
  inner join zipcode on
      zipcode.id = property.zipcode_id
  where
      full_address = '139-Skillman-Ave-Apt-5C-Brooklyn-NY-11211';
  </code></pre>
  
  <p>The above query takes from </p>
  
  <blockquote>
    <p>0.025 s to 0.048 s</p>
  </blockquote>
  
  <p>EXPLAIN ANALYZE</p>
  
  <pre><code>Nested Loop  (cost=29.82..65.96 rows=1 width=97) (actual time=0.668..0.671 rows=1 loops=1)
    -&gt;  Nested Loop  (cost=29.53..57.65 rows=1 width=107) (actual time=0.617..0.620 rows=1 loops=1)
          -&gt;  Nested Loop  (cost=29.25..49.30 rows=1 width=120) (actual time=0.582..0.585 rows=1 loops=1)
                -&gt;  Nested Loop  (cost=28.97..41.00 rows=1 width=127) (actual time=0.532..0.534 rows=1 loops=1)
                      -&gt;  Bitmap Heap Scan on property  (cost=28.54..32.56 rows=1 width=131) (actual time=0.454..0.456 rows=1 loops=1)
                            Recheck Cond: (full_address = '139-Skillman-Ave-Apt-5C-Brooklyn-NY-11211'::citext)
                            Heap Blocks: exact=1
                            -&gt;  Bitmap Index Scan on property_full_address  (cost=0.00..28.54 rows=1 width=0) (actual time=0.426..0.426 rows=1 loops=1)
                                  Index Cond: (full_address = '139-Skillman-Ave-Apt-5C-Brooklyn-NY-11211'::citext)
                      -&gt;  Index Scan using street_pkey on street  (cost=0.42..8.44 rows=1 width=28) (actual time=0.070..0.070 rows=1 loops=1)
                            Index Cond: (id = property.street_id)
                -&gt;  Index Scan using city_id_pk on city  (cost=0.29..8.30 rows=1 width=25) (actual time=0.047..0.047 rows=1 loops=1)
                      Index Cond: (id = property.city_id)
          -&gt;  Index Scan using state_id_pk on state  (cost=0.28..8.32 rows=1 width=19) (actual time=0.032..0.032 rows=1 loops=1)
                Index Cond: (id = property.state_id)
    -&gt;  Index Scan using zipcode_id_pk on zipcode  (cost=0.29..8.30 rows=1 width=22) (actual time=0.048..0.048 rows=1 loops=1)
          Index Cond: (id = property.zipcode_id)
  Planning Time: 5.473 ms
  Execution Time: 1.601 ms
  </code></pre>
  
  <h2>I have the following methods which uses JDBCTemplate to execute the same queries.</h2>
  
  <p><strong>Query one</strong></p>
  
  <pre><code>public void performanceTest(String str) {
      template.queryForObject(
              "select * from state where state_name = ?",
              new Object[] { str }, (result, rowNum) -&gt; {
                  return result.getObject("state_name");
              });
  
  }
  </code></pre>
  
  <blockquote>
    <p>time: 140ms, which is 0.14 seconds</p>
  </blockquote>
  
  <p><strong>Query two</strong></p>
  
  <pre><code>public void performanceTest(String str) {
      template.queryForObject(
              "SELECT property.id AS property_id , full_address, street_address, street.street, city.city as city, state.state_code as state_code, zipcode.zipcode as zipcode FROM property INNER JOIN street ON street.id = property.street_id INNER JOIN city ON city.id = property.city_id INNER JOIN state ON state.id = property.state_id INNER JOIN zipcode ON zipcode.id = property.zipcode_id WHERE full_address = ?",
              new Object[] { str }, (result, rowNum) -&gt; {
                  return result.getObject("property_id");
              });
  
  }
  </code></pre>
  
  <p>The time it takes to execute the method above is</p>
  
  <blockquote>
    <p>time: 828 ms, which is 0.825 seconds</p>
  </blockquote>
  
  <p>I am timing the method's execution time using this code below</p>
  
  <pre><code>long startTime1 = System.nanoTime();
  propertyRepo.performanceTest(address); //or "Florida" depending which query I'm testing
  long endTime1 = System.nanoTime();
  long duration1 = TimeUnit.MILLISECONDS.convert((endTime1 - startTime1), TimeUnit.NANOSECONDS);
  System.out.println("time: " + duration1);
  </code></pre>
  
  <p>Why is query two so much slower when I run it from JDBC compared to when I run it from Adminer? Anything I can do to improve the performance for query two?</p>
  
  <p><strong>EDIT:</strong></p>
  
  <p>I created two different PHP scripts containing the queries respectively. They take the same amount of time using PHP, so I assume it has something to do with JDBC? Below is the result of the PHP scripts. The time PHP takes is a higher than Java takes with Query one since I am not using any connection pooling. But both queries are taking pretty much the same amount of time to execute. Something is causing a delay with Query two on JDBC.</p>
  
  <p><a href="https://i.stack.imgur.com/XARw1.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/XARw1.png" alt="enter image description here"></a></p>
  
  <p><strong>EDIT:</strong></p>
  
  <p>When I run the query using prepared statement it's slow. But it's fast when I run it with statement. I did EXPLAIN ANALYZE for both, using preparedStatement and statement</p>
  
  <p><strong>preparedStatement explain analyze</strong></p>
  
  <pre><code>Nested Loop  (cost=1.27..315241.91 rows=1 width=97) (actual time=0.091..688.583 rows=1 loops=1)
    -&gt;  Nested Loop  (cost=0.98..315233.61 rows=1 width=107) (actual time=0.079..688.571 rows=1 loops=1)
          -&gt;  Nested Loop  (cost=0.71..315225.26 rows=1 width=120) (actual time=0.069..688.561 rows=1 loops=1)
                -&gt;  Nested Loop  (cost=0.42..315216.95 rows=1 width=127) (actual time=0.057..688.548 rows=1 loops=1)
                      -&gt;  Seq Scan on property  (cost=0.00..315208.51 rows=1 width=131) (actual time=0.032..688.522 rows=1 loops=1)
                            Filter: ((full_address)::text = '139-Skillman-Ave-Apt-5C-Brooklyn-NY-11211'::text)
                            Rows Removed by Filter: 8790
                      -&gt;  Index Scan using street_pkey on street  (cost=0.42..8.44 rows=1 width=28) (actual time=0.019..0.019 rows=1 loops=1)
                            Index Cond: (id = property.street_id)
                -&gt;  Index Scan using city_id_pk on city  (cost=0.29..8.30 rows=1 width=25) (actual time=0.010..0.010 rows=1 loops=1)
                      Index Cond: (id = property.city_id)
          -&gt;  Index Scan using state_id_pk on state  (cost=0.28..8.32 rows=1 width=19) (actual time=0.008..0.008 rows=1 loops=1)
                Index Cond: (id = property.state_id)
    -&gt;  Index Scan using zipcode_id_pk on zipcode  (cost=0.29..8.30 rows=1 width=22) (actual time=0.010..0.010 rows=1 loops=1)
          Index Cond: (id = property.zipcode_id)
  Planning Time: 2.400 ms
  Execution Time: 688.674 ms
  </code></pre>
  
  <p><strong>statement explain analyze</strong></p>
  
  <pre><code>Nested Loop  (cost=29.82..65.96 rows=1 width=97) (actual time=0.232..0.235 rows=1 loops=1)
    -&gt;  Nested Loop  (cost=29.53..57.65 rows=1 width=107) (actual time=0.220..0.223 rows=1 loops=1)
          -&gt;  Nested Loop  (cost=29.25..49.30 rows=1 width=120) (actual time=0.211..0.213 rows=1 loops=1)
                -&gt;  Nested Loop  (cost=28.97..41.00 rows=1 width=127) (actual time=0.198..0.200 rows=1 loops=1)
                      -&gt;  Bitmap Heap Scan on property  (cost=28.54..32.56 rows=1 width=131) (actual time=0.175..0.177 rows=1 loops=1)
                            Recheck Cond: (full_address = '139-Skillman-Ave-Apt-5C-Brooklyn-NY-11211'::citext)
                            Heap Blocks: exact=1
                            -&gt;  Bitmap Index Scan on property_full_address  (cost=0.00..28.54 rows=1 width=0) (actual time=0.162..0.162 rows=1 loops=1)
                                  Index Cond: (full_address = '139-Skillman-Ave-Apt-5C-Brooklyn-NY-11211'::citext)
                      -&gt;  Index Scan using street_pkey on street  (cost=0.42..8.44 rows=1 width=28) (actual time=0.017..0.017 rows=1 loops=1)
                            Index Cond: (id = property.street_id)
                -&gt;  Index Scan using city_id_pk on city  (cost=0.29..8.30 rows=1 width=25) (actual time=0.010..0.010 rows=1 loops=1)
                      Index Cond: (id = property.city_id)
          -&gt;  Index Scan using state_id_pk on state  (cost=0.28..8.32 rows=1 width=19) (actual time=0.007..0.007 rows=1 loops=1)
                Index Cond: (id = property.state_id)
    -&gt;  Index Scan using zipcode_id_pk on zipcode  (cost=0.29..8.30 rows=1 width=22) (actual time=0.010..0.010 rows=1 loops=1)
          Index Cond: (id = property.zipcode_id)
  Planning Time: 2.442 ms
  Execution Time: 0.345 ms
  </code></pre>