Content-Type: multipart/mixed; boundary="------------NmCGWYZDUZIHSKtfK7rHtp2b"
Message-ID: <a8b99dce-1bfb-4d97-af73-54a32b85c916@dalibo.com>
Date: Wed, 17 Sep 2025 15:55:29 +0200
MIME-Version: 1.0
User-Agent: Mozilla Thunderbird
From: =?UTF-8?Q?Fr=C3=A9d=C3=A9ric_Yhuel?= <frederic.yhuel@dalibo.com>
Content-Language: fr, en-US
To: "pgsql-performance@lists.postgresql.org"
 <pgsql-performance@lists.postgresql.org>
Subject: Indexes on expressions with multiple columns and operators
Autocrypt: addr=frederic.yhuel@dalibo.com; keydata=
 xjMEXn3bgxYJKwYBBAHaRw8BAQdA6tX5FT/n5ztMWIoBdl6k5avvu65fv6ryfVzIx/aH3V3N
 JEYuIFlodWVsIDxmcmVkZXJpYy55aHVlbEBkYWxpYm8uY29tPsKWBBMWCAA+FiEEi7OTyf6H
 ePS+C6n11XGWhN8zGNUFAmZDVJ8CGwMFCQtfi90FCwkIBwIGFQoJCAsCBBYCAwECHgECF4AA
 CgkQ1XGWhN8zGNV65gEA6o+UrebFjn2CIAOYwP3CiiJugiM5TMH9yJyehQua5ZoA/2ChxiVn
 8OUaAw/ErxJmQi1mSN0tNZBL/KhkUPBDUDQNzjgEXn3bgxIKKwYBBAGXVQEFAQEHQCuh5cLQ
 AtEBXHa2Fmtp0kduBu7msM7qO/gaEcdjFUdiAwEIB8J+BBgWCAAmAhsMFiEEi7OTyf6HePS+
 C6n11XGWhN8zGNUFAmZDVJ8FCQtfi90ACgkQ1XGWhN8zGNWOOAEArSqePBqfd4Kx4ulACaWO
 6fM+XSDfUlBAQXx9rU6DR4MBALwozl9g91tRysim6lKxggvBfp/PvbpTZNrxYLWB2fYP
Cc: Jehan-Guillaume de Rorthais <jgdr@dalibo.com>,
 Christophe Courtois <christophe.courtois@dalibo.com>
Archived-At: 
 <https://www.postgresql.org/message-id/a8b99dce-1bfb-4d97-af73-54a32b85c916%40dalibo.com>
Precedence: bulk

This is a multi-part message in MIME format.
--------------NmCGWYZDUZIHSKtfK7rHtp2b
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 8bit

Hello, in the following, I don't understand why:

1) the expression index isn't used in the first EXPLAIN

2) the number of estimated rows is completely off in the second EXPLAIN, 
whereas the planner could easily use the statistics of foo_f_idx.

(SQL script attached, tested with master and v17)

DROP TABLE IF EXISTS foo;

CREATE UNLOGGED TABLE foo (id bigint, ackid int, crit text);

ALTER TABLE foo ALTER COLUMN crit SET statistics 400;

INSERT INTO foo SELECT i, NULL, CASE WHEN i%100=1 THEN 'WARNING' ELSE 
'INFO' END FROM generate_series(1,100000) AS T(i);

UPDATE foo SET ackid = random()*10000 WHERE id%100=1 AND id > 500 ;

CREATE INDEX foo_expr_idx ON foo ((ackid IS NULL AND crit = 'WARNING'));

ANALYZE foo ;

EXPLAIN (ANALYZE, SUMMARY OFF, BUFFERS OFF) SELECT * FROM foo WHERE 
ackid IS NULL AND crit = 'WARNING';
                                               QUERY PLAN 

-------------------------------------------------------------------------------------------------------
  Seq Scan on foo  (cost=0.00..1797.00 rows=990 width=17) (actual 
time=0.012..23.932 rows=5.00 loops=1)
    Filter: ((ackid IS NULL) AND (crit = 'WARNING'::text))
    Rows Removed by Filter: 99995
(3 rows)

CREATE OR REPLACE
FUNCTION f(crit text, ackid int)
RETURNS bool
LANGUAGE plpgsql
IMMUTABLE AS $$
   BEGIN RETURN crit = 'WARNING' AND ackid IS NULL; END
$$;

CREATE INDEX foo_f_idx ON foo (f(crit, ackid));

ANALYZE foo ;

EXPLAIN (ANALYZE, SUMMARY OFF, BUFFERS OFF) SELECT * FROM foo WHERE 
f(crit, ackid);
                                                       QUERY PLAN 

-----------------------------------------------------------------------------------------------------------------------
  Index Scan using foo_f_idx on foo  (cost=0.29..8.39 rows=33333 
width=17) (actual time=0.021..0.028 rows=5.00 loops=1)
    Index Cond: (f(crit, ackid) = true)
    Index Searches: 1
