title: Index-only scan very slow on small table
body: <p>I am trying to optimize the following PostgreSQL query:</p>
  <pre><code>SELECT DISTINCT route.id,
                  route.ref_origine,
                  route.platform
  FROM route
           INNER JOIN level ON (route.id IN (
      SELECT level_route.route_id
      FROM level_route
      WHERE level.id = level_route.level_id))
           CROSS JOIN site
  WHERE level.ref_site = site.id
    AND site.ref_client = 1
    AND site.visible = TRUE;
  </code></pre>
  <p>I have renamed tables and fields for clarity but it is generated by QueryDSL.</p>
  <p>When analyzing this query, I can see:</p>
  <ul>
  <li>Anticipated planning time is always around 2 seconds</li>
  <li>Actual execution time is always around 1.4 seconds</li>
  </ul>
  <p>I would like to improve this specific query's performance because we have a lot of queries like this one to execute, and it makes our job runs too long.</p>
  <p>PostgreSQL Explain Visualizer gives me the following info when fed one of my explain analyze results:</p>
  <ul>
  <li>execution time (s): 2.16</li>
  <li>planning time (ms): 0.22</li>
  <li>slowest node (s): 2.16</li>
  <li>largest node (rows): 3030</li>
  <li>costliest node: 581,444.75</li>
  </ul>
  <p>Running EXPLAIN ANALYZE again (without JSON format for PEV, so it's a bit different but similar plan) gives me the following output:</p>
  <pre><code>HashAggregate  (cost=582383.52..582444.12 rows=6060 width=17) (actual time=2054.753..2054.984 rows=1088 loops=1)
  &quot;  Group Key: route.id, route.ref_origine, route.platform&quot;
    -&gt;  Nested Loop  (cost=3.99..581516.26 rows=115635 width=17) (actual time=95.786..2053.788 rows=1230 loops=1)
          Join Filter: (SubPlan 1)
          Rows Removed by Join Filter: 268440
          -&gt;  Seq Scan on route  (cost=0.00..50.30 rows=3030 width=17) (actual time=0.006..1.104 rows=3030 loops=1)
          -&gt;  Materialize  (cost=3.99..9.15 rows=76 width=8) (actual time=0.000..0.007 rows=89 loops=3030)
                -&gt;  Hash Join  (cost=3.99..8.77 rows=76 width=8) (actual time=0.029..0.070 rows=89 loops=1)
                      Hash Cond: (level.ref_site = site.id)
                      -&gt;  Seq Scan on level  (cost=0.00..3.47 rows=147 width=16) (actual time=0.002..0.018 rows=148 loops=1)
                      -&gt;  Hash  (cost=3.65..3.65 rows=27 width=8) (actual time=0.021..0.021 rows=28 loops=1)
                            Buckets: 1024  Batches: 1  Memory Usage: 10kB
                            -&gt;  Seq Scan on site  (cost=0.00..3.65 rows=27 width=8) (actual time=0.005..0.017 rows=28 loops=1)
                                  Filter: (visible AND (ref_client = 1))
                                  Rows Removed by Filter: 25
          SubPlan 1
            -&gt;  Index Only Scan using level_route_pkey on level_route  (cost=0.28..4.68 rows=23 width=8) (actual time=0.002..0.005 rows=14 loops=269670)
                  Index Cond: (level_id = level.id)
                  Heap Fetches: 0
  Planning time: 0.221 ms
  Execution time: 2055.094 ms
  </code></pre>
  <p>As far as I know, the problem seems to be lying in the last part of this copy/pasted plan: The index only scan of table level_route.</p>
  <p>Indeed, level_route is a table with 3030 rows, only consisting of two fields: level_id and route_id. A regular association table. Primary key level_route_pkey is (level_id, route_id) so we have an index on this field combination, and it is used in the index only scan.
  While this seems logical and smart (avoids useless heap fetches), why would an index only scan of a 3030 rows table consisting of two basic fields take more than one second to execute?</p>
  <p>There is something I don't understand here. Looking for any advice/suggestions to improve this query execution time and performance.</p>
  <p>Here are the tables and relevant indexes definitions:</p>
  <p><strong>route table</strong></p>
  <pre><code>id          bigint                not null,
  ref_origine bigint,
  platform    boolean default false not null,
  constraint route_pkey
      primary key (id),
  constraint fk6o50xseq5lpmpw8mo8de5nylu
      foreign key (ref_origine) references point
  </code></pre>
  <p><strong>level table</strong></p>
  <pre><code>id          bigint                      not null,
  altitude    integer           default 0 not null,
  elevation   integer                     not null,
  name        varchar(255)                not null,
  svg         text                        not null,
  ref_site    bigint,
  description varchar(10485760) default ''::character varying,
  type        varchar(50)       default NULL::character varying,
  constraint level_pkey
      primary key (id),
  constraint unique_elevation_for_level_in_site
      unique (ref_site, elevation),
  constraint fkl4mlang0ahx72vnrp1slop579
      foreign key (ref_site) references site
  </code></pre>
  <p><strong>level_route table</strong></p>
  <pre><code>level_id bigint not null,
  route_id bigint not null,
  constraint level_route_pkey
      primary key (level_id, route_id),
  constraint fkd0kfn8u2jfqcg46li5aw2mp5n
      foreign key (level_id) references level,
  constraint fkm1to01mwy537dxb7vu8qk9ut1
      foreign key (route_id) references route
  
  create index if not exists level_route_level_id_index
      on level_route (level_id);
  </code></pre>
  <p><strong>site table</strong></p>
  <pre><code>id          bigint                                          not null,
  description varchar(255),
  name        varchar(255)                                    not null,
  naoid       bigint,
  naotoken    varchar(255),
  version     integer      default 0                          not null,
  ref_client  bigint,
  uuid        varchar(255) default ''::character varying      not null,
  deployement varchar(50)  default 'Never'::character varying not null,
  deployedat  timestamp,
  visible     boolean      default false                      not null,
  constraint site_pkey
      primary key (id),
  constraint fkhqoxjhhr0ud7i5cvu9nhwc62k
      foreign key (ref_client) references client
  </code></pre>
  <p>Thanks a lot to anyone who will take some time to help me here.</p>