Query Optimizer bei Functions als Filter

Hallo,

ich bin über ein seltsames Laufzeitverhalten gestolpert. Scheinbar wird bei einem normalen where = bzw. genauer where = der Index nicht verwendet. Das irritiert mich. Kann man das Verhalten beeinflussen? Bzw. wann tritt das auf? Bei nicht deterministischen Funktionen? Bug oder Feature?

(Es geht hier inhaltlich zunächst nur darum, Testdaten zu generieren, also bitte nicht über die Abfragen wundern.)

perftest=# CREATE UNIQUE INDEX snu ON device (serial_number);
 FEHLER:  Relation »snu« existiert bereits
Zeit: 1,402 ms
perftest=#
perftest=# select count(*) from device;
  count
---------
 2096376
(1 Zeile)

Zeit: 341,468 ms
perftest=#
perftest=# select version();
                                                     version                    
------------------------------------------------------------------------------------------------------------------
 PostgreSQL 12.2 (Debian 12.2-1.pgdg100+1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 8.3.0-6) 8.3.0, 64-bit
(1 Zeile)
Zeit: 0,180 ms

perftest=#
perftest=#
perftest=# select * from device
perftest-# where serial_number = '123JZG00002P7110303334';
 serial_number | type_of_device | container_version | configuration | tags
---------------+-----------------+-------------------+---------------+------
(0 Zeilen)

Zeit: 0,303 ms
perftest=#
perftest=#
perftest=# explain
perftest-# select * from device
perftest-# where serial_number = '123JZG00002P7110303334';
                               QUERY PLAN
------------------------------------------------------------------------
 Index Scan using snu on device  (cost=0.43..8.45 rows=1 width=147)
   Index Cond: ((serial_number)::text = '123JZG00002P7110303334'::text)
(2 Zeilen)

Zeit: 0,387 ms
perftest=#
perftest=# select * from device
perftest-# where serial_number =
perftest-# '123JZG00002P'||trunc(random()*3)+7||1||trunc(random()*100000000);
 serial_number | type_of_device | container_version | configuration | tags
---------------+-----------------+-------------------+---------------+------
(0 Zeilen)

Zeit: 1987,309 ms (00:01,987)
perftest=#
perftest=#
perftest=# explain
perftest-# select * from device
perftest-# where serial_number =
perftest-# '123JZG00002P'||trunc(random()*3)+7||1||trunc(random()*100000000);
Zeit: 1,519 ms
                       QUERY PLAN                                               
-----------------------------------------------------------------------------
 Seq Scan on gateway  (cost=0.00..146491.86 rows=1 width=147)
   Filter: ((serial_number)::text = ((('123JZG00002P'::text || ((trunc((random() * '3'::double precision)) + '7'::double precision))::text) || '1'::text) || (trunc((random() * '100000000'::double precision)))::text))
 JIT:
   Functions: 2
   Options: Inlining false, Optimization false, Expressions true, Deforming true
(5 Zeilen)
[code]

Es gibt funktionale Indexe, allerdings wird das nix wenn Du da mit random() hantierst.

Um mal ein Beispiel zu zeigen:

test=# create table demo(name text);
CREATE TABLE
test=*# copy demo from stdin;
Enter data to be copied followed by a newline.
End with a backslash and a period on a line by itself, or an EOF signal.
>> Andreas
>> Peter     
>> Susi
>> \.
COPY 3
test=*# create index on demo (lower(name));
CREATE INDEX
test=*# explain analyse select * from demo where name = 'Andreas';
                                          QUERY PLAN                                           
-----------------------------------------------------------------------------------------------
 Seq Scan on demo  (cost=0.00..1.04 rows=1 width=32) (actual time=0.014..0.016 rows=1 loops=1)
   Filter: (name = 'Andreas'::text)
   Rows Removed by Filter: 2
 Planning Time: 0.244 ms
 Execution Time: 0.039 ms
(5 rows)

test=*# explain analyse select * from demo where lower(name) = lower('Andreas');
                                          QUERY PLAN                                           
-----------------------------------------------------------------------------------------------
 Seq Scan on demo  (cost=0.00..1.04 rows=1 width=32) (actual time=0.016..0.020 rows=1 loops=1)
   Filter: (lower(name) = 'andreas'::text)
   Rows Removed by Filter: 2
 Planning Time: 0.176 ms
 Execution Time: 0.042 ms
(5 rows)

test=*# set enable_seqscan to off;
SET
test=*# explain analyse select * from demo where lower(name) = lower('Andreas');
                                                      QUERY PLAN                                                      
----------------------------------------------------------------------------------------------------------------------
 Index Scan using demo_lower_idx on demo  (cost=0.13..8.15 rows=1 width=32) (actual time=0.102..0.104 rows=1 loops=1)
   Index Cond: (lower(name) = 'andreas'::text)
 Planning Time: 0.121 ms
 Execution Time: 0.128 ms
(4 rows)

test=*#

weil die Tabelle zu klein ist mußte ich noch seqscan ausschalten …

Nach meinem Verständnis ist das keine Problematik, wo ein Functional Index zuständig ist / angewendet würde.
Es gibt eine indizierte Spalte, die ohne(!) Function direkt, unverändert gefiltert wird. Zwar über eine Expression, die Funktion(en) beinhaltet, die ist aber zum Ausführungszeitpunkt berechenbar, ähnlich wie Zeitfunktionen oder andere.

@castorp:
Das passt besser und es wird tatsächlich der Index benutzt.
Muss ich mir mal genauer anschauen die Ecke. Danke!

ja, genau, volatile, das meinte ich mit ‘nicht deterministisch’.
Aber es passt trotzdem nicht zu meiner Vorstellung von DB Verhalten, denn hinter der Expression stecken keine Rows. Die Rows sind ja in dem Fall zumindest auf der “anderen Seite”. Eine Expression müsste eigentlich genau einen Wert liefern, was sie ja in diversen Contexten auch tut. Im Prinzip müsste sie “von Natur aus” eigentlich das machen, was Du mit dem kaschierenden Select machst, das liefert definitv nur eine Row.

Wenn ich die Random Funktion im Select mit einer Table von 5 Rows aufrufe, dann will ich natürlich 5 unterschiedliche Werte.

Ich verstehe das Verhalten, aber es scheint mir irgendwie daneben.
In der PG Doku habe ich zwar grundsätzliche Erklärungen zu Volatile, Stable und Immutable gefunden, aber keine Listen, was was ist oder entsprechende Notizen je Funktion. Gibt es das irgendwo?

Du kannst je Funktion in psql mittels \df+ das sehen:

test=# \df+ random
                                                                                List of functions
   Schema   |  Name  | Result data type | Argument data types | Type | Volatility |  Parallel  |  Owner   | Security | Access privileges | Language | Source code | Description  
------------+--------+------------------+---------------------+------+------------+------------+----------+----------+-------------------+----------+-------------+--------------
 pg_catalog | random | double precision |                     | func | volatile   | restricted | postgres | invoker  |                   | internal | drandom     | random value
(1 row)

Du kannst es auch in den Katalogen abfrage, siehe https://www.postgresql.org/docs/12/catalog-pg-proc.html, Spalte provolatile:

provolatile tells whether the function’s result depends only on its input arguments, or is affected by outside factors. It is i for “immutable” functions, which always deliver the same result for the same inputs. It is s for “stable” functions, whose results (for fixed inputs) do not change within a scan. It is v for “volatile” functions, whose results might change at any time. (Use v also for functions with side-effects, so that calls to them cannot get optimized away.)

Danke!