Thread

1. Index Only Scans with functions

   The Post Office <noreply@postgresql.org> — 2026-07-05T06:48:39Z

   The following documentation comment has been logged on the website:
   
   Page: https://www.postgresql.org/docs/18/indexes-index-only-scans.html
   Description:
   
   On the documentation I read:
   
   "However, PostgreSQL's planner is currently not very smart about such cases.
   It considers a query to be potentially executable by index-only scan only
   when all columns needed by the query are available from the index. In this
   example, x is not needed except in the context f(x), but the planner does
   not notice that and concludes that an index-only scan is not possible."
   
   I tried to reproduce the behavior, but it used the index (which is good):
   select version();
   drop table if exists t;
   drop function if exists add_ten;
   create or replace function add_ten(a integer)
   returns integer
   language plpgsql
   immutable
   as $$
   begin
       return a + 10;
   end;
   $$;
   create table t as select generate_series(1, 1000000) a;
   create index t_i1 on t (add_ten(a));
   analyze t;
   explain analyze select add_ten(a) from t where add_ten(a) < 12;
   
   Output:
                                                                  version
   -------------------------------------------------------------------------------------------------------------------------------------
    PostgreSQL 18.4 (Ubuntu 18.4-1.pgdg24.04+1) on x86_64-pc-linux-gnu,
   compiled by gcc (Ubuntu 13.3.0-6ubuntu2~24.04.1) 13.3.0, 64-bit
   (1 row)
   
   DROP TABLE
   DROP FUNCTION
   CREATE FUNCTION
   SELECT 1000000
   CREATE INDEX
   ANALYZE
                                                   QUERY PLAN
   -----------------------------------------------------------------------------------------------------------
    Index Scan using t_i1 on t  (cost=0.42..8.69 rows=1 width=4) (actual
   time=0.034..0.035 rows=1.00 loops=1)
      Index Cond: (add_ten(a) < 12)
      Index Searches: 1
      Buffers: shared hit=4
    Planning:
      Buffers: shared hit=8 read=5
    Planning Time: 0.154 ms
    Execution Time: 0.051 ms
   (8 rows)
   
   mydb=#
   
   I also tried some older versions on dbfiddle.uk without success. What did I
   do wrong?
   
   Jochen
   
   
   
   
   

2. Re: Index Only Scans with functions

   David G. Johnston <david.g.johnston@gmail.com> — 2026-07-06T15:49:36Z

   On Mon, Jul 6, 2026 at 8:44 AM PG Doc comments form <noreply@postgresql.org>
   wrote:
   
   > The following documentation comment has been logged on the website:
   >
   > Page: https://www.postgresql.org/docs/18/indexes-index-only-scans.html
   >
   
   
   > and concludes that an index-only scan is not possible."
   >
   > I tried to reproduce the behavior, but it used the index (which is good):
   >
   
   You haven't demonstrated it using an index ONLY scan which is what that
   section is talking about.
   
   
   >                                                 QUERY PLAN
   >
   > -----------------------------------------------------------------------------------------------------------
   >  Index Scan using t_i1 on t  (cost=0.42..8.69 rows=1 width=4) (actual
   > time=0.034..0.035 rows=1.00 loops=1)
   >
   >
   
   David J.