Bug in the redundant indexes report

[gleu]

The WHERE clause doesn't seem to be checked. Here is a quick example:

CREATE TABLE t1 (id integer);
CREATE INDEX ON t1(id) WHERE id=1;
CREATE INDEX ON t1(id) WHERE id=2;

The query used by pgcluu will find they are redundant indexes. But they aren't.

┌─[ RECORD 1 ]─────┬──────────────────────────────────────────────────────────────────────┐
│ date_trunc       │ 2022-04-11 15:00:28+02                                               │
│ current_database │ postgres                                                             │
│ contained        │ CREATE INDEX t1_id_idx1 ON public.t1 USING btree (id) WHERE (id = 2) │
│ container        │ CREATE INDEX t1_id_idx ON public.t1 USING btree (id) WHERE (id = 1)  │
└──────────────────┴──────────────────────────────────────────────────────────────────────┘

Not sure how we can fix it though.

[darold]

Commit 168ff82 is an attempt to fix this issue.

[gleu]

OK, so that seems to be working. Though there still is a bug, which may not be related. See this (the "oh" view in the script is your updated query):

$ cat tests.sql
DROP TABLE IF EXISTS t1;
CREATE TABLE t1(id integer);
CREATE INDEX ON t1(id);
CREATE INDEX ON t1(id);
CREATE INDEX ON t1(id) WHERE id=1;
TABLE oh \gx
DROP TABLE IF EXISTS t1;
CREATE TABLE t1(id integer);
CREATE INDEX ON t1(id);
CREATE INDEX ON t1(id) WHERE id=1;
CREATE INDEX ON t1(id);
TABLE oh \gx

$ psql -f tests.sql 
DROP TABLE
Time: 4.152 ms
CREATE TABLE
Time: 2.719 ms
CREATE INDEX
Time: 9.118 ms
CREATE INDEX
Time: 8.945 ms
CREATE INDEX
Time: 8.998 ms
┌─[ RECORD 1 ]─────┬───────────────────────────────────────────────────────┐
│ date_trunc       │ 2022-04-12 07:25:33+02                                │
│ current_database │ postgres                                              │
│ contained        │ CREATE INDEX t1_id_idx1 ON public.t1 USING btree (id) │
│ container        │ CREATE INDEX t1_id_idx ON public.t1 USING btree (id)  │
└──────────────────┴───────────────────────────────────────────────────────┘

Time: 2.234 ms
DROP TABLE
Time: 3.110 ms
CREATE TABLE
Time: 2.746 ms
CREATE INDEX
Time: 9.637 ms
CREATE INDEX
Time: 9.908 ms
CREATE INDEX
Time: 9.301 ms
(0 rows)

Time: 1.029 ms

So depending on the order of creation of indexes, they will be found redundant or not.

[gleu]

I took another look at the query, and I understand now why there's this dependency on the order of creation. Though I don't see an easy way out. The good thing is that now there is no false positive. And, as a great philosopher said, "I can live with" false negatives.

[darold]

Commit 8918139 fixes this issue.