From: | Dominique Devienne <ddevienne(at)gmail(dot)com> |
---|---|
To: | Julien Rouhaud <rjuju123(at)gmail(dot)com> |
Cc: | Matthias Apitz <guru(at)unixarea(dot)de>, Josef Šimánek <josef(dot)simanek(at)gmail(dot)com>, pgsql-general <pgsql-general(at)lists(dot)postgresql(dot)org> |
Subject: | Re: SELECT with LIKE clause makes full table scan |
Date: | 2022-01-26 13:34:21 |
Message-ID: | CAFCRh--nsj2afcFP0iFU6_n9Hb4bSBmGu+YktkkZ8GwDXf1rYQ@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
On Wed, Jan 26, 2022 at 1:13 PM Julien Rouhaud <rjuju123(at)gmail(dot)com> wrote:
> > > > Why is this (ignoring the Index) and what could be done?
> > [...]
> > create INDEX d01ort on d01buch(d01ort) ;/* D01ORT */
>
> That index can't be used with a LIKE that has a trailing wildcard.
Really? That seems to contradict the doc, i.e. default index type is B-Tree,
which definitely supports trailing wildcard LIKE-predicates, as
explicitly stated in said doc:
https://www.postgresql.org/docs/14/indexes-types.html#INDEXES-TYPES-BTREE
So what makes you say that? --DD
PS: That also contradicts the small demo I made earlier up-thread:
```
ddevienne=> \d+ pg_class
Indexes:
"pg_class_oid_index" UNIQUE, btree (oid)
"pg_class_relname_nsp_index" UNIQUE, btree (relname, relnamespace) <<<<<<<<<
"pg_class_tblspc_relfilenode_index" btree (reltablespace, relfilenode)
```
Whether the index is UNIQUE or not does not matter in this case.
From | Date | Subject | |
---|---|---|---|
Next Message | Julien Rouhaud | 2022-01-26 13:51:39 | Re: SELECT with LIKE clause makes full table scan |
Previous Message | Julien Rouhaud | 2022-01-26 12:13:06 | Re: SELECT with LIKE clause makes full table scan |