pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  

help / color / mirror / Atom feed

PostgreSQL doesn't use index-only scan if there is an expression in index
2+ messages / 2 participants
[nested] [flat]

* PostgreSQL doesn't use index-only scan if there is an expression in index
@ 2024-02-15 14:37 Pavel Kulakov <paul.kulakov@systematica.ru>
  2024-02-15 15:01 ` Re: PostgreSQL doesn't use index-only scan if there is an expression in index Laurenz Albe <laurenz.albe@cybertec.at>
  0 siblings, 1 reply; 2+ messages in thread

From: Pavel Kulakov @ 2024-02-15 14:37 UTC (permalink / raw)
  To: pgsql-performance@lists.postgresql.org

Hello,

 

PostgreSQL doesn't use 'Index Only Scan' if there is an expression in index.

 

The documentation says that PostgreSQL's planner considers a query to be
potentially executable by index-only scan only when all columns needed by
the query are available from the index. 

I think an example on
https://www.postgresql.org/docs/16/indexes-index-only-scans.html :

 

SELECT f(x) FROM tab WHERE f(x) < 1;

 

is a bit confusing. Even the following query does not use 'Index Only Scan'

 

SELECT 1 FROM tab WHERE f(x) < 1;

 

Demonstration:

---------------------------

drop table if exists test;

 

create table test(s text);

create index ix_test_upper on test (upper(s));

create index ix_test_normal on test (s);

 

insert into test (s)

select 'Item' || t.i

from pg_catalog.generate_series(1, 100000, 1) t(i);

 

analyze verbose "test";

 

explain select 1 from test where s = 'Item123';

explain select 1 from test where upper(s) = upper('Item123');

--------------------------

Query plan 1:

Index Only Scan using ix_test_normal on test  (cost=0.42..8.44 rows=1
width=4)

  Index Cond: (s = 'Item123'::text)

  

Query plan 2 (SHOULD BE 'Index Only Scan'):

Index Scan using ix_test_upper on test  (cost=0.42..8.44 rows=1 width=4)

  Index Cond: (upper(s) = 'ITEM123'::text)

------------------------   

 

If I add 's' as included column to ix_test_upper the plan does use 'Index
Only Scan'. That looks strange to me: there is no 's' in SELECT-clause, only
in WHERE-clause in the form of 'upper(s)' and this is why ix_test_upper is
choosen by the planner.

 

Thanks,

Pavel

^ permalink  raw  reply  [nested|flat] 2+ messages in thread

* Re: PostgreSQL doesn't use index-only scan if there is an expression in index
  2024-02-15 14:37 PostgreSQL doesn't use index-only scan if there is an expression in index Pavel Kulakov <paul.kulakov@systematica.ru>
@ 2024-02-15 15:01 ` Laurenz Albe <laurenz.albe@cybertec.at>
  0 siblings, 0 replies; 2+ messages in thread

From: Laurenz Albe @ 2024-02-15 15:01 UTC (permalink / raw)
  To: Pavel Kulakov <paul.kulakov@systematica.ru>; pgsql-performance@lists.postgresql.org

On Thu, 2024-02-15 at 17:37 +0300, Pavel Kulakov wrote:
> Hello,
>  
> PostgreSQL doesn't use 'Index Only Scan' if there is an expression in index.
>  
> The documentation says that PostgreSQL's planner considers a query to be potentially
> executable by index-only scan only when all columns needed by the query are available from the index. 
> I think an example on https://www.postgresql.org/docs/16/indexes-index-only-scans.html :
>  
> SELECT f(x) FROM tab WHERE f(x) < 1;
>  
> is a bit confusing. Even the following query does not use 'Index Only Scan'
>  
> SELECT 1 FROM tab WHERE f(x) < 1;
>  
> Demonstration:
> ---------------------------
> drop table if exists test;
>  
> create table test(s text);
> create index ix_test_upper on test (upper(s));
> create index ix_test_normal on test (s);
>  
> insert into test (s)
> select 'Item' || t.i
> from pg_catalog.generate_series(1, 100000, 1) t(i);
>  
> analyze verbose "test";
>  
> explain select 1 from test where s = 'Item123';
> explain select 1 from test where upper(s) = upper('Item123');
> --------------------------
> Query plan 1:
> Index Only Scan using ix_test_normal on test  (cost=0.42..8.44 rows=1 width=4)
>   Index Cond: (s = 'Item123'::text)
>   
> Query plan 2 (SHOULD BE 'Index Only Scan'):
> Index Scan using ix_test_upper on test  (cost=0.42..8.44 rows=1 width=4)
>   Index Cond: (upper(s) = 'ITEM123'::text)
> ------------------------   
>  
> If I add 's' as included column to ix_test_upper the plan does use 'Index Only Scan'.
> That looks strange to me: there is no 's' in SELECT-clause, only in WHERE-clause in
> the form of 'upper(s)' and this is why ix_test_upper is choosen by the planner.

You need to create the index like this:

  CREATE INDEX ix_test_upper ON test (upper(s)) INCLUDE (s);

See https://www.postgresql.org/docs/current/indexes-index-only-scans.html:

 "In principle, index-only scans can be used with expression indexes.
  For example, given an index on f(x) where x is a table column, it
  should be possible to execute

    SELECT f(x) FROM tab WHERE f(x) < 1;

  as an index-only scan; and this is very attractive if f() is an
  expensive-to-compute function. 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. If an index-only scan seems sufficiently
  worthwhile, this can be worked around by adding x as an included column,
  for example

    CREATE INDEX tab_f_x ON tab (f(x)) INCLUDE (x);

Yours,
Laurenz Albe





^ permalink  raw  reply  [nested|flat] 2+ messages in thread

end of thread, other threads:[~2024-02-15 15:01 UTC | newest]

Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2024-02-15 14:37 PostgreSQL doesn't use index-only scan if there is an expression in index Pavel Kulakov <paul.kulakov@systematica.ru>
2024-02-15 15:01 ` Laurenz Albe <laurenz.albe@cybertec.at>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox