Re: Index creation

Your statement seems obvious to me. But what I see doesn't seem like a
conscious choice. It turns out that it is better to have a lighter
general-purpose index than to strive to create a target covering index for
a certain kind of operation.

DROP INDEX IF EXISTS bpd.index_class_prop_id_prop_inherit;
CREATE INDEX IF NOT EXISTS index_class_prop_id_prop_inherit
ON bpd.class_prop USING btree
(id_prop_inherit ASC NULLS LAST, timestamp_class_inherit ASC NULLS LAST)
TABLESPACE pg_default;

DROP INDEX IF EXISTS bpd.index_class_prop_id_prop_inherit_covering;
CREATE INDEX IF NOT EXISTS index_class_prop_id_prop_inherit_covering
ON bpd.class_prop USING btree
(id_prop_inherit ASC NULLS LAST, timestamp_class_inherit ASC NULLS LAST)
INCLUDE(id, id_class, inheritance)
TABLESPACE pg_default;

Uchet=# SELECT relid, indexrelid, schemaname, relname, indexrelname,
idx_scan, idx_tup_read, idx_tup_fetch
Uchet-#
Uchet-# FROM bpd.cfg_v_stat_all_indexes WHERE indexrelname LIKE
'index_class_prop_id_prop_inherit%';
relid | indexrelid | schemaname | relname | indexrelname
| idx_scan | idx_tup_read | idx_tup_fetch
-------+------------+------------+------------+-------------------------------------------+----------+--------------+---------------
17572 | 40036 | bpd | class_prop |
index_class_prop_id_prop_inherit | 0 | 0 |
0
17572 | 40037 | bpd | class_prop |
index_class_prop_id_prop_inherit_covering | 7026 | 7026 |
0
(2 rows)

DROP INDEX IF EXISTS bpd.index_class_prop_id_prop_inherit_covering;

CREATE INDEX IF NOT EXISTS index_class_prop_id_prop_inherit_covering
ON bpd.class_prop USING btree
(id_prop_inherit ASC NULLS LAST, timestamp_class_inherit ASC NULLS LAST)
INCLUDE(id, id_class, inheritance)
TABLESPACE pg_default;

DROP INDEX IF EXISTS bpd.index_class_prop_id_prop_inherit;

CREATE INDEX IF NOT EXISTS index_class_prop_id_prop_inherit
ON bpd.class_prop USING btree
(id_prop_inherit ASC NULLS LAST, timestamp_class_inherit ASC NULLS LAST)
TABLESPACE pg_default;

Uchet=# SELECT relid, indexrelid, schemaname, relname, indexrelname,
idx_scan, idx_tup_read, idx_tup_fetch FROM bpd.cfg_v_stat_all_indexes WHERE
indexrelname LIK
E 'index_class_prop_id_prop_inherit%';
relid | indexrelid | schemaname | relname | indexrelname
| idx_scan | idx_tup_read | idx_tup_fetch
-------+------------+------------+------------+-------------------------------------------+----------+--------------+---------------
17572 | 40049 | bpd | class_prop |
index_class_prop_id_prop_inherit | 6356 | 6356 |
0
17572 | 40048 | bpd | class_prop |
index_class_prop_id_prop_inherit_covering | 0 | 0 |
0
(2 rows)
--
Regards, Dmitry!

пн, 20 июн. 2022 г. в 00:08, David G. Johnston <david(dot)g(dot)johnston(at)gmail(dot)com>:

> On Sun, Jun 19, 2022 at 12:06 PM Дмитрий Иванов <firstdismay(at)gmail(dot)com>
> wrote:
>
>> Good afternoon.
>> I have a query parser question. If there are two kinds of queries using
>> an indexed field. In this case, one view is limited to this field, the
>> second one uses a number of fields included in the index by the include
>> directive. It makes sense to have two indexes, lightweight and containing
>> include. Or will the plan rely on the nearest suitable index without
>> considering its weight?
>>
>>
> The system should avoid the larger sized index unless it will sufficiently
> benefit from the Index Only Scan that such a larger covering index is
> supposed to facilitate.
>
> David J.
>