(3 rows)

SELECT tablename, most_common_vals, most_common_freqs FROM pg_stats 
WHERE tablename like 'foo_%';
   tablename   | most_common_vals | most_common_freqs
--------------+------------------+-------------------
  foo_expr_idx | {f,t}            | {0.99995,5e-05}
  foo_f_idx    | {f,t}            | {0.99995,5e-05}

SELECT 100000 * 5e-05 AS the_row_estimate_that_the_planner_should_use;
  the_row_estimate_that_the_planner_should_use
---------------------------------------------
                                      5.00000


Best regards,
Frédéric
--------------NmCGWYZDUZIHSKtfK7rHtp2b
Content-Type: application/sql; name="expr_index.sql"
Content-Disposition: attachment; filename="expr_index.sql"
Content-Transfer-Encoding: base64

XHNldCBFQ0hPIHF1ZXJpZXMKXHNldCB0b3RhbF9yb3dzIDEwMDAwMAoKRFJPUCBUQUJMRSBJ
RiBFWElTVFMgZm9vOwoKQ1JFQVRFIFVOTE9HR0VEIFRBQkxFIGZvbyAoaWQgYmlnaW50LCBh
Y2tpZCBpbnQsIGNyaXQgdGV4dCk7CgpBTFRFUiBUQUJMRSBmb28gQUxURVIgQ09MVU1OIGNy
aXQgU0VUIHN0YXRpc3RpY3MgNDAwOwoKSU5TRVJUIElOVE8gZm9vIFNFTEVDVCBpLCBOVUxM
LCBDQVNFIFdIRU4gaSUxMDA9MSBUSEVOICdXQVJOSU5HJyBFTFNFICdJTkZPJyBFTkQgRlJP
TSBnZW5lcmF0ZV9zZXJpZXMoMSwgOnRvdGFsX3Jvd3MpIEFTIFQoaSk7CgpVUERBVEUgZm9v
IFNFVCBhY2tpZCA9IHJhbmRvbSgpKjEwMDAwIFdIRVJFIGlkJTEwMD0xIEFORCBpZCA+IDUw
MCA7CgpDUkVBVEUgSU5ERVggZm9vX2V4cHJfaWR4IE9OIGZvbyAoKGFja2lkIElTIE5VTEwg
QU5EIGNyaXQgPSAnV0FSTklORycpKTsKQU5BTFlaRSBmb28gOwoKRVhQTEFJTiAoQU5BTFla
RSwgU1VNTUFSWSBPRkYsIEJVRkZFUlMgT0ZGKSBTRUxFQ1QgKiBGUk9NIGZvbyBXSEVSRSBh
Y2tpZCBJUyBOVUxMIEFORCBjcml0ID0gJ1dBUk5JTkcnOwoKQ1JFQVRFIE9SIFJFUExBQ0UK
RlVOQ1RJT04gZihjcml0IHRleHQsIGFja2lkIGludCkKUkVUVVJOUyBib29sCkxBTkdVQUdF
IHBscGdzcWwKSU1NVVRBQkxFIEFTICQkCiAgQkVHSU4gUkVUVVJOIGNyaXQgPSAnV0FSTklO
RycgQU5EIGFja2lkIElTIE5VTEw7IEVORAokJDsKCkNSRUFURSBJTkRFWCBmb29fZl9pZHgg
T04gZm9vIChmKGNyaXQsIGFja2lkKSk7CkFOQUxZWkUgZm9vIDsKCkVYUExBSU4gKEFOQUxZ
WkUsIFNVTU1BUlkgT0ZGLCBCVUZGRVJTIE9GRikgU0VMRUNUICogRlJPTSBmb28gV0hFUkUg
Zihjcml0LCBhY2tpZCk7CgpTRUxFQ1QgdGFibGVuYW1lLCBtb3N0X2NvbW1vbl92YWxzLCBt
b3N0X2NvbW1vbl9mcmVxcyBGUk9NIHBnX3N0YXRzIFdIRVJFIHRhYmxlbmFtZSBsaWtlICdm
b29fJSc7CgpTRUxFQ1QgbW9zdF9jb21tb25fZnJlcXNbMl0gQVMgZnJlcSBGUk9NIHBnX3N0
YXRzIFdIRVJFIHRhYmxlbmFtZSA9ICdmb29fZl9pZHgnIFxnc2V0CgpTRUxFQ1QgOnRvdGFs
X3Jvd3MgKiA6ZnJlcSBBUyB0aGVfcm93X2VzdGltYXRlX3RoYXRfdGhlX3BsYW5uZXJfc2hv
dWxkX3VzZTsK

--------------NmCGWYZDUZIHSKtfK7rHtp2b